Default Values

When you start creating tables and setting them so they can’t have NULL values, you’ll eventually need a way to define a default value for the column. A default value is one that a column would apply to its record if a value is not provided.

To specify the default value in a SQL statement, assign the desired value to the DEFAULT keyword. Here are examples:

CREATE TABLE character
(
    firstName NVARCHAR(50)
	, lastName NVARCHAR(50)
    , Planet NVARCHAR(20) DEFAULT N'Tatooine'
)

When in doubt, start on Tatooine!

You don’t have to use literal values for DEFAULT. You could use a function too.

CREATE TABLE character
(
    firstName NVARCHAR(50)
	, lastName NVARCHAR(50)
    , Planet NVARCHAR(20) DEFAULT N'Tatooine'
	, birthDate DATETIME DEFAULT GETDATE()
)

If the table exists already and you want to add a column that has a default value, use the ALTER TABLE command.

ALTER TABLE &let;TableName> ADD &let;ColumnName> DEFAULT &let;value>

Let’s assume the birthDate column hadn’t already been added to the character table.

ALTER TABLE character ADD COLUMN birthDate DATETIME DEFAULT GETDATE()

That’s all you have to remember in order to set a column to use a DEFAULT value. If you have any questions, send ’em in. I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *