Greetings, would appreciate some help with this one please.If I create two tables as below and there is a 1:n relationship between the tables so that the primary key from TABLE one (random_int) is posted in TABLE two as a foreign key to represent the relationship. There is a conditional constraint in as much that the sum of an instance of TABLE one and an instance of TABLE two i.e. random_int and another_random_int as a row in the relationship table cannot total more than 150. How would I declare a check constraint such as this please?Also the second part of my question is that in TABLE two I have declared two separate named constraints for the column another_small_int i.e. another_small_int_value_1 & another_small_int_value_2. It looks sorta OK like this but is it good practice to keep the constraints separate like this or is it best to combine them as one constraint, and if so, how?CREATE TABLE one ( random_int SMALLINT NOT NULL, random_varchar VARCHAR (5), PRIMARY KEY (random_int),CONSTRAINT random_int_rangeCHECK (random_int BETWEEN 1 AND 99),CONSTRAINT random_varchar_rangeCHECK (random_varchar IN (‘five’, ‘six’, ‘seven’))CREATE TABLE two ( random_date DATE NOT NULL, another_small_int SMALLINT, PRIMARY KEY (random_date),FOREIGN KEY (random_int) REFERENCES one,CONSTRAINT random_date_yearCHECK (random_date YEAR (date) = 2010),CONSTRAINT another_small_int_value_1CHECK (another_small_int < 100)),CONSTRAINT another_small_int_value_2CHECK (another_small_int DECIMAL (5, 2))
Many thanks!
Declaring foreign key/relationship constraints
Posted - 2011-06-08 : 20:04:22
