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]

Ori (or*******@hotmail.com) writes:

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.

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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

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
--