CODEWITHSUNDEEP
sql-server
Bob Tway
Bob Tway

Reputation: 9613

T-SQL: Can you create a Foreign Key relationship without a Clustered Index?

I suspect I may be trying to do something impossible. But I wanted to check and make sure.

I'm fetching data from an external database which uses non-numeric primary keys. We have to keep copies of this data in our own databases, and we like to use auto-generated identity integers for our primary keys. So I created a table with an identity PK as normal, and then set a unique constraint on the column that's going to hold the "other" primary key.

The data we pull in also has a self-referential foreign key from another column. I need to recreate this. So I tried to build it in T-SQL:

ALTER TABLE [Client] 
ADD CONSTRAINT [ClientCode_Unique] UNIQUE NONCLUSTERED 
(
    [ClientCode] ASC
)

ALTER TABLE [Client]
    WITH CHECK ADD CONSTRAINT FK_Client_Agency 
    FOREIGN KEY (ClientCode) REFERENCES Client(LinkedAgency)

However, this resulted in an error:

There are no primary or candidate keys in the referenced table 'Client' that match the referencing column list in the foreign key 'FK_Client_Agency'.

As best I can tell, T-SQL demands that one of the columns in a FK relationship be either a PK or a Clustered Index. I already have a Clustered PK on that table so I think I can't create the key. The alternative is to add another column referencing the actual PK and make sure it's populated correctly in the code that references the data.

Is this right, or is there another way I can create that FK?

Upvotes: 0

Views: 185

Answers (2)

Paolo
Paolo

Reputation: 2254

the answer to the question in the title is: yes you can create a Foreign Key relationship without a Clustered Index.

T-SQL demands that one of the columns in a FK relationship be either a PK or a Unique Index.
The reason is that the remote column is the one used to 'look up' the master data (as in master-detail) at the remote end: if the key data at the remote (master) end is not unique there is no way to identify the matching 'master' row in a bunch of N rows with the very same key.

this is not a technical issue brought to you by SQL or T-SQL but a logical issue in the data: you will have hard times and the very same issues looking up the very same master data in an excel spreadsheet where the link is on a column with duplicate ids.

the above leads to a question about the data: how is possible to look up the master data in the source application if the id in the master table is not unique?
should you ever get the answer to that question you may be able to understand how to make that foreign key relationship.

my extremely wild guesses are that there is some column missing or that the export is somewhat broken.

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

A foreign key can reference the primary key of another table or a key with a unique constraint. So, if your Agency table contains two columns

  • Id
  • LinkedAgency

If Id is the primary key, it can automatically be used as a foreign key. To use LinkedAgency as a foreign key, you must declare a unique constraint on it.

Upvotes: 1

Related Questions