1

I work with SQL Management Studio and I want to add a constraint checking if the data in a column from Table1 exist in another column from Table2 (but the both are not unique).

Table1   | Table2
--------------------
ColX        ColY 
  A      |   A        //Ok
  A      |   A        //Ok
  B      |   A        //Ok
  B      |   B        //Ok
  C      |   D        //Impossbile by the constraint

Is-it possible to do something like this ?

Alex
  • 2,927
  • 8
  • 37
  • 56

2 Answers2

2

Unfortunately, SQL Server won't let you creating a FOREIGN KEY to a non-unique column.

You can make a trigger which would populate an auxiliary table with unique values of the column, and make a foreign key to that table:

CREATE TABLE
       Table1_Y
       (
       colY VARCHAR(20) NOT NULL PRIMARY KEY,
       cnt BIGINT NOT NULL
       )
GO

CREATE TRIGGER
        TR_Table1_All
ON      Table1
AFTER   INSERT, UPDATE, DELETE
AS
MERGE
INTO    Table1_Y t
USING   (
        SELECT  colY, SUM(cnt)
        FROM    (
                SELECT  colY, 1
                FROM    INSERTED
                UNION ALL
                SELECT  colY, -1
                FROM    DELETED
                ) q (colY, cnt)
        ) s (colY, сте)
ON      t.colY = s.colY
WHEN MATCHED AND t.cnt - s.cnt <= 0 THEN
DELETE
WHEN MATCHED THEN
UPDATE
SET     t.cnt = t.cnt + s.cnt
WHEN NOT MATCHED BY TARGET AND s.cnt > 0 THEN
INSERT  (colY, cnt)
VALUES  (s.colY, s.cnt);
GO

then make a foreign key to that table:

ALTER TABLE
        Table2
ADD CONSTRAINT
        FK_Table2_ColX__Table1_ColY
FOREIGN KEY
        (colX)
REFERENCES
        Table1 (colY)
GO
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
1

Yes, it is called foreign key

http://www.w3schools.com/sql/sql_foreignkey.asp

ALTER TABLE Table1
ADD CONSTRAINT FK_Name
FOREIGN KEY (ColX) REFERENCES Table2(ColY)

Edit1: Dang, right, thats what i get for trying to do anything BEFORE i have my morning coffee. Thats addiction for you. Let me think :)

Edit2: So, ok the only way i can think of doing this without structure change is using and UDF in a Check constraint:

create function myudf(@id int)
returns bit
as
begin
    return coalesce((select top 1 1 from Table2 where ColY = @id),0)
end

ALTER TABLE Table1
ADD CONSTRAINT FK_table2_ColX CHECK (dbo.myudf(ColX) = 1)

The other way is adding a new table where ColY would be PK and both Table2 and Table1 would reference it by FK.

But the best solution depends on your requirements.

Vladislav Zalesak
  • 703
  • 5
  • 18
  • Thanks for your answer, that's the first thing I tried but the ColY from table2 isn't unique so I've got an error pop-up when I try – Alex Jul 09 '14 at 08:54
  • The op explicitly mentioned that neither of the columns is unique. – Quassnoi Jul 09 '14 at 08:54
  • 1
    Please note that the check constraint is not checked on DML against the other table, so if someone deletes or updates the last referenced record from `Table2`, this would leave an orphan row in `Table1`. – Quassnoi Jul 09 '14 at 09:20