Unique values between two tables.

[Ori]

Hi,

I have two tables such that in each table I need to make sure that
column x in table A and column y in table B have a unique values
meaning that a user cannot insert a value to column A if its already
exist in column B and vice versa.

How can I enforce it? Please remember that this two different tables.

Thanks,

Ori.

[Erland Sommarskog]

Re: Unique values between two tables.

Ori (orianavim@hotm ail.com) writes:[color=blue]
> I have two tables such that in each table I need to make sure that
> column x in table A and column y in table B have a unique values
> meaning that a user cannot insert a value to column A if its already
> exist in column B and vice versa.
>
> How can I enforce it? Please remember that this two different tables.[/color]

Triggers is one one way.

You can also set up an indexed view which is a SELECT with UNION ALL of
the two tables with a unique index on the common key.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: Unique values between two tables.

You can create a third table to enforce the constraint:

CREATE TABLE C (x INTEGER PRIMARY KEY, z CHAR(1) NOT NULL CHECK (z IN
('A','B')), UNIQUE (x,z))

CREATE TABLE A (x INTEGER NOT NULL, z CHAR(1) NOT NULL DEFAULT 'A' CHECK (z
= 'A'), FOREIGN KEY (x,z) REFERENCES C (x,z) /* PRIMARY KEY ??? */ )

CREATE TABLE B (x INTEGER NOT NULL, z CHAR(1) NOT NULL DEFAULT 'B' CHECK (z
= 'B'), FOREIGN KEY (x,z) REFERENCES C (x,z) /* PRIMARY KEY ??? */ )

--
David Portas
SQL Server MVP
--