Foreign Key validations in MSSQL

[JOM1488]

MSSQL v7 appears to validate foreign keys differently from Oracle and Sybase ASA.<br>

In MSSQL if you have a table which validates both a State and City field against a City table (which includes the state) then MSSQL appears to REQUIRE you to include the City field in the update statement even if you only update the state. For example,<br>

If you issue an SQL statement such as the following:<br>

UPDATE people SET state_id = 'FL' WHERE person='SMITH'<br>

<br>

MSSQL gives the following error:<br>

>>>><br>

UPDATE statement conflicted with TABLE FOREIGN KEY constraint 'people_city_id'.<br>

The conflict occurred in database 'T-MSSQL', table 'city'.<br>

The statement has been terminated.<br>

<<<<<br>

<br>

Note: The people record with person = 'SMITH' does NOT have a value in the city_id field.<br>

The MSSQL error seems to be due to MSSQL thinking that a city_id value MUST be present in the SQL statement (Oracle and Sybase ASA do not have such limitations and will happily execute the above SQL statement).<br>

<br>

IF you issue the following statement:<br>

UPDATE people SET state_id = 'FL', city_id = NULL WHERE person = 'SMITH'<br>

<br>

THEN MSSQL will execute the statement without a problem.<br>

<br>

MSSQL's behaviour is odd since Oracle and Sybase both do not require the NULL value to be included in the SQL statement. The purpose of SQL is to isolate business rules from knowledge of the entire schema. Does MSSQL not follow the conventions of the rest of the world or is there some switch or something to make MSSQL act more reasonably?<br>

<br>

The Alter table statements used to create the Foreign Key constraints are listed below:<br>

ALTER TABLE people<br>

ADD CONSTRAINT people_state_id FOREIGN KEY (state_id) <br>

REFERENCES state (state_id)<br>

ALTER TABLE people<br>

ADD CONSTRAINT people_city_id FOREIGN KEY (state_id,city_id) <br>

REFERENCES city (state_id,city_id)<br>

<br>

The city table, of course, includes a Foreign key to state:<br>

ALTER TABLE city<br>

ADD CONSTRAINT city_state_id FOREIGN KEY (state_id) <br>

REFERENCES state (state_id)<br>

<br>

Please let me know what can be done about this odd behaviour of MSSQL v7. Thanks!<br>

 

[bitbrain]

Do your columns have DEFAULT values? Maybe the FK columns should default to NULL.

 

[JOM1488]

It's not a default issue. Microsoft informed us that MS implements Foreign Keys differently from other databases. Thus for MS the only real solution for composite foreign keys is to replace the Foreign Key validation with triggers. Pain in the %&$#!.