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

left and right join in sql

count function in sql

update column value in sql

correlated subquery in sql

pattern matching operator in sql

sql server count distinct

sql outer join

sql replace special characters

sql get hour from timestamp

using case in sql

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;

Related Links

first day of year in sql

sqrt in sql server

sql average function

intersection in sql server

any keyword in sql

sql syntax

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.