sql constraint default

Posted by Vibheesh under Sql Server on 12/28/2011 | Points: 10 | Views : 1596 | Status : [Member] | Replies : 2
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;




Responses

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

Up
0
Down

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

Up
0
Down
SQL SERVER DEFAULT CONSTRAINT IS NOT NULL

REGARDS


sriram

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

Login to post response