CODEWITHSUNDEEP
sql-serverunique-constraint
subash
subash

Reputation: 4137

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

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

Upvotes: 10

Views: 51398

Answers (5)

Mircea Stanciu
Mircea Stanciu

Reputation: 3753

I prefer this approach where this table references another table (transaction_log):

CREATE TABLE transaction_settings_log
(
    transaction_fk UUID NOT NULL
        CONSTRAINT transaction_log_pkey REFERENCES transaction_log (id) UNIQUE,
    group_selected BOOLEAN DEFAULT TRUE,
    leg_closed     BOOLEAN DEFAULT FALSE
);

Upvotes: 0

Anthony Faull
Anthony Faull

Reputation: 17957

Forget about the unique constraint for now. Just create your new foreign key on the two columns.

ALTER TABLE dbo.PurchaseDetail
ADD FOREIGN KEY (Customer, Product)
    REFERENCES dbo.Purchase (Customer, Product)

Upvotes: 0

Gennady Vanin Геннадий Ванин
Gennady Vanin Геннадий Ванин

Reputation: 10384

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

Upvotes: 11

Matt
Matt

Reputation: 4388

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.

Upvotes: 2

Randy Minder
Randy Minder

Reputation: 48402

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

Upvotes: 1

Related Questions