CODEWITHSUNDEEP
sql-servert-sqlforeign-keysduplicates
Josh Heitz
Josh Heitz

Reputation: 68

Why does Microsoft SQL Server Allow Foreign Key Duplicates?

Consider the following script, which I ran in Microsoft SQL Server 2012:

create table PKTestTable1 (
    Column1 int not null,
    Column2 int not null,
    constraint PK_PKTestTable1 primary key (
        Column1,
        Column2
    )
)
create table FKTestTable1 (
    Column1 int not null,
    Column2 int not null
)
alter table FKTestTable1 add foreign key (Column1, Column2) references PKTestTable1(Column1, Column2)
alter table FKTestTable1 add foreign key (Column1, Column2) references PKTestTable1(Column1, Column2)

Previously, if I had asked SQL Server to "find me the foreign key that points columns A, B, and C in that order on Table1 to columns D, E, and F in that order on Table2," I would have expected no more than one row returned. When running this script, I would have expected SQL Server to display an error citing the duplication, but it does not. Instead, two foreign keys are created with distinct, auto-generated names. As far as I can tell, these foreign keys are exactly the same in every respect except for their names.

I realize I can avoid this by naming my constraints - that's not the problem. It's confusing to see that multiple foreign keys are allowed to exist with the exact same definition. My question is: why does SQL Server allow this?

Upvotes: 1

Views: 664

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

There are other properties that a foreign key can have. Off the to of my head, something like "on delete cascade". By allowing " duplicates", you can change your mind on what those properties should be. For example:

  1. Foreign key already exists
  2. Create new foreign key with "on delete cascade"
  3. Drop old foreign key

In that scenario, your data is protected at all times by at least one foreign key.

Upvotes: 1

Related Questions