Sql Server Composite Primary Key

Question

I found something today in one of my databases that I cannot explain. I noticed on one of my tables that there was a foreign key made up of two fields which referenced the same two fields on the same table. These two fields were also the same two fields which made up the composite primary key. For example:

Table: Listings
Primary Key: MlsNumber (int), MlsBoard (int)
Foreign Key: PK Table: Listings (MlsNumber, MlsBoard) FKTable: Listings(MlsNumber, MlsBoard)

When I saw this I assumed I had accidentally created it at some point. Then I found the same thing in 4 more tables in the same database. I am the only person who can modify this database, and this is not something I did intentionally. I did notice that in every case where I found it, it was on a table with a composite primary key. I have many other tables in the same database which have composite primary keys and do not contain this odd foreign key.

Does anyone know what would create this? The server is SQL Server 2008.

Answer 1

If the FK fields reference a different set of two fields in the same table, then This represents a self-join. For example, if a table of employees had a composite primary Key, (say DivisionId, and EmployeeId, where the employeeIds were only unique within each division), and then you wanted to have each employee record identify the employee's Supervisor - as another row in the same table.

Then each record would have to have a composite ForeignKey (SupervisorDivisionId, SupervisorId) in order to identify the Employee's Supervisor.

If these two FK fields reference themselves, then there is no valid reason for this as a constraint structure in this way will ALWAYS be satisiefd, ( it cannot be false. ) and is therefore pointless.

Answer 2

I believe I have found the cause and IMO this is a very poor design decision by the Microsoft folks.

I went to create a new Foreign Key and set the Primary Key Table and Foreign Key Table and their columns. I then realized that I needed to clean the data in the table before creating the Foreign Key, so I clicked close. What I discovered is that this creates a pending Foreign Key as I described in my question, a FK comprised of the Primary Key fields referencing itself.

So what I can reasonably guess has happened is that at some point on these 5 tables, I started to create a FK, thought I had aborted that operation when I hit close, and then made another change to the table, such as adding a field or changing a datatype, and then when I saved the table, I got an unintended FK created. I might add that it was an FK with parameters I had never set.

This is a bug in my opinion.

Edit: I just discovered that it's actually worse than I described. Even if you hit the cancel button on the Foreign Key creation dialog, it still creates the self-referencing Foreign Key in a pending state. This is terribly stupid.

Edit 2: This keeps getting worse the more I investigate. I just got it to save one of these self-referencing FK's by closing SSMS and clicking No on the save changes dialog. I reopened SSMS and the self-referencing FK had been created anyway. Just to recap, I clicked Cancel on the create FK dialog, and No on the save changes dialog, and it created a Foreign Key with parameters I had never set.