sql constraint default

Posted by Vibheesh under Sql Server on 12/28/2011 | Points: 10 | Views : 2680 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'

The above table when i created in sql i set default constraint as 'Sandnes'.after that i tried to alter as 'SANDNES' using below code.
now i want to alter the constraint ,i used the below query but it is not working.
is it possible to alter once we set default value.kindly give the reply.i reffered the tutorial site www.w3schools.com,www.tutorialpoint.com

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

another exaple

CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2) DEFAULT 5000.00,
PRIMARY KEY (ID)
);

ALTER TABLE CUSTOMERS
MODIFY SALARY DECIMAL (18, 2) DEFAULT 5000.00;

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sksamantaray on: 12/30/2011 [Member] Silver | Points: 25

0	In Sql Server when you use default keyword while creating a table, Be default it creates a constraint for you . You can see it , while clicking on tree str of table in your database explorer. Moreover if you want to change anything first you need to drop the existing constraint You can not Alter the constraint so,drop it and recreate with modifications. You can check it: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' ) insert into Persons(P_Id,LastName,FirstName,Address) values(1,'samantaray','sanjay','bbsr') select * from Persons alter table persons drop constraint DF__Persons__City__4AB81AF0 alter table persons ADD CONSTRAINT DF__Test1__Val1__32E0915F DEFAULT 'SAD' for city insert into Persons(P_Id,LastName,FirstName,Address) values(2,'samantaray','sanjay','bbsr') select * from Persons Thanks, Sanjay Vibheesh, if this helps please login to Mark As Answer. \| Alert Moderator

In Sql Server when you use default keyword while creating a table, Be default it creates a constraint for you .
You can see it , while clicking on tree str of table in your database explorer.
Moreover if you want to change anything first you need to drop the existing constraint
You can not Alter the constraint so,drop it and recreate with modifications.
You can check it:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes' )
insert into Persons(P_Id,LastName,FirstName,Address) values(1,'samantaray','sanjay','bbsr')
select * from Persons
alter table persons drop constraint DF__Persons__City__4AB81AF0

alter table persons ADD CONSTRAINT DF__Test1__Val1__32E0915F DEFAULT 'SAD' for city
insert into Persons(P_Id,LastName,FirstName,Address) values(2,'samantaray','sanjay','bbsr')
select * from Persons

Thanks,
Sanjay

Vibheesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/23/2012 [Member] Starter | Points: 25

0	SQL SERVER DEFAULT CONSTRAINT IS NOT NULL REGARDS sriram Vibheesh, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts