SQL DEFAULT Constraint
The SQL DEFAULT constraint provides a default value to a field when the user did not provide a specific value.
The default value will be added to all newly inserted records, if no other value is specified with SQL INSERT INTO statement by user.
Related Links
SQL DEFAULT Constraint on CREATE TABLE
The following SQL statement creates a DEFAULT constraint on the "AuthorName" field when the "Books" table is created:
Microsoft SQL Server / Oracle / Microsoft Access / MySql
CREATE TABLE Books
(
BookID INT,
BookName VARCHAR(255),
AuthorName VARCHAR(255) DEFAULT 'N/A',
BookPrice DECIMAL (7, 2) DEFAULT 100.00
);
In the above example, the following SQL statement creates a new table called Books and adds four fields. Here, AuthorName and BookPrice fields has default value.
Here, AuthorName field is set to 'N/A' and BookPrice field is set to '100.00' by default, so in case SQL INSERT INTO statement does not provide a value for these fields, then by default these fileds would be set to their default values.
To insert records on "Books" table, use the following SQL statements:
INSERT INTO Books (BookID, BookName, AuthorName)
VALUES (111, 'Sql Programming','Suresh Babu');
INSERT INTO Books (BookID, BookName, BookPrice)
VALUES (222, 'Sql Hacking', 225);
INSERT INTO Books (BookID, BookName, AuthorName, BookPrice)
VALUES (333, 'Sql Query Performance', 'Siva Kumar' 150);
The SQL statement above would insert three new records into the "Books" table.
In the first record we didn't specify a value for the "BookPrice" field in the INSERT INTO statement, it does get assigned the default value of "100.00".
In the second record we didn't specify a value for the "AuthorName" field in the INSERT INTO statement, it does get assigned the default value of "N/A".
After executing above query, the "Books" table look like this:
BookId | BookName | AuthorName | BookPrice |
---|---|---|---|
111 | Sql Programming | Suresh Babu | 100.00 |
222 | Sql Hacking | N/A | 225 |
333 | Sql Query Performance | Siva Kumar | 150 |
SQL DEFAULT Constraint on ALTER TABLE
To create a SQL DEFAULT constraint on the "AuthorName" field when the table is already created, use the following SQL statement:
For MySQL
ALTER TABLE Books
ALTER AuthorName SET DEFAULT 'Not Available';
For Microsoft SQL SERVER / Microsoft ACCESS
ALTER TABLE Books
ALTER COLUMN AuthorName SET DEFAULT 'Not Available';
For Oracle
ALTER TABLE Books
MODIFY AuthorName DEFAULT 'Not Available';
To DROP a DEFAULT Constraint
To drop or remove a DEFAULT constraint, use the following SQL statement:
For MySQL
ALTER TABLE Books
ALTER AuthorName DROP DEFAULT;
For Microsoft SQL SERVER / Microsoft ACCESS / ORACLE
ALTER TABLE Books
ALTER COLUMN AuthorName DROP DEFAULT;
Related Links