Okay, lets say I have a table in a database like this:
CREATE TABLE person
person_Number VARCHAR(6) PRIMARY KEY,
person_Name VARCHAR(50) NOT NULL,
person_UserName VARCHAR(20) NOT NULL
person_Password VARCHAR(15) NOT NULL
I want to make the primary keys look like this: P00001, P00002, P00003. I can code it easily in Java, PHP etc to do this but I was wondering if you could constraint/check it some how in the database like how it's done in this example:
CONSTRAINT user_type LIKE 'P[0-9][0-9][0-9][0-9][0-9]'
will force your primary key values to look like that, in other words the letter P followed by five digits.
I think that I understand what r937 is thinking when he said it couldn't be done... Since there is no "order" within a table, no constraint can enforce sequence. This means that programs could insert the rows with PK values in any order so P99999 could come between P12345 and P00000, but I don't see that as having anything to do with the "look" of the PK value.
I'd go with an auto increment number and then display this in the desired format on the presentation layer.
Mmm ... the only problem is that it has to be stored as 'P00125' in the database (fussy teacher).
The system I have now is working fine, I have a method in my app that generates one for you, it's just that in theory I can but 'hello!' as the primary key if I wanted to (it is set as VARCHAR), I want the database to stop invalid data being put into the database that's all. Something like what Access has with Masked Input.
Is there really no way? I don't know ANSI SQL properly but is there not some method along the lines of:
(mod(person_Number,1,1) = 'P'
AND len(person_Number) = 6
AND mid(person_Number,2,1) between '0' and '9'
AND mid(person_Number,3,1) between '0' and '9'
AND mid(person_Number,4,1) between '0' and '9'
AND mid(person_Number,5,1) between '0' and '9'
AND mid(person_Number,6,1) between '0' and '9'
i think you'll find that MOD, LEN, and MID are not ANSI SQL functions
as for data integrity, that issue is moot when we're talking about incremental numbers, as pat suggested, you could have P00001, P00002, and then some wise guy inserts P00937, which fits the constraint, but is a time bomb waiting for the incremental numbers to reach that point...