New answers tagged referential-integrity
There is no way round the restriction with truncate as other answers have indicated, but you can of course use delete without disabling the constraints: delete from parent; Note that 'delete' and 'truncate' have different side-effects
If the Pk has multiple columns, and you don't specify the columns in the Fk specification, then it may get them wrong. I chased a bug for a while that turned out to be that the columns were wrong. For example: FOREIGN KEY (OneID, TwoID) REFERENCES MySchema.ForeignTable was interpreted as: FOREIGN KEY (OneID, TwoID) REFERENCES MySchema.ForeignTable(TwoID, ...
The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say: If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that ...
No, there is no built-in feature for merging one key into another, updating all referencing rows automatically. There could be all kinds of complications you need to resolve. Simple case You need to run UPDATE on all referencing tables and DELETE on the referenced table. The simple case would look like this: BEGIN; -- 1. update references UPDATE sale ...
Following Neil and user16484 suggestions also ypercube and jynus and good practices at DB modeling, I come with this model This is what they talked about
No, that is not right. There should not be an order_id in a person table. Generally speaking, is an order id an attribute of a person? No. I'd start with Single Table Inheritance, as it's simpler: create table parties ( party_id int primary key, type smallint not null references party_types(party_type_id), --1=individual,2=organization name text not ...
Top 50 recent answers are included