Constraints Tips

Some time ago, I loaded a large set of data into one my tables. To speed up the load, I disabled the FOREIGN KEY and CHECK constraints on the table and then re-enabled them after the load was complete. I am now finding that some of the loaded data was referentially invalid. What happened?

I am designing a table and I am trying to enforce domain integrity on some of my columns using CHECK constraints. Can you give me some examples of using CHECK constraints to constrain a single column and to constrain a column based on the value in another column?

Adding constraints such as check constraints or foreign keys to a table are best practices to keep your data as clean as possible with minimal data enforcement rules performed at the database level. Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. This may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?

I am modifying a table and I need to add a column that must be defined as being unique. I see that SQL Server allows you to define a UNIQUE constraint on a column but I also see that you can create a unique index on a column. Which one should I use?

I have a DEFAULT defined on a table column and I have to create a script to update this DEFAULT to reflect a change in business requirements. I tried an ALTER TABLE statement, but that generates a syntax error. How can I easily redefine DEFAULTs?

I have a DEFAULT defined on a table column and I have to create a script to update this DEFAULT to reflect a change in business requirements. I tried an ALTER TABLE statement, but that generates a syntax error. How can I easily redefine DEFAULTs?

I am modifying a table and I need to add a column that must be defined as being unique. I see that SQL Server allows you to define a UNIQUE constraint on a column but I also see that you can create a unique index on a column. Which one should I use?

I am designing a table and I am trying to enforce domain integrity on some of my columns using CHECK constraints. Can you give me some examples of using CHECK constraints to constrain a single column and to constrain a column based on the value in another column?

Adding constraints such as check constraints or foreign keys to a table are best practices to keep your data as clean as possible with minimal data enforcement rules performed at the database level. Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. This may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?

Some time ago, I loaded a large set of data into one my tables. To speed up the load, I disabled the FOREIGN KEY and CHECK constraints on the table and then re-enabled them after the load was complete. I am now finding that some of the loaded data was referentially invalid. What happened?

I am designing a table and I am trying to enforce domain integrity on some of my columns using CHECK constraints. Can you give me some examples of using CHECK constraints to constrain a single column and to constrain a column based on the value in another column?

I am modifying a table and I need to add a column that must be defined as being unique. I see that SQL Server allows you to define a UNIQUE constraint on a column but I also see that you can create a unique index on a column. Which one should I use?

Some time ago, I loaded a large set of data into one my tables. To speed up the load, I disabled the FOREIGN KEY and CHECK constraints on the table and then re-enabled them after the load was complete. I am now finding that some of the loaded data was referentially invalid. What happened?

I have a DEFAULT defined on a table column and I have to create a script to update this DEFAULT to reflect a change in business requirements. I tried an ALTER TABLE statement, but that generates a syntax error. How can I easily redefine DEFAULTs?

Adding constraints such as check constraints or foreign keys to a table are best practices to keep your data as clean as possible with minimal data enforcement rules performed at the database level. Unfortunately sometimes issues may occur where the data becomes out of synch and one of these constraints has been violated. This may be due to disabled constraints or constraints that are later added with the NOCHECK option. Finding these issues can be done by running queries to check each of the constraints, but is there any easier way to determine if the data the constraints support has been violated?