Friday, February 15, 2013

How to use DEFAULT Constraint in SQL Server

SQL Server DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.The default value will be added to all new records, if no other value is specified.

Creating DEFAULT Constraint with CREATE TABLE Statement
The following SQL creates a DEFAULT constraint on the "City" column when the "Customers" table is created:

My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
City varchar(255) DEFAULT 'Hyderabad'
Gender varchar(50)
)

INSERT Statement for Table with DEFAULT Constraint values
When a Field is declared as DEFAULT , it will take the default value specified , we no need to insert this value in the INSERT INTO statement.So we have to ignore or skip it.This we can do as follows :

INSERT INTO Customers values (123,'Tamatam','Reddy',DEFAULT,'Male')

Here , In the INSERT INTO statement we passed DEFAULT as a value for the DEFAULT Value City.,so that it will take the Default value specified(Hyderabad) in the Table Creation.

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
C_Id int,
OrderDate Date DEFAULT GETDATE()
)

Creating SQL DEFAULT Constraint with ALTER TABLE Statement
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

SQL Server / MS Access:
ALTER TABLE Customers ALTER COLUMN City SET DEFAULT 'Hyderabad'

MySQL:
ALTER TABLE Customers ALTER City SET DEFAULT 'Hyderabad'

Oracle:
ALTER TABLE Customers MODIFY City DEFAULT 'Hyderabad'

To drop a DEFAULT constraint
SQL Server / Oracle / MS Access:
ALTER TABLE Customers ALTER COLUMN City DROP DEFAULT

MySQL:
ALTER TABLE Customers ALTER City DROP DEFAULT

--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog