Alter Sql table (Change foreign key to Second primary of the table)

Question

Hi,

I've a sql table with a primary key(Auto Incremented) and a foreign key.Now I need to modify the table by modifying the foreign key to second primary key so that its values are not allowed to duplicate.

How do i alter my table without affecting the data? Need the sql code.

Regards, Vix

Answer 1

If I understand your request, you want to force the foreign key to be unique within the given table so your schema looks like:

Create Table Table1
(
    Id int not null primary key clustered
    , ForeignId not null
    , ...
    , Constraint FK_Table1_Table2
        Foreign Key ( ForeignId )
        References Table2( Id )
)

And you now want to force ForeignId to be unique in this table, correct? You would do the following:

Alter Table Table1
    Add Constraint UC_Table1_ForeignId Unique Nonclustered ( ForeignId )