Add Default Constraint

-------ADD A DEFAULT CONSTRAINT------------------

CREATE TABLE GENDER

(

GENDERID INT PRIMARY KEY NOT NULL,

GENDER VARCHAR(10)

)

INSERT INTO GENDER VALUES(1,'MALE')

INSERT INTO GENDER VALUES(2,'FEMALE')

INSERT INTO GENDER VALUES(3,'UNKNOWN')

SELECT * FROM GENDER

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

CREATE TABLE PERSON

(

NAME VARCHAR(44),

GENDERID INT FOREIGN KEY REFERENCES GENDER(GENDERID)

)

INSERT INTO PERSON VALUES('ANURAG',1)

INSERT INTO PERSON VALUES('SIBA',1)

INSERT INTO PERSON VALUES('SABITA',2)

INSERT INTO PERSON VALUES('MUKUA',3)

SELECT * FROM PERSON

--LETS INSERT A RECORD INTO PERSON

INSERT INTO PERSON(NAME) VALUES('NIBEDITA')

SELECT * FROM PERSON

--LOOK AT THE NIBEDITA RECORD...WE FIND THAT IT'S NULL. WE NEED A DEFAULT CONSTRAINT..WHENEVER WE DONT KNOW ABOUT THE GENDER IT SHOULD AUTOMATICALLY MAKE IT 3

--ADD DEFAULT CONSTRAINT

ALTER TABLE PERSON

ADD CONSTRAINT DF_PERSON_GENDERID

DEFAULT 3 FOR GENDERID

--WE ARE GIVING A MEANINGFUL NAME LIKE : DF FOR DEFAULT,,,THEN TABLE NAME PERSON,,,THEN COLUMNNAME GENDERID

--LETS INSERT ANOTHER RECORD

INSERT INTO PERSON(NAME) VALUES('ANITA')

SELECT * FROM PERSON

O/P

ANURAG            1

SIBA              1

SABITA            2

MUKUA             3

NIBEDITA          NULL

ANITA             3

--COOOL :)

--LETS DO A TWIST NOW...

INSERT INTO PERSON(NAME,GENDERID) VALUES('JAKAKA',NULL)

--WILL THE DEFAULT FIRE FOR 'JAKAKA' ???????????????

SELECT * FROM PERSON

--ANSWR IS NO

--------IF I WANT TO ADD A NEW COLUMN ,,,WITH DEFAULT

--LETS DO IT

ALTER TABLE PERSON

ADD COUNTRYID INT

CONSTRAINT DF_PERSON_COUNTRYID DEFAULT 0

SELECT * FROM PERSON

--COOL

--O/P

NAME                    GENDERID         COUNTRYID

ANURAG                        1           NULL

SIBA                          1           NULL

SABITA                        2           NULL

MUKUA                         3           NULL

NIBEDITA                   NULL          NULL

ANITA                         3           NULL

JAKAKA                      NULL          NULL

--DROP CONSTRAINT NAME

ALTER TABLE PERSON

DROP CONSTRAINT DF_PERSON_COUNTRYID

--TO CHECK WHETHER YOUR CONSTRAINT IS DROPPED OR NOT ...

--OBJECT EXPLORER-->DATABASE NAME-->TABLES-->PERSON TABLE-->CONSTRAINT

SELECT * FROM PERSON