Using the DEFAULT Clause in a CREATE TABLE Statement to Set Default Column Values

A default column value is the character string or number that you want the DBMS to enter into a column when you don't provide a value for the column. You learned how to create default column values in Tip 49, "Using the Transact-SQL CREATE DEFAULT Statement to Set a Column Default," and how to bind them to multiple columns in one or more tables in Tip 50, "Using the MS-SQL Server Stored Procedure sp_bindefault to Bind a User-Created Default to a Table Column." Unfortunately, the Transact-SQL CREATE DEFAULT statement and the sp_bindefault stored procedure are available to you only if you are working with MS-SQL Server.

The standard SQL-92 CREATE TABLE statement (available on all SQL relational DBMS products) gives you the ability to define default values for columns when you create a table. Not only is setting default column values standard across DBMS products, but it is also simpler than the Transact-SQL default value creation and binding process.

To define a column default value, simply add the keyword DEFAULT followed by the default value to the column definition in a CREATE TABLE statement. For example, the SQL CREATE TABLE statement

   CREATE TABLE employee
  (employee_ID             INTEGER,
   first_name              VARCHAR(20)  NOT NULL,
   last_name               VARCHAR(30)  NOT NULL,
   social_security_number  CHAR(11)     DEFAULT 'Unknown',
   street_address          VARCHAR(35)  DEFAULT 'Unknown',
   health_card_number      CHAR(15)     DEFAULT 'Applied For',
   sheriff_card_number     CHAR(15)     DEFAULT 'Applied For',
   hourly_rate             NUMERIC(5,2) DEFAULT 10.00,
   bonus_level             INTEGER      DEFAULT 1,
   job_rating_90days       SMALLINT,
   job_rating_180days      SMALLINT,
   job_rating_1year        SMALLINT
   PRIMARY KEY (employee_ID))

defines default values for SOCIAL_SECURITY_NUMBER, STREET_ADDRESS, HEALTH_CARD_NUMBER, SHERIFF_CARD_NUMBER, HOURLY_RATE, and BONUS_LEVEL. As such, when you execute the SQL INSERT statement

 INSERT INTO employee
  (employee_ID, first_name, last_name,
   social_security_number, street_address)
VALUES (1, 'Konrad', 'King', NULL, '77 Sunset Strip')

the DBMS will set the HEALTH_CARD_NUMBER and SHERIFF_CARD_NUMBER columns to "Applied For," the HOURLY_RATE column to 10.00, and the BONUS_LEVEL column to 1. Although the SOCIAL_SECURITY_NUMBER and STREET_ADDRESS columns have defaults, the default values were not used for the columns since the INSERT statement set the SOCIAL_SECURITY_NUMBER column to NULL and the STREET_ADDRESS column to "77 Sunset Strip." Finally, since the CREATE TABLE in the current example, did not define default values for the three job rating columns (JOB_RATING_90DAYS, JOB_RATING_180DAYS, and JOB_RATING_1YEAR), the DBMS will set these columns to NULL.

Note

Before you set column default values in the CREATE TABLE statement, check to see if your DBMS will allow you to change or stop using the defaults after you've created the table. MS-SQL Server will not let you use the ALTER TABLE statement to add, change, or drop (remove) any column defaults you define in a CREATE TABLE statement. (You can use the ALTER TABLE statement to add a new column and assign a default value to the column. However, once the column is part of a table, you cannot change its default value). If you are using MS-SQL Server, you can get around this shortcoming by using the Transact-SQL CREATE DEFAULT statement to create a named column default object outside the table definition. MS-SQL Server lets you bind a name column default to a column using the sp_bindefault stored procedure. You can change the value of a named column default at any time by unbinding the default from all columns, dropping it, re-creating the column default with a new value, and then rebinding it to the columns in one or more tables.