| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
11-12-08, 00:55
|
|
Registered User
|
|
Join Date: Aug 2008
Location: Australia
Posts: 9
|
|
|
Constraints in Tables
|
|
Okay, lets say I have a table in a database like this:
Code:
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:
Code:
user_Type VARCHAR(15)
CONSTRAINT type_chk
CHECK (user_Type IN('admin', 'client', 'guest')),
|
|
11-12-08, 03:18
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,084
|
|
|
|
11-12-08, 05:07
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,307
|
|
|
|
Maybe I'm confused, but I think that:
Code:
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.
-PatP
|
|
11-12-08, 05:19
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,084
|
|
pat, your LIKE strings look awfully microsoftian and not very ansi-ish
my "no" was based on a huge assumption, that grich wanted the numbers to be assigned automatically
if the numbers are to be assigned outside the database, then, yeah, you could declare a constraint for the format, but it'd be ugly and complex
but if the numbers are assigned outside the database, what real purpose does the constraint serve? to prevent you from subverting your own application logic?
|
|
11-12-08, 05:43
|
|
Registered User
|
|
Join Date: Aug 2008
Location: Australia
Posts: 9
|
|
Quote:
|
Originally Posted by r937
... if the numbers are assigned outside the database, what real purpose does the constraint serve? to prevent you from subverting your own application logic?
|
I see what you mean.
Thanks for the insight guys, I think I'll keep the assigned in the programming rather than the database.
|
|
11-12-08, 05:48
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,002
|
|
I'd go with an auto increment number and then display this in the desired format on the presentation layer.
|
|
11-12-08, 05:57
|
|
Registered User
|
|
Join Date: Aug 2008
Location: Australia
Posts: 9
|
|
Quote:
|
Originally Posted by georgev
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.
|
|
11-12-08, 08:54
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,307
|
|
Quote:
|
Originally Posted by r937
pat, your LIKE strings look awfully microsoftian and not very ansi-ish
|
True, but like you I sometimes have more insight into the question than what is originally posted.
-PatP
|
|
11-12-08, 09:36
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,002
|
|
Quote:
|
Originally Posted by Grich
Mmm ... the only problem is that it has to be stored as 'P00125' in the database (fussy teacher).
|
Your teacher is a fool 
|
|
11-12-08, 10:10
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,084
|
|
Quote:
|
Originally Posted by georgev
I'd go with an auto increment number ...
|
not in ANSI SQL you wouldn't
|
|
11-12-08, 10:31
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,002
|
|
True.
You'd create a SEQUENCE, right?
EDIT: Perhaps not, appears to be a Google Red-Herring on SQL92
|
Last edited by gvee; 11-12-08 at 10:38.
|
11-12-08, 11:03
|
|
King of Understatement
|
|
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,905
|
|
Quote:
|
Originally Posted by georgev
True.
You'd create a SEQUENCE, right?
|
Over Celko's dead body
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
|
|
|
11-12-08, 11:20
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,170
|
|
Quote:
|
Originally Posted by r937
not in ANSI SQL, no
|
Is there really no way? I don't know ANSI SQL properly but is there not some method along the lines of:
Code:
CHECK
(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'
)
|
|
11-12-08, 11:21
|
|
Moderator.
|
|
Join Date: Sep 2002
Location: UK
Posts: 5,170
|
|
Quote:
|
Originally Posted by r937
but if the numbers are assigned outside the database, what real purpose does the constraint serve? to prevent you from subverting your own application logic?
|
Er, to maintain data integrity as a good database should?
|
|
11-12-08, 11:39
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,084
|
|
good one, tony
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...
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Constraints in Tables
