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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.



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?


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 '14 at 14:14
Must the contract_number field be persisted in a table or can a view be used? – Jeremy Mar 28 '14 at 15:00
Is the table MyISAM or InnoDB ??? – RolandoMySQLDBA Mar 28 '14 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 '14 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 '14 at 5:52

Your Answer


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

Browse other questions tagged or ask your own question.