SQL sever 约束

/*建库*/

use master

go

if exists(select * from sysdatabases where name = 'StudentDB')

create database StudentDB

on primary

(

name = 'StudentDB_data',

filename = 'E:\DATASTORE\StudentDB_data.mdf',

size = 10MB,

filegrowth = 1MB

)

log on

(

name = 'StudentDB_log',

filename = 'E:\DATASTORE\StudentDB_log.ldf',

size = 10MB,

filegrowth = 1MB

)

go

/*建学生表*/

use StudentDB

go

if exists(select * from sysobjects where name = 'Students')

drop table Students

go

create table Students

(

Id int identity(100,1) primary key,

Name varchar(10) not null,

Gender char(2) default('男'),

Age int not null,

ClassId int not null

)

go

/*插入*/

use StudentDB

go

insert into Students(Name, Gender, Age, ClassId)values('haode', '男', 200, 1)

insert into Students(Name, Gender, Age, ClassId)values('wangwu', default, 30, 1)

insert into Students(Name, Gender, Age, ClassId)values('lisi', '男', 10, 1)

insert into Students(Name, Gender, Age, ClassId)values('zhangsan',  '男', 20, 2)

go

/*查询*/

use StudentDB

go

select * from Students

go

/*建班级表*/

use StudentDB

go

if exists(select * from sysobjects where name = 'Classes')

drop table Classes

go

create table Classes

(

Id int identity(1,1) primary key,

Name varchar(10) not null

)

go

/*插入*/

use StudentDB

go

insert into Classes(Name)values('计算机')

insert into Classes(Name)values('软件')

go

/*查询*/

use StudentDB

go

select * from Classes

go

/*主键约束*/

use StudentDB

go

if exists(select * from sysobjects where name = 'pk_Id')

alter table Students

drop constraint pkId

alter table Students

add constraint pk_Age primary key(Id)

go

/*唯一约束*/

use StudentDB

go

if exists(select * from sysobjects where name = 'uq_Age')

alter table Students

drop constraint uq_Age

go

alter table Students

add constraint uq_Age unique(Age)

go

/*检查约束*/

use StudentDB

go

if exists(select * from sysobjects where name = 'ck_Age')

alter table Students

drop constraint ck_Age

go

alter table Students

add constraint ck_Age check(Age between 0 and 99)

go

/*默认值约束*/

use StudentDB

go

if exists(select * from sysobjects where name = 'df_Gender')

alter table Students

drop constraint df_Gender

go

alter table Students

add constraint df_Gender default('男')for Gender

go

/*外键约束*/

use StudentDB

go

if exists(select * from sysobjects where name = 'fk_ClassId')

alter table Students

drop constraint fk_ClassId

go

alter table Students

add constraint fk_ClassId foreign key(ClassId) references Classes(Id)

go