2

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?

Thanks!

  • 3
    Hey Zach, thanks for contributing. We consider opinion based questions off topic on DBA.SE. Maybe you can re-phrase your question to focus on valid ways to manage constraints dynamically or if there are any reasons why this approach is invalid & avoid the opinionated parts. – LowlyDBA Oct 26 '18 at 16:06
  • 1
    No answer here, but an idea of why it might have been done. Maybe the database gets a massive update once in a long time. The idea might be to disable all the constraints, do the massive update, then put the constraints back in place. The only way to verify this would be to delve into the maintenance procedures.. – Walter Mitty Oct 26 '18 at 18:52
  • @WalterMitty, I think you're on to something. This kind of thing was common in massive ETL jobs in the past, where the order of table loading could break the process, so the constraints are dropped and recreated. If the order of precedence is set correctly, then this kind of thing isn't necessary. – Randolph West Oct 26 '18 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.