Is there anything wrong with linking two identical primary keys in two different tables?

As you can see here, in OrderInfo and OrderNumber, I have a relationship set. But I'm not sure what kind. I was hoping to set a relationship so if I deleted an OrderNumber, it would cascade delete the corresponding record in OrderInfo, but it won't let me. It gives me this nasty '


can't cascade delete on identity column

error, so after removing cascade delete, it let me save it appropriately. I'm not sure what key to key means, but I know key to infinity is one to many?

It looks like your model is a one-to-one relationship between OrderNumber and OrderInfo, with OrderInfo.OrderID as the OrderInfo primary key and as a foreign key referencing OrderNumber.OrderID.

The error messages suggests you have defined OrderInfo.OrderID as an IDENTITY column. It should not be IDENTITY - the assigned OrderID value from thre related OrderNumber row should be used when inserting the related OrderInfo row to maintain the foreign key relationship. That should allow you to specify DELETE CASCADE.

StackExchange Database Administrators Q#178355, answer score: 7