CHECK constraint

I've asked this before, without answer (though msnews.microsoft.com seems to
not show everything).
Imagine:
CREATE colors (
colorid INT PRIMARY KEY,
description VARCHAR(20)
)
CREATE TABLE tables (
tableid INT PRIMARY KEY,
colorid INT FOREIGN KEY colors(colorid)
)
CREATE TABLE table_chairs (
chairid INT PRIMARY KEY,
colorid INT FOREIGN KEY colors(colorid),
goeswithtable INT FOREIGN KEY tables(tableid),
CHECK ( ...... )
)
Please don't question the layout of my database tables. This is just an
example to illustrate my question.
So in table_chairs, you have chairs that belong to (makes a nice set with) a
certain table.
I wish to add a check constraint to ensure that the colorid of the chairid
is equal to the colorid of the table.
I can not use TRIGGERS.. can this be done with CHECK constraints or are they
themselves constrained in that they can not reference other tables?
Lisathe easiest way is just not to have colorid in table_chairs at all -
you can retrieve it from tables.
Yet if you really need to have the redundant column (BTW why?), define
a unique constraint on tables(tableid , colorid ) and have a FK point
to it from table_chairs|||It's nto redundant..
The colorid in table_chairs describes the colorid of chairid. The colorid in
tables is the colorid of tables.
The idea is that blue chairs may only be linked to a blue table.
But I need to find a way to CHECK that the colorid of the table (in
table_chairs) matches the colorid of the table (tables), without using
triggers.. is it possible?
"Alexander Kuznetsov" <AK_TIREDOFSPAM@.hotmail.COM> wrote in message
news:1141851166.856693.323390@.v46g2000cwv.googlegroups.com...
> the easiest way is just not to have colorid in table_chairs at all -
> you can retrieve it from tables.
> Yet if you really need to have the redundant column (BTW why?), define
> a unique constraint on tables(tableid , colorid ) and have a FK point
> to it from table_chairs
>|||> is it possible?
*untested"
alter table tables add unique(tableid, colorid)
alter table table_chairs add foreign key(goeswithtable, colorid)
references tables(tableid, colorid)