Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to create a character field with some rules, they are,

Field name:

  1. contract_number.

Rules: Contract Number

  1. Contract number must be alphanumeric.
  2. Contract number must be unique.
  3. Contract number must be auto increment.

Example:

CBE27032014001

Here, the contract number represents the following terms,

CBE 27 03 2014 001
  1. CBE - (Coimbatore) City Name.
  2. 27032014 - Date Month Year.
  3. 001 - Contract reference number (just numeric value).

My database contract table as follows,

create table contract
(
contract_id int not null auto_increment,
contract_number  char(14) not null,
................
unique key(contract_number),
primary key(contract_id)
);

This is completely new to me,

  1. How to implement this task?
  2. Is there any general logic to implement such kind of tasks?
  3. Any suggestions to split the big task into smaller tasks, so that it will easy to understand?

Thanks.

share|improve this question
    
Question: Let's say we have one contract with the contract number CBE27032014001. If there is another contract in that city the following day, would the contract number be CBE28032014001 or CBE28032014002? I'm assuming the former. –  mdoyle Mar 28 at 14:14
    
Must the contract_number field be persisted in a table or can a view be used? –  Jeremy Mar 28 at 15:00
    
Is the table MyISAM or InnoDB ??? –  RolandoMySQLDBA Mar 28 at 19:46
    
@mdoyle, City and Date values are mentioned as above, but the contract_reference number is auto_increment and unique, 001 should not repeat again, it may be 1 to 100000, I have to refer it has 000001, if I am correct. –  rathishDBA Mar 31 at 5:48
    
@Jeremy, contract_number will be used in view and I thought to split the field as 3 different columns but I do know whether it will work or not, also I am not aware of that, I am just a beginner and start learning. –  rathishDBA Mar 31 at 5:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.