0

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.

Rathish Kumar B
  • 2,304
  • 5
  • 24
  • 36
  • 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. – Rathish Kumar B Mar 31 '14 at 05: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. – Rathish Kumar B Mar 31 '14 at 05:52
  • @RolandoMySQLDBA, I am using InnoDB, I got some references for MyISAM but, I could not find for InnoDB – Rathish Kumar B Mar 31 '14 at 05:53
  • "I thought to split the field as 3 different columns" If storing the contract number in three columns is an option, that is the simplest way to do this--one for city code, one for date, and the third auto_increment column. – mdoyle Mar 31 '14 at 14:03
  • Does this answer your question? Unique identifier field with a conditional – mustaccio May 29 '22 at 14:50

0 Answers0