Friday 11 July 2014

Microsoft Sql Server table and constraints Part 1

This is first part of a series of blog discussing tables,constraints in Microsoft Sql Server database. I have
created on database to store person information. For this I have used MSS 2008R2 version.


Drop table sample.gender

Create table sample.gender
(
id int primary key,
gender nvarchar(10) not null
)

Insert into Sample.gender values (1,'Male')
Insert into Sample.gender values (2,'Female')
Insert into sample.gender values (3,'Not Sure')

select * from sample.Gender

Drop table sample.person

Create table sample.person
(
id        int                primary key,
Name    nvarchar(30)    Not null,
LastName    nvarchar(30)    Not null,
Email    nvarchar(15)        not null,
Nationality nvarchar(20)    not null,
gender       int              not null
)

Alter table sample.person add constraint fk_con foreign key (gender) references sample.gender (id)

truncate table sample.person

Insert into sample.person values (1,'Jai','Sharma','vat@gmail.com','Indian',1)
Insert into sample.person values (2,'Ram','Jain','RJ@gmail.com','Srilankan',1)
Insert into sample.person values (3,'Hariharn','Dev','hd@gmail.com','Singapore',1)
Insert into sample.person values (4,'Anmol','Sethi','AS@gmail.com','Indian',1)
Insert into sample.person values (5,'Rajan','Chawala','RC@gmail.com','Srilankan',1)
Insert into sample.person values (6,'Anamika','Mukharji','AM@gmail.com','Bangladesh',2)
Insert into sample.person values (7,'Rupali','Sen','RS@gmail.com','Aussie',2)
Insert into sample.person(id,name,lastname,email,nationality) values (8,'Radhika','Reddy','RR@gmail.com','Aussie')

select * from sample.person
--Adding a default constraint
Alter table Sample.Person
add constraint dflt_tblPerson_gender default 3 for gender

---Adding an column in table
Alter table sample.person
add  city nvarchar(20)
constraint dflt_person_city default 'Melbourne'

---Drop a column from table
Alter table sample.person
drop column city



----Constraint cascade


---Check constraint

Alter table sample.person
add constraint chk_person_aga check (age > 0 and age <100)

Any comment,suggestions or correction is welcome.

No comments:

Post a Comment