创建带主键和约束的表
创建带组合主键和外键的表
Student (学生表)
CREATE TABLE Student
(
sclass varchar(10) NOT NULL, --- 限制非空
snumb varchar(10) PRIMARY KEY, --- 设置为主键
sname varchar(40) NOT NULL, --- (下)设置默认值男，约束选项为男女
sgender varchar(4) DEFAULT ‘男‘ CONSTRAINT consgender CHECK (sgender IN(‘男‘, ‘女‘)),
sbirthday date,
sage int CONSTRAINT consage CHECK (sage >= 0 and sage <= 150)
)
CONSTRAINT 约束名 CHECK (条件)
SELECT INTO Student VALUES(‘电气51‘, ‘10002‘, ‘王涛‘, ‘男‘, ‘1993-2-1‘, 18)
INSERT INTO Student(sclass, snumb, sname)
VALUES(‘电气51‘, ‘10004‘, ‘王涛‘) ---> 产生默认性别男
SELECT * FROM Student
INSERT INTO Student(sclass, snumb)
VALUES(‘电气51‘, ‘10005‘) ---- 出现错误: 不能将值 NULL 插入列 ‘sname‘
以上为，加了约束之后的效果
CREATE TABLE Course
(
cnumb varchar(10) PRIMARY KEY, --- 设为主键
cname varchar(40) NOT NULL, --- 非空
chours int CONSTRAINT conshour CHECK(chours >= 0 and chours <= 300),
credit decimal(5,2) CONSTRAINT conscredit CHECK(credit >= 0 and credit < 20),
intro text --- text字段存放一段文字
)
INSERT INTO Course
VALUES(‘ENP09001‘, ‘理论力学‘, 56, 3, ‘研究物体机械运动的基本规律‘)
SELECT * FROM Course
study (选课表)
CREATE TABLE Study
(
snumb varchar(10),
cnumb varchar(10),
score decimal(5,2),
PRIMARY KEY (snumb, cnumb), ---- 设为组合主键
FOREIGN KEY (snumb) REFERENCES Student(snumb), ---- 声明是什么表中的外键(即:插入数据时，必须要在Student和Course存在才可以)
FOREIGN KEY (cnumb) REFERENCES Course (cnumb)
)
INSERT INTO Study
VALUES(‘10001‘, ‘ENP09001‘, 90) --- snumb和cnumb必须要在Student和Course存在才可以
SELECT * FROM Student
SELECT * FROM Course
SELECT * FROM Study
3.总结：
原文：http://www.cnblogs.com/douzujun/p/6413535.html