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

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 nameFOREIGN 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 constraintUNIQUE(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 constraintUNIQUE(PK1,PK2)  ALTER TABLE childA ADD --constraint yourUniqueName --uncomment for adding your own name to FK constraintFOREIGN KEY (FK1, FK2) REFERENCES parentB(PK1, PK2); 

Apologies but I'm not really sure what you're asking here. Giving more of an example with table definitions would help! I think you're saying you have two columns in TableA in a unique constraint named "Cons2cols", and you also want these two columns to be a FK to a two column PK / unqiue pair in TableB.

That works as follows, if you're creating the tables from scratch:

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

If the tables already exist, you can add in these same constraints after the fact:

ALTER TABLE TableA ADD CONSTRAINT FK_TableA_FK1FK2 FOREIGN KEY (FK1, FK2) REFERENCES TableB (PK1, PK2);ALTER TABLE TableA ADD CONSTRAINT Cons2cols UNIQUE(FK1, FK2);

Either way, TableA now has a unique, 2 column FK to another table.

You need to keep in mind that adding a FK on a column does not automatically put an index on that column. You'll need to do this in two steps.

1) Make a column in your table a FK to a parent table.2) Add a unique constraint on that same column