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.

You can also search these topics using sql default constraint, sql default value, sql alter table default value, alter table alter column default, alter table set default, alter table default, alter column default, alter table set default value, sql server create table default value, alter table with default value.

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;

You can also search these topics using sql column default value, sql server column default value, sql server add default constraint, alter table add column default value, sql server default constraint, sql server default value, create table default value, alter table set column, insert values in sql.