This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Default Column Values

The eleventh part of the SQL Server Programming Fundamentals tutorial considers the use of default column values. These are literal values or functions that are used to provide default values for columns when new table rows are created.

Adding a Default Value Using Transact-SQL

There are three common situations where you will want to add a default constraint using T-SQL. The first of these is during the creation of a new table. In this instance, the constraint is provided immediately after the column specification that you wish it to affect. The syntax for a default constraint is as follows:

CONSTRAINT constraint-name DEFAULT default-value

As the JoBS database is already complete, there is no need to add a new table with a default constraint. However, if we were to recreate the Engineers table including the standard hourly rate described earlier, the T-SQL for the table creation would be as follows:

    EngineerId INT NOT NULL,
    EngineerName VARCHAR(50) NOT NULL,
    HourlyRate MONEY NOT NULL CONSTRAINT DF_Engineers_HourlyRate DEFAULT 20,
    Photograph VARBINARY(MAX)

NB: In this case, the standard constraint name of DF_Engineers_HourlyRate has been used. This is the name that the table designer would generate for this constraint.

The second situation in which you may create a default constraint is when you are adding a new, non-nullable column to a table with existing data. If you add such a column without a default constraint, as in the following example, you will receive an error message.

ALTER TABLE Engineers ADD OvertimeRate MONEY NOT NULL -- Error

In order to add a required column, a default constraint must be included with the column specification. The syntax of the constraint is identical to that used when creating a default value during the creation of a table. The following example adds an overtime rate column to the Engineers table and automatically updates every existing row to use the default value of £30. Try executing the statement then reviewing the data.

CONSTRAINT DF_Engineers_OvertimeRate DEFAULT 30

Finally, you may wish to add a default constraint to an existing table. This uses the ALTER TABLE statement. The statement syntax is similar to that for adding a column specification except that in this case you specify that you wish to add a default constraint. As no column is being added, the previous default constraint syntax must be modified slightly to specify the name of the column being modified. The complete syntax is therefore:

ALTER TABLE table-name
ADD CONSTRAINT constraint-name DEFAULT default-value FOR column-name

We can demonstrate this by adding a new default constraint to the CustomerComplaints table. In this table we want the creation date and time for each row to be recorded. Rather than specifying a literal date value, we will use a function to determine the value for each new row. The function to retrieve the current date and time is getdate().

ALTER TABLE CustomerComplaints
ADD CONSTRAINT DF_CustomerComplaints_ComplaintTime DEFAULT getdate()
FOR ComplaintTime

Once the above constraint is added, the creation time will be recorded automatically for all new complaints. Try adding a row to the table using the following command and then review the data to see the effect.

INSERT INTO CustomerComplaints
    (ComplaintReference, CustomerNumber, Complaint)
    (4, 2, 'Bill information is incorrect.')

There are two more default constraints required in the JoBS database. The first of these will set the default customer creation date in the Customers table. In this case, we only want to hold the date of creation, with the time defaulting to midnight. For an explanation of the date processing involved, see the article, "Retrieving the Date From a SQL Server DateTime".

Add the default constraint using the following statement:

ALTER TABLE Customers ADD CONSTRAINT DF_Customers_CreatedDate
DEFAULT convert(DATETIME, floor(convert(FLOAT, getdate()))) FOR CreatedDate

Finally, we will specify that a new, random GUID will be generated as the JobId for each new row in the Jobs table. This will remove the requirement to manually set this table's primary key values. The function to generate a new GUID is named newid().


Dropping a Default Constraint

The final common task to be considered in this article is the deletion of an existing default value. You may need to drop a default constraint if you wish to recreate it with a new value, if you want to drop a column that it refers to, or if you only added the constraint so that you could create a non-nullable column. The syntax for dropping a constraint is as follows:

ALTER TABLE table-name DROP CONSTRAINT constraint-name

For example, to drop the default value for the JobId column of the Jobs table, you could execute the following command:

27 August 2008