Class 4 - Constraints

CONSTRAINTS :
Primary Key, Foreign Key,Unique ,Default Key,Check,Null

Primary key
NEW TABLE : create table abc (sl int identity(1,1), name varchar(100), constraint PK_sl Primary key(Sl))

ON EXISTING : ALTER TABLE ABC ADD CONSTRAINT pk_sL PRIMARY KEY (SL)

DROPPING : ALTER TABLE ABC DROP CONSTRAINT pk_sL


Foreign Key
NEW TABLE : CREATE TABLE ABCD (SL INT, PLACE VARCHAR(100),DISTRICT VARCHAR(50), CITY VARCHAR(50), CONSTRAINT FK_sl$abc$sl Foreign Key (Sl) references abc(Sl))

ON EXISTING : alter table abcd ADD constraint FK_Sl$abc$sl FOREIGN KEY (SL) REFERENCES ABC(SL)

DROPPNG : alter table abcd drop constraint FK_Sl$abc$sl


unique
NEW TABLE : create table abcde (sl int, rollnum int, classname varchar(10),
Constraint UQ_rollnum Unique(rollnum))

ON EXISTING : ALTER TABLE ABCDE ADD CONSTRAINT uq_ROLLNUM UNIQUE CLUSTERED (ROLLNUM)

DROPPING : ALTER TABLE ABCDE DROP CONSTRAINT UQ_ROLLNUM


check
NEW TABLE : CREATE TABLE ABCDEF (SL INT, AMOUNT INT, CONSTRAINT ch_AMOUNT CHECK(AMOUNT>5))

ON EXISTING : ALTER TABLE ABCDEF ADD CONSTRAINT CH_AMOUNT CHECK(AMOUNT>5)

DROPPING : ALTER TABLE ABCDEF DROP CONSTRAINT CH_AMOUNT


default
CREATE TABLE ABCDEFF (SL INT, MARKS INT DEFAULT 35)

Null / not null

CONSTRAINTS IN SQL SERVER

CONSTRAINTS :
Primary Key, Foreign Key,Unique ,Default Key,Check,Null

primary key
NEW TABLE : create table abc (sl int identity(1,1), name varchar(100), constraint PK_sl Primary key(Sl))

ON EXISTING : ALTER TABLE ABC ADD CONSTRAINT pk_sL PRIMARY KEY (SL)

DROPPING : ALTER TABLE ABC DROP CONSTRAINT pk_sL


Foreign Key
NEW TABLE : CREATE TABLE ABCD (SL INT, PLACE VARCHAR(100),DISTRICT VARCHAR(50), CITY VARCHAR(50), CONSTRAINT FK_sl$abc$sl Foreign Key (Sl) references abc(Sl))

ON EXISTING : alter table abcd ADD constraint FK_Sl$abc$sl FOREIGN KEY (SL) REFERENCES ABC(SL)

DROPPNG : alter table abcd drop constraint FK_Sl$abc$sl


unique
NEW TABLE : create table abcde (sl int, rollnum int, classname varchar(10),
Constraint UQ_rollnum Unique(rollnum))

ON EXISTING : ALTER TABLE ABCDE ADD CONSTRAINT uq_ROLLNUM UNIQUE CLUSTERED (ROLLNUM)

DROPPING : ALTER TABLE ABCDE DROP CONSTRAINT UQ_ROLLNUM


check
NEW TABLE : CREATE TABLE ABCDEF (SL INT, AMOUNT INT, CONSTRAINT ch_AMOUNT CHECK(AMOUNT>5))

ON EXISTING : ALTER TABLE ABCDEF ADD CONSTRAINT CH_AMOUNT CHECK(AMOUNT>5)

DROPPING : ALTER TABLE ABCDEF DROP CONSTRAINT CH_AMOUNT


default
CREATE TABLE ABCDEFF (SL INT, MARKS INT DEFAULT 35)

Null / not null