sql server - Add a unique constraint of a sql table as foreign key reference to an another sql table

Question

how to add a unique constraint of a sql table as foreign key reference to an another sql table in sql server 2005

Answer 1

In order to add FK constraint (in child table to parent table) you have to add unique constraint to parent table columns of relationship.
All the rest is optional or has nothing to do with FK:

• no obligatory need of any primary key
• no need of uniqueness in child table colums(s)

The parent table (in such FK relation) is frequently called (including by SSMS) as Primary Key table but PK is not must, unique key/constraint in parent table is enough (as PK is unique, it is particular case of unique constraint in parent table).

Drop TableA and TableB from answer by Matt, which is confusing for beginners,
and recreate them as

CREATE TABLE parentB--TableB 
(
    PK1 INT NOT NULL,
    PK2 INT NOT NULL,
    --I would not have additional non-referenced data in parent table, 
    --rather in child table
    --SomeData VARCHAR(1000),

    --CONSTRAINT PK_TableB PRIMARY KEY CLUSTERED (PK1, PK2)
)

CREATE TABLE childA--TableA 
(
    --PK INT, -- NOT NULL,
    FK1 INT-- NOT NULL,  -- Or NULL, if you''d rather.
    FK2 INT --NOT NULL --,
    , SomeData VARCHAR(1000)
    --CONSTRAINT PK_TableA PRIMARY KEY CLUSTERED (PK),
    --CONSTRAINT FK_TableA_FK1FK2 FOREIGN KEY (FK1, FK2) REFERENCES TableB (PK1, PK2),
    --CONSTRAINT Cons2cols UNIQUE(FK1, FK2)
)

Now, in order, to add FK

ALTER TABLE childA 
ADD 
--constraint FK1_childA 
--this is optional, if one needs to add his own custom name
FOREIGN KEY (FK1) REFERENCES parentB(PK1); 

you should first create unique constraint on corresponding referenced column in parent table column:

ALTER TABLE parentB ADD 
--CONSTRAINT YourUniqueName --uncomment for adding your own name to constraint
UNIQUE(PK1) 

---

Similarly for 2 columns foreign key constraint
(first, you need corresponding unique constraint in parent table):

ALTER TABLE parentB ADD 
--CONSTRAINT YourUniqueName --for adding your own name to unique constraint
UNIQUE(PK1,PK2)  

ALTER TABLE childA 
ADD 
--constraint yourUniqueName --uncomment for adding your own name to FK constraint
FOREIGN KEY (FK1, FK2) REFERENCES parentB(PK1, PK2);