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

I suppose it depends entirely in which SQL database you are using... Oracle, DB2, MySQL Access/JET, dBase... even SQL server

be advised that not all database sytems support this syntax

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

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?

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

Maybe if you can explain WHY you want this constraint, someone could suggest a design that will accomodate what you want.

which database system are you using? sybase? progress? firebird? db2? postgresql?

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

i am

OK, I trust on your knowledge in this matter !

B.t.w., which RDBMS implementations support this particular check constraint?

exactly!!! see post #8

I'm sure DB2 does not support this.
(While it supports most of the SQL 2 stuff.)

i'm sure sql server and mysql don't, too

Just found the following in the SQL-2 standard description (http://www.contrib.andrew.cmu.edu/~s...ql/sql1992.txt) :
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.