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

Question

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

This requires creating a new table and inserting a foreign key into a new table that refers to the main key of an existing table. Therefore, I want to establish a connection between my new tblTwo, which refers to the primary key tblOne.

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

Columns in table "tblOne" do not match an existing primary key or UNIQUE constraint

I'm not quite sure what this means, and I was wondering if there was anything like that?

Answer 1

This means that the primary key in tblOne was not declared properly - you need to go to tblOne and add the PRIMARY KEY constraint to it.

If you are sure that tblOne has a PRIMARY KEY constraint, then perhaps your database has several tblOne tables belonging to different schemas, and your link suggestion in your FK constraint chooses the wrong one.

If there is a composite key (which your comment will indicate), then you should include both columns in your foreign key link. Note that a table cannot have multiple primary keys, but if it has a composite key, you will see a key symbol next to each column that is part of the primary key.

Answer 2

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

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

Answer 3

I had a situation that led me to this topic. The same error, but another reason. Maybe this will help someone.

Table1 ColA (PK) ColB (PK) ColC Table2 ID (PK) ColA COLB 

When I try to create a foreign key in table 2, I select the values ​​from the combo box in the reverse order

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

This caused an error, as in the title of the topic. Creating the order of storing columns in the primary key table, as they are, the error disappeared.

Stupid, but .. :)

Answer 4

If you still get this error after all the tips from the above answers have followed, everything looks right.

One way to fix this is by deleting the primary keys for both tables, save, update and add them again. Then try adding your relationship again.

Answer 5

This problem caught me, I was adding relationships to the wrong table. Therefore, if you are trying to add relationships in table A to table B, try adding a relationship in table B to table A.

Answer 6

This error occurred to me. When I tried to limit add foreign key , starting with PrimaryKey Table

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

Answer 7

It 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 . Here you have another link with a practical case .

EDIT:

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

IMHO, the foreign key field should always refer to one register in the reference table (i.e. the entire primary key in your case). This means that before creating a foreign key, you must put both fields of the primary key tblOne in tblTwo.

In any case, I’ve worked a bit on the Internet, and it seems that SQL Server 2008 (like some previous versions and other RDBMSs) gives you the ability to refer to only part of the primary key, while this part is the candidate key (Not Null and Unique), and you create a unique constraint for it.

I'm not sure that you can use this in your case, but look at this link for more information about this.

Answer 8

I found that the column names must match.

Example: So if tblOne has an id called categoryId, the link in tblTwo should also be called categoryId.

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

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

Answer 9

If all else fails, this could be the reason: Given 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 determine the dependence of B on A, you are forced to observe the order in which the primary values ​​are determined.

This means that 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 result in the error you are encountering.

Answer 10

I found another way to get this error. This can also happen if you try to make a recursive foreign key (foreign key for the primary key in the same table) in the design view in SQL Management Studio. If you have not saved the table using the primary key, it will return this message. Just save the table, then it will allow you to create a foreign key.