SQL创建表以及约束

DROP TABLE teacher purge;
CREATE TABLE teacher(
tno Number(4),
tname Varchar2(6),
tage Number(3),
tdate DATE,

--创建主键约束（使用constraint 关键字引导创建）
Constraint pk_tno PRIMARY key(tno),
--创建唯一约束
Constraint uk_tname Unique(tname),
--创建检查约束
Constraint check_tage CHECK(tage between 0 and 250)
);
--插入数据
INSERT INTO teacher VALUES(1001,'jack',34,SYSDATE);
INSERT INTO teacher VALUES(1002,'tom',34,SYSDATE);
INSERT INTO teacher VALUES(1003,'kimi',34,SYSDATE);
SELECT * from teacher;

如果要指定约束，就需要使用CONSTRAINT

外键约束

--删除数据表
DROP TABLE sc PURGE;
DROP TABLE course PURGE;
DROP TABLE teacher PURGE;
DROP TABLE student PURGE;
--创建数据表
create table Student(
sno varchar2(10),
sname varchar2(20),
sage NUMBER(2),
ssex varchar(5),
--唯一主键
Constraint pk_sno Primary KEY(sno)
);
/*初始化学生信息*/
insert into student values('s001','jack',25,'男');
insert into student values('s002','tom',26,'男');
insert into student values('s003','kimi',27,'女');
insert into student values('s004','KK',19,'男');
insert into student values('s005','XX',19,'男');
commit;
create table teacher(
tno varchar2(10),
tname varchar2(20),
constraint pk_tno primary key(tno));
/*初始化老师信息*/
insert into teacher values('t001','LY');
insert into teacher values('t002','SY');
insert into teacher values('t003','HMX');
commit;

create table course(
cno varchar2(10),
cname varchar2(20),
tno varchar2(20),
constraint pk_con primary key(cno)
);
/*初始化课程信息*/
insert into course values('c001','J2SE','t001');
insert into course values('c002','Java WEB','t001');
insert into course values('c003','SSH','t001');
insert into course values('c004','ORACLE','t001');
insert into course values('c005','C#','t001');
insert into course values('c006','JAVASCRIPT','t001');
commit;

--创建学生对应的课程成绩
create table sc(
sno varchar2(10),
cno varchar2(10),
score number(4,2),
--主键约束
constraints pk_sno primary key(sno),
--创建sno外键约束
constraint fk_sno FOREIGN kEY(sno) REFERENCES student(sno),
--创建cno外键约束
constraints fk_cno Foreign KEY(cno) REFERENCES course(cno)
);
insert into sc values('s001','c001',89.8);
insert into sc values('s002','c001',93.4);
insert into sc values('s003','c002',80.7);
select * from sc;

小结

1.外键约束指的是数据表中的某个字段的数据来源依赖于其他数据表中的某个字段的数据，也就是说当前的数据表中插入的数据必须在其他数据表中有记录
2.使用外键约束后，表之间就存在一定的依赖关系，依赖的数据表（引用其他表中数据的表）被依赖的数据表叫作父表或者主表
3.外键的约束语COMSTRAINT fk_sno FOREIGN KEY（sno）REFERENCES student(sno);
4.删除数据的时候要先删除子表，再删除父表
5.创建数据表的时候，先创建父表再创建子表