SQL Server: Unable to create relationship

Question

I was trying to create a table that has a one to many relationships. but it seems that adding a foreign key in Personal is not working. I am trying to link a Personal Information table to a address table? what is the solution for this error?

• Address table saved successfully
• Personal table

Unable to create relationship 'FK_Personal_Address'.
Cascading foreign key 'FK_Personal_Address' cannot be created where the referencing column 'Personal.ID' is an identity column. Could not create constraint. See previous errors.

Answer 1

it seem that you try to create a foreign key on Personal.ID related to itself.

You probably want to do something like :

ALTER TABLE Adress  WITH NOCHECK ADD  CONSTRAINT [FK_Adress_Personnal] FOREIGN KEY(Personal_Id)
REFERENCES Personal (ID)

Answer 2

I got the same error with adding foreign key constraints to one of my tables. I found the workaround was to add it WITH NOCHECK. why I was able to add the other two foreign keys WITH CHECK but not the third foreign? I found that it was not the table but the order of the foreign key to be added. Any insight to this will be much appreciated.

Answer 3

The primary key in the Person table is presumably an identity. This is an auto-incrementing integer field.

You need to make the foreign key in the address table of type int, not identity. It will hold integers which correspond to Person records, but you don't want the foreign key to auto-increment. For each record in the child table (address) you will set a specific value for the foreign key indicating to which parent record (Person) it belongs.

Example:

INSERT person (firstname, lastname) VALUES ('John', 'Smith')

This will insert the new person record and the field personid will be filled automatically because it is an IDENTITYfield.

Now to insert an address from John Smith you need to know his personid. For example:

-- Say, for example, personid of John Smith is 55
INSERT address (personid, street, city) VALUES (55, 'High Street', 'London')

So in the person table the personid is generated automatically but in the address table you specify the value that matches an existing person. That's the whole point of a foreign key.

Without more information about your schema it's hard to guess the problem.

Answer 4

I made sure to follow identity, int and primary key discussed in above answer. However, I was still getting the same error.

'xReason' table saved successfully
'xAddress' table
- Unable to create relationship 'FK_xAddress_xReason'.  
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_xAddress_xReason". The conflict occurred in database "databaseName", table "dbo.xReason", column 'xReasonID'.

This error resolved when I inserted some data into a Reason table. (table that had a primary key)

If you read this far, this might be your problem.

Answer 5

Without seeing the structure of the tables in the question, I believe the most likely cause is the column in your child table (Address) is marked as an Identity column. In a foreign-key relationship, the parent determines the value of the field, not the child. The column may be the PK in the child table, but not an Identity.