Today, I continuing my series of talks on fundamental SQL Server database topics. I am a proud United States Army Reservist (USAR) Veteran. Just like boot camp I went to so long ago, I am going nick name the series BASIC TRAINING.
In my last article, I was talking about how to ADD, ALTER and DROP columns using the ALTER TABLE statement. One problem that we faced was how to add a column with a NOT NULL clause to an existing table that contained data. I will be introducing the DEFAULT constraint which can be used to solve this problem.
We are going to build upon the sample database named [TRAINING] containing a schema named [PUBS] and a table named [PUBLISHERS]. As a database developer, one should always check the existence of an object before dropping it. That way, the drop statement will not fail.
The code snippet below drops the column if it exists.
|
1 2 3 4 5 6 7 8 9 |
<span style="color: #008000; font-size: small;">-- Remove column if it exists IF EXISTS ( SELECT * FROM sys.objects o join sys.columns c ON o.object_id = c.object_id WHERE o.name = 'PUBLISHERS' and o.type = 'U' and c.name = 'zip' ) ALTER TABLE [PUBS].[PUBLISHERS] DROP COLUMN zip; GO </span> |
Please note that every user table exists in the ‘sys.objects’ system view and every column of that table exists in the ‘sys.columns’ system view. By joining the tables and selecting on the appropriate names, we can determine the existence of the column.
There are two ways to add a constraint: unnamed and named. The code snippet below adds a unnamed default constraint.
|
1 2 3 4 5 6 |
<span style="color: #008000; font-size: small;">-- Add constraint w/o name ALTER TABLE [PUBS].[PUBLISHERS] ADD zip char(5) NOT NULL DEFAULT('02910'); GO </span> |
The system creates a unique name for this constraint based upon the table name, column name, and a random hex string.
Let’s drop this column so that I can demonstrate how to create a named constraint. We can see below that this failed due to the DEFAULT constraint.
|
1 |
|
To successfully drop the column, we need to drop the constraint first.
|
1 |
|
Let’s repeat this process by adding back the DEFAULT constraint with a name of DF_LOCAL_ZIP.
If you query the table named [PUBLISHERS], you will noticed that the new field has a default zip code of ‘02910’. Just what we want.
This is due to the fact that we added the new COLUMN and DEFAULT constraint at the same time. If we did it in two separate steps, with a allow NULL clause on the column, we would end up with NULL values in the zip column. See code examples at end of article.
This is where the WITH VALUES clause comes into play. It has to be specified with the ADD column sub-command inside a ALTER TABLE statement. In short, it will fill in a column that allows NULLS with the default value.
|
1 2 3 4 5 6 |
<span style="color: #008000; font-size: small;">-- Add constraint with name, with values clause ALTER TABLE [PUBS].[PUBLISHERS] ADD zip char(5) NOT NULL CONSTRAINT DF_LOCAL_ZIP DEFAULT('02910') WITH VALUES; GO </span> |
In summary, I started introducing the four table constraints that can be used to add data integrity to a database design.
The DEFAULT constraint allows you want to add a NOT NULL column to an existing table. The constraint inserts a default value into a COLUMN when no value is supplied. I use DEFAULT constraints when creating tables so that I can have two audit columns at the end of a table: who inserted the record SUSER_SNAME() and when it was inserted GETDATE().
Note, you can just ADD or DROP constraints. There is no ALTER clause. Next time, I will be talking about PRIMARY KEY constraints.
