I just wanted to get feedback from actual DBAs, based on their experience and knlowledge of database theory, in regards to this.
At my company, we have a large .sql file that holds all of our schema scripts which get applied to customer servers during updates.
In this file, most schema statements are explicitly typed out (i,e, ALTER TABLE Foo DROP COLUMN Bar) except for constraints.
For some reason, a previous DBA created a table called DBConstraints. I have included the schema below along with a couple of sample rows on a fake table. They include primary, foreign, unique, check, and default constraints in said table.
Once the rows are in this table, during application of the schema scripts, we call a stored procedure that creates, drops, and applies the constraints from the DBConstraints table based on the constraint's type and definition.
Here is the table along with some sample data.
TABLE DBCONSTRAINTS ConstraintType | TableName | ConstraintName | ColumnSetDefinition | ReferenceTableName Primary / Foo / PK_FOO_KeyId | (KeyId) | NULL Foreign / Foo / FK_Foo_Bar_Link | (ForeignId) references Bar(ForeignId) on delete cascade / Bar Default / Bar / DK_Bar_UpdatedDate | (GETDATE())
There is a stored procedure called
gp_UpdateConstraints which processes them.
An example of one of our schema scripts is something like this:
gp_UpdateConstraints @Action = 'Drop', @TableName = 'Foo' --This will drop constraints. ALTER TABLE FOO ADD TestColumn BIT NULL; DELETE FROM DBConstraints WHERE ConstraintName = 'DK_Foo_TestColumn'; INSERT DBConstraints (ConstraintType, TableName, ConstraintName, ColumnSetDefinition) VALUES ('Default', 'Foo', 'DK_Foo_TestColumn', '(0) for TestColumn'); gp_UpdateConstraints @Action = 'Apply', @TableName = 'Foo' --This will reapply constraints.
To me, this seems to be an unnecessary and non-standard way of maintaining constraints, and I don't really see any advantage of doing it this way versus just maintaining the constraints normally. The only thing I know that the procedure does is automatically search for dependencies in foreign keys and drop them based on the table that is being worked on, but even that isn't foolproof.
Does anyone have any ideas why this may have been done this way, and whether it is actually better?