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

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.