Unable to create relationship 'FK_pets_owners

Valerie Meunier

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.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty3y ago
Accepted 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.
+1
Valerie Meunier3y ago
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.
+1