Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Unanswered: table cannot have more than four row under car_id (was "hi")

    hi,

    i am creating a table called T2 with the following fields: car_id, make, model, sale_date. And would like to know how i can set a rule so that the table cannot have more than four row under car_id.

    cheers for the helps guyz

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose it depends entirely in which SQL database you are using... Oracle, DB2, MySQL Access/JET, dBase... even SQL server
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create table T2
    ( car_id integer
    , make integer
    , model integer
    , sale_date date
    , check ( 4 <= ( select count(car_id) from T2 ) )
    )
    be advised that not all database sytems support this syntax

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    7
    hi,

    ive tried that method it did not work. i am using database using sql databse language using ssh to create this database if this help any.

    sorry im new to this.

    any help would be grateful

    cheers

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you posted in the SQL forum, which is the generic forum for the SQL language, and i'm pretty sure what i wrote is valid SQL

    as i said, not every database system supports this type of CHECK constraint

    which database are you using? and what does "did not work" mean? did you get an error message? if so, what was it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Posts
    7
    this is the code i taped into my database and recived the following error.

    dataworkbase=> CREATE TABLE t6 (
    dataworkbase(> car_id int,
    dataworkbase(> make varchar(10),
    dataworkbase(> model varchar(10),
    dataworkbase(> sale_date date,
    dataworkbase(> check ( 4 <= (select count(car_id) from t6) )
    dataworkbase(> );
    ERROR: cannot use subselect in CHECK constraint expression


    i have playes around with the code but still experincing problems

    would really be gratefull for any assistance.
    cheers

  7. #7
    Join Date
    Aug 2004
    Posts
    330
    Maybe if you can explain WHY you want this constraint, someone could suggest a design that will accomodate what you want.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which database system are you using? sybase? progress? firebird? db2? postgresql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    Code:
    create table T2
    ( car_id integer
    , make integer
    , model integer
    , sale_date date
    , check ( 4 <= ( select count(car_id) from T2 ) )
    )
    I'm not sure whether this is a valid check constraint (according to SQL 2) or not.
    Conceptually, a constraint should limit its scope to a single row; e.g., when inserting two rows in a table, the order of inserting should not influence which one is accepted by the check constraints or RI constraints. Which is not the case here.

    Conceptual (or syntactic) limitations of check / RI constraints can typically be overcome by using triggers.

    If the RDBMS supports "before" triggers, it can indeed be implemented by a constraint like
    Code:
    (select count(*) from T2 where car_id=New.car_id)+(select count(*) from New)<=4
    (Don't forget to add the number of rows being currently inserted to the ones already there!)

    If only "after" triggers are supported, a "shadow" table will have to be created with a single row per car_id, and a counter (with a check constraint on it) that gets incremented for every INSERT on table T2.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Peter.Vanroose
    I'm not sure whether this is a valid check constraint (according to SQL 2) or not.
    i am
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by r937
    i am
    OK, I trust on your knowledge in this matter !

    B.t.w., which RDBMS implementations support this particular check constraint?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Peter.Vanroose
    B.t.w., which RDBMS implementations support this particular check constraint?
    exactly!!! see post #8
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    I'm sure DB2 does not support this.
    (While it supports most of the SQL 2 stuff.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sure sql server and mysql don't, too

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Just found the following in the SQL-2 standard description (http://www.contrib.andrew.cmu.edu/~s...ql/sql1992.txt) :
    The <search condition> shall not generally contain a <query specification> or a <query expression> that is possibly non-deterministic.
    My interpretation is that this excludes check constraints using COUNT(*) on the current table, since in that case the result depends on the order of a multi-row insert, which is a "non-deterministic" situation.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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