Working with Unique and Default Constraints :: SQL Server 2012
As you examine the database of your current project more closely, you find that there are
more data integrity problems than you first realized. Here are some of the problems you
found. How would you solve them?
- Most of the tables have a surrogate key, which you have implemented as a primary key.
However, there are other columns or combinations of columns that must be unique,
and a table can have only one primary key. How can you enforce that certain other
columns or combinations of columns will be unique?
- Several columns allow NULLs, even though the application is supposed to always
populate them. How can you ensure that those columns will never allow NULLs?
- Often the application must specify specific values for every column when inserting
into a row. How can you set up the columns so that if the application does not insert a
value, a standard default value will be inserted automatically?
Answers
- You can create a unique constraint on a column or set of columns to ensure their
unique values, in addition to the primary key.
- You can prevent a column from ever having NULLs by altering the table and redefining
the column as NOT NULL.
- You can create a default constraint on a column to ensure that if no value is inserted, a
default value will be inserted in its place.