Check Constraint

Here is what I am trying to accomplish, and I would like to know if it is
possible through the use of check constraints.
Two tables Table_A and Table_B. When I insert new record in Table_A I would
like to check for the existence of a record, through the use of a check
constraint, in Table_B. If the record exists then insert the record,
otherwise the insert fails.
If this is possible, how would I set up the expression? I have tried the
EXISTS statement with no success.
Thanks,On Wed, 26 Apr 2006 15:01:02 -0700, Robert wrote:

>Here is what I am trying to accomplish, and I would like to know if it is
>possible through the use of check constraints.
>Two tables Table_A and Table_B. When I insert new record in Table_A I would
>like to check for the existence of a record, through the use of a check
>constraint, in Table_B. If the record exists then insert the record,
>otherwise the insert fails.
>If this is possible, how would I set up the expression? I have tried the
>EXISTS statement with no success.
>Thanks,
Hi Robert,
Rather than using a CHECK constraint, use a FOREIGN KEY (aka REFERENCES)
constraint for this.
Hugo Kornelis, SQL Server MVP|||Robert (Robert@.discussions.microsoft.com) writes:
> Here is what I am trying to accomplish, and I would like to know if it is
> possible through the use of check constraints.
> Two tables Table_A and Table_B. When I insert new record in Table_A I
> would like to check for the existence of a record, through the use of a
> check constraint, in Table_B. If the record exists then insert the
> record, otherwise the insert fails.
> If this is possible, how would I set up the expression? I have tried the
> EXISTS statement with no success.
You can do this, if you put the check in a user-defined function.
But don't do this! I tried it once. An update of all rows in a table with
24000 rows went from one second to 30 seconds.
Inter-table checks are best done in a trigger.
Unless, we are talking foreign-key checks, in which case you should
use a FOREIGN KEY constraint:
ALTER TABLE Orders ADD CONSTRAINT
FOREIGN KEY fk_Orders_Customers (CustomerID)
REFERENCES Customers(CustomerID)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx