sql_default

SQL DEFAULT Constraint

SQL DEFAULT Constraint

The DEFAULT constraint is used to specify a default value for a column. This default value will be automatically inserted into the column if an INSERT INTO statement does not provide a specific value for that column.

This is useful for ensuring columns have a sensible value even if one isn’t explicitly provided, like setting a default status for new accounts or a default registration date.

SQL DEFAULT Constraint on CREATE TABLE

You can define a DEFAULT constraint when creating a table. The following SQL sets a default value for the City column and the RegistrationDate column when the Customers table is created:

Standard SQL Syntax:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(255) NOT NULL,
    ContactName VARCHAR(255),
    City VARCHAR(100) DEFAULT 'Unknown',
    Country VARCHAR(100) DEFAULT 'USA',
    RegistrationDate DATE DEFAULT CURRENT_DATE -- Uses the current date as default
);

In this example:

• If a new customer is added without specifying a City, their city will automatically be set to ‘Unknown’.
• If Country is not specified, it defaults to ‘USA’.
• If RegistrationDate is not specified, it defaults to the current date at the time of insertion. CURRENT_DATE is a standard SQL function. Other similar functions include CURRENT_TIMESTAMP (date and time) and NOW() (common in PostgreSQL and MySQL for current timestamp).

Note: For system-generated timestamps, SQL Server often uses GETDATE() or SYSDATETIME(). The standard SQL function is CURRENT_TIMESTAMP.

SQL DEFAULT Constraint on ALTER TABLE

To add a DEFAULT constraint to an existing column in an existing table, use the ALTER TABLE statement. The syntax can vary slightly between database systems.

PostgreSQL / MySQL / SQL Server (adding default to an existing column):

-- For PostgreSQL & MySQL
ALTER TABLE Customers
ALTER COLUMN Country SET DEFAULT 'Canada';

-- For SQL Server (often involves adding a named constraint)
ALTER TABLE Customers
ADD CONSTRAINT DF_Customers_Country DEFAULT 'Canada' FOR Country;

Oracle:

ALTER TABLE Customers
MODIFY Country DEFAULT 'Canada';

Important: Adding a default constraint usually only affects future INSERT operations. It typically does not change existing rows in the table unless explicitly updated.

To DROP a DEFAULT Constraint

To remove a DEFAULT constraint, you also use ALTER TABLE. Again, syntax can differ.

PostgreSQL / MySQL:

ALTER TABLE Customers
ALTER COLUMN Country DROP DEFAULT;

SQL Server (requires knowing the constraint name):

-- First, find the constraint name (e.g., using SQL Server Management Studio or system stored procedures)
-- Let's assume the constraint name is DF_Customers_Country
ALTER TABLE Customers
DROP CONSTRAINT DF_Customers_Country;

Oracle:

ALTER TABLE Customers
MODIFY Country DEFAULT NULL; -- Sets the default to NULL, effectively removing the previous specific default value

Practical Application:

• Setting a default OrderStatus to ‘Pending’ for new orders.
• Assigning a CreationTimestamp with DEFAULT CURRENT_TIMESTAMP for audit trails.
• Providing a default IsActive flag to TRUE for new user accounts.

Learning Obstacle: Managing named vs. unnamed default constraints can be a hurdle, especially when dropping them in systems like SQL Server. It’s good practice to name your constraints if your RDBMS allows easy naming for defaults.