SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

Question

I need to make some changes to a SQL Server 2008 database.

This requires the creation of a new table, and inserting a foreign key in the new table that references the Primary key of an already existing table. So I want to set up a relationship between my new tblTwo, which references the primary key of tblOne.

However when I tried to do this (through SQL Server Management Studio) I got the following error:

The columns in table 'tblOne' do not match an existing primary key or UNIQUE constraint

I'm not really sure what this means, and I was wondering if there was any way around it?

Dislocate

Answer 1

It means that the primary key in tblOne hasn't been properly declared - you need to go to tblOne and add the PRIMARY KEY constraint back onto it.

If you're sure that tblOne does have a PRIMARY KEY constraint, then maybe there are multiple tblOne tables in your DB, belonging to different schemas, and your references clause in your FK constraint is picking the wrong one.

If there's a composite key (which your comment would indicate), then you have to include both columns in your foreign key reference also. Note that a table can't have multiple primary keys - but if it has a composite key, you'll see a key symbol next to each column that is part of the primary key.

Bulley

Hi Damien, there is only one tblOne in the database, and it definitely has a PK. One thing I noticed (I wasn't involved in the creation of the database, and I rarely have to go near it) just now is there are two primary keys in tblOne (so a composite?). Would this affect it? – Dislocate

Updated my answer - you have to include both columns in your FK constraint – Bulley

If you have a composite key, are sure you added all the columns, and are still getting this, see alanh's answer below. – Im

And if there's a composite key, should I add two columns(2 FK) in the other table and reference them to the columns in the composite primary key or should I create only one column(1 FK) and reference it to the composite key? – Azerbaijan

As per "alanh" - Key order is important for composite keys – Overmuch

Answer 2

If you have a composite key the order is important when creating a FK, and sometimes the order is not how it is displayed.

What I do is go to the Keys section of the table1 and select script primary key as create to clipboard and then create FK using the order as shown in script

Okeefe

Thank God I scrolled down to this answer! – Draft

U made my day... Thanks – Coagulase

Answer 3

I've had this situation that led me to this topic. Same error but another cause. Maybe it will help someone.

Table1
ColA (PK)
ColB (PK)
ColC


Table2
ID (PK)
ColA
COLB

When trying to create foreign key in Table2 I've choose values from combobox in reverse order

Table1.ColB = Table2.ColB
Table1.ColA = Table2.ColA

This was throwing me an error like in topic name. Creating FK keeping order of columns in Primary key table as they are, made error disappear.

Stupid, but.. :)

Kaceykachina

Answer 4

If you still get that error after you have followed all advice from the above answers and everything looks right.

One way to fix it is by Removing your Primary keys for both tables, Save, Refresh, and add them again. Then try to add your relationship again.

Daw

I had a problem changing the table that the relationship was to reference. Instead of changing it, I just deleted it and made a new relationship and you're right it ended up working. – Mozell

Answer 5

This Error happened with me When I tried to add foreign key constraint starting from PrimaryKey Table

Simpy go to other table and and create this foreign key constraint from there (foreign key Table)

Nassi

Answer 6

This issue caught me out, I was adding the relationship on the wrong table. So if you're trying to add a relationship in table A to table B, try adding the relationship in table B to table A.

Supercilious

Answer 7

That looks like you are trying to create a foreign key in tblTwo that does not match (or participate) with any primary key or unique index in tblOne.

Check this link on MSDN regarding it. Here you have another link with a practical case.

EDIT:

Answwering to your comment, I understand you mean there are 2 fields in the primary key (which makes it a composite). In SQL it is not possible to have 2 primary keys on the same table.

IMHO, a foreign key field should always refer to a single register in the referenced table (i.e. the whole primary key in your case). That means you need to put both fields of the tblOne primary key in tblTwo before creating the foreign key.

Anyway, I have investigated a bit over the Internet and it seems SQL Server 2008 (as some prior versions and other RDBMS) gives you the possibility to reference only part of the primary key as long as this part is a candidate key (Not Null and Unique) and you create an unique constraint on it.

I am not sure you can use that in your case, but check this link for more information on it.

Distefano

