Constraint using SQL query?

I have this situation...
TABLE 1:
ID = int
catID = int
...random stuff
TABLE 2:
ID = int
...random stuff
There is a foreign key such that all values in Table 2, their ID must
correspond to an ID in Table 1.
I want to write a constraint such that all rows in Table 2 correspond
to a row in Table 1 (IDs match), and that the catID value of that row
is 1.
I don't know of a way to do this with a regular constraint since it is
across tables, can it be done with a a sql query as a constraint?
Any help is appreciated...
AmitAmit Ku wrote:
> I have this situation...
> TABLE 1:
> ID = int
> catID = int
> ...random stuff
> TABLE 2:
> ID = int
> ...random stuff
> There is a foreign key such that all values in Table 2, their ID must
> correspond to an ID in Table 1.
> I want to write a constraint such that all rows in Table 2 correspond
> to a row in Table 1 (IDs match), and that the catID value of that row
> is 1.
> I don't know of a way to do this with a regular constraint since it is
> across tables, can it be done with a a sql query as a constraint?
> Any help is appreciated...
> Amit
Something like this (I am reposting a canned answer)
CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1, 2, 3)),
UNIQUE (user_id),
PRIMARY KEY (user_id, user_type),
user_firstname VARCHAR(20) NOT NULL,
user_lastname VARCHAR(20) NOT NULL,
..);
CREATE TABLE Quiz
(user_id INTEGER NOT NULL,
user_type INTEGER NOT NULL
CHECK (user_type IN (1)),
FOREIGN KEY (user_id, user_type),
REFERENCES Users (user_id, user_type),
question_id INTEGER NOT NULL
REFERENCES Questions (question_id),
PRIMARY KEY (user_id, question_id));
Note that for real life production code you want to explicitly name all
your constraints...
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/