Unable to create relationship 'FK_pets_owners

Question

Hi

I'm using SSMS 18.11.1.

I have two tables with this rules: one owner can have more than one pets. One pets belongs to one owner.

table owners:
-----------
primary key nameID int
name varchar(15)

table pets:
----------
primary key   petsID   int
       nameID   int
       pets   varchar(15)

When creating in SSMS a foreign key relationship between table 'pets' and 'owners' and using option Cascade with the Delete rule, i get this error:

'owners' table saved successfully
'pets' table
- Unable to create relationship 'FK_pets_owners'.
Cascading foreign key 'FK_pets_owners' cannot be created where the referencing column 'pets.nameID' is an identity column.
Could not create constraint or index. See previous errors.

What's wrong here? Is this a bug?
Thanks
V.

Amit Mohanty · Answer

The error message you received is not a bug but rather an expected behavior. The issue is with the column 'pets.nameID' in the 'pets' table being an identity column. You cannot create a cascading foreign key constraint where the referencing column ('pets.nameID' in this case) is an identity column.

Valerie Meunier · Answer

Thanks for replying. I'm speaking about a bug because somethimes it doesn't give that error. Look at this database with two tables: products and suppliers. table products: ------------------ primary key prodID int pname varchar(15) suppID int table suppliers: ------------------- primary key suppID int sname varchar(15) This works perfect! I can use the option cascade and i get no error! Where is the difference? By the way it's hard to imagine it is not possible to create a cascading foreign key constraint where the referencing column is an identity column. That's 99% of the cases. They have no other common fields to do this.