Hi Guillem, One thing I noticed (I wasn't involved in the creation of the database, and I rarely have to go near it) just now is there are two primary keys in tblOne (so a composite?). Would this affect it? – Dislocate

If tblOne has a two-column, composite primary key, then both those columns also need to be in tblTwo before you can set a foreign key reference from tblTwo to tblOne. – Uriisa

A foreign key can reference any UNIQUE key constraint on another table - it doesn't have to be part of the composite PK to be referenced - and it's been true since at least SQL Server 2000. – Bulley

Edited again to clarify that by "the whole primary key" I meant "in the present case", not that it should always be a primary key. I know foreign keys can reference unique keys. This is also the case in other RDBMS like Oracle. – Distefano

To be pedantic, if one column on its own has a unique constraint on it then a multi-column constraint that includes that column is not a primary key because it isn't a minimal superkey. It's one of the (many) non-relational oddities of SQL that it has a constraint called "PRIMARY KEY" which is used for things which are not primary keys at all! – Ataghan

@Guillem Vicens: I think you should remove the sentence that begins, "Anyway, I have investigated a bit over the Internet..." It is not factually correct (e.g. NOT NULL is not a requirement, doesn't need to form part of a PK, etc), is a feature of Standard SQL and not limited to the 2008 version of SQL Server, as confirmed in comments (including your own! :) – Advert

@onedaywhen, maybe my sentence is a bit misleading (sorry, english is not my first language :-) ). I wrote SQL Server 2008 because it is the RDBMS that was mentioned in the question. I did not want to imply that v2008 was the only version that allowed to reference part of a primary key nor that it was the only RDBMS to do so. I do not know if all RDBMS allow it, though, even if it is a SQL standard, so I only mentioned SQL Server. Anyway, I will edit it and hope I do not make it misleading again :-P – Distefano

'reference only part of the primary key' is still misleading - the unique constraint can be applied to any column or columns (whether they are part of the PK or not). – Bulley

@Damien_The_Unbeliever, I guess you mean it should read 'reference only part of the primary key or unique key'? – Distefano

@Guillem - no. Basically, a FOREIGN KEY has to reference a complete key. This can either be the PRIMARY KEY or a UNIQUE (KEY). That's it. There's no ability to reference part of a PK. The reason the FK was valid in the linked to answer is because it referenced a UNIQUE KEY. That's it. The fact that this unique key happened to be part of the PK is completely irrelevant. – Bulley

@Damien_The_Unbeliever, you're right, thx for the correction and patience. :-) I have edited again my answer to specify that you need to create a unique constraint on the part of the PK you want to reference in order to be able to do it. To be honest, I should have read thoroughly the link I provided. It is stated there very clearly. :-S – Distefano

Answer 8

I have found that the column names must match.

Example: So if tblOne has id called categoryId a reference in tblTwo must also be called categoryId.

_tblname, primary key name, foreign key_
tblOne, "categoryId", none
tblTwo, "exampleId", "categoryId"

I noticed this when trying to create foreign key between 2 tables that both had the column name "id" as primary key.

Ballistics

This isn't the case with SQL Server 2008 R2 – Sw

Yep, column names do not have to match as long as the relationship is defined – Sw

Answer 9

If nothing helps, then this could be the reason: Considering this case: Table A: Column 1 (Primary Key) Column 2 (Primary Key) Column 3 Column 4

Table B: Column a (Primary Key) Column b Column c

when you are defining a dependency B to A, then you are forced to respect the order in which the primaries are defined.

That's mean your dependency should look like this: Table A Table B Column 1 Column b Column 2 Column c

AND NOT: Table A Table B Column 2 Column c Column 1 Column b

then this will lead to the error you are encountering.

Bergama

Answer 10

I've found another way to get this error. This can also happen if you are trying to make a recursive foreign key (a foreign key to the primary key in the same table) in design view in SQL Management Studio. If you haven't yet saved the table with the primary key it will return this message. Simply save the table then it will allow you to create the foreign key.

Downstairs

Answer 11

If you have data in your tables this could be the issue.

In my case I had some data in the Account table that I loaded at 3 pm, and some data in Contact table that I loaded at 3:10 pm, so Contact table had some values that weren't in my Account table yet.

I ended up deleting these values from the contact table and then managed to add a key without any problems.

Hydrotherapy

Answer 12

In my case, the error occurred because I used the SSMS designer pane to add the relationship (which was pointing towards a composite unique constraint).

Writing the ALTER TABLE query by hand, and respecting the order of the composite keys of the unique constraint, I was able to define the foreign key constraint.

Rocha

Answer 13

In my self-referencing table I mixed up the primary key and foreign key fields.

Platinocyanide

Answer 14

Kindly also see that there are no existing data inside the table where the primary key is defined while setting the foreign key with another table column.

this was the cause of the error in my case. I had to take backup empty the table set the relationship and then upload the data back. sharing my experience Was using ms sql smss

Byre

there was some data which was not meeting the new constraint criteriea – Byre

Please clarify via edits, not comments. Please use standard spelling & punctuation. PS This is not clear. In particular "no existing data inside the table where the primary key is defined while setting the foreign key". – Ceyx