主键,外键等约束详解实例.docx

文档介绍：1、--创建表
create table tb_Dept
(
Deptid char(2) Primary key,
DeptName char(16) Not Null
)
2、--外键约束
create table tb_Student
(
Studid char(10) Primary key,
Studname char(8) Not null,
Deptid char(2) Not null,
Constraint FK_DeptID Foreign Key (Deptid)
References Tb_Dept(DeptID)
)
3、--外键约束简化形式,必须要求tb_Dept表中DeptID为主键,且数值类型相同
create table Tb_Student
(
StudId char(10) Primary key,
StudName char(8) Not null,
DeptID char(2) not null References Tb_Dept
)
4、--创建表,无主键
create table Tb_Class
(
ClassID char(8) not null,
ClassName varchar(30) not null,
DeptId char(2) not null,
ClassStuNumber int
)
5、--创建表,同时定义主键
create table Tb_Class
(
classid char(8) not null,
ClassName varchar(30) not null,
DeptID char(2) not null,
ClassStuNumber int
constraint PK_ClassID Primary key
(ClassID,ClassName)
)
6、--新增主键
Alter table Tb_class
ADD
Constraint PK_ClassID primary key(Classid)
7、--删除主键
Alter table tb_Class
Delete
Constraint PK_ClassID Primary key(ClassID)
8、--外键级联更新,删除,简化形式
Create table tb_student
(
studID char(10) Primary key,
StudName char(10) not null,
DeptID char(2) not null References tb_Dept
On Update cascade
on delete cascade
)
9、--外键级联更新,删除,标准
create table tb_student
(
studid char(10) Primary key,
StudName char(8) not null,
DeptID char(2) not null,
Constraint FK_Deptid foreign key(DeptID)
References Tb_Dept(Dep