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?


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


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.