MSSQL 2000 supported with FK constraints with onDelete

I was trying to use add_foreign_key_constraint against a MSSQL 2000 database and apparently the SQL that it generates when using onDelete=“SET NULL” is invalid.

MSSQL 2k had a strange system for managing onDelete triggers at the table level of the foreign table.

Just want to confirm that Liquibase has no way of managing onDelete in this way.

Not native way.  You’ll have to switch to using if the addForeignKeyConstraint tag is not working for you.

How stange is the system?  Is it somethign that could be generated for sqlserver 2000 databases easily in place of the normal onDelete?

Nathan

The system is definitely a little antiquated. Instead of ON DELETE SET NULL at the FK constraint level, the mssql 2000 way that was recommended to use is to use triggers. Triggers are queries assigned to the referenced table that get run in the event that a record in that table is updated or deleted. The query basically enumerates all of the possible tables that could contain references to this table’s records and performs the SET NULL actions against all of them.

Basically if you have Table A with column c1 that reference column c2 in Table b, the ON DELETE behavior would reside in a trigger in Table B that said when deleting a record from Table B, update Table A and null any records in where column c1 referenced the deleted record in Table B.

The real problem with this approach is that if you ever want to remove the FK constraint, modify the column or remove it entirely, it’s a two step process. You have remove the constraint itself; then, you have to modify the trigger that exists on the referenced table. If you don’t do it, you’ll get a runtime error when deleting from the referenced table because the trigger query is no longer valid. If you just remove the FK constraint, you’d still have the weird behavior where the ON DELETE SET NULL part would still be in effect even if the constraint was gone.

Should probably be noted that the onDelete attribute of add fk constraint is not valid for SQL Server 2000. The SQL that liquibase generates is invalid.