Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2008
    Location
    Australia
    Posts
    9

    Unanswered: 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')),

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not in ANSI SQL, no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd go with an auto increment number and then display this in the desired format on the presentation layer.
    George
    Home | Blog

  7. #7
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    I'd go with an auto increment number ...
    not in ANSI SQL you wouldn't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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.
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    True.
    You'd create a SEQUENCE, right?
    Over Celko's dead body
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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'
    )

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •