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.


Search Keys

  • 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 date

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;


Search Keys

  • 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
  • alter table set column
  • column value
  • insert values in sql