建表约束

 思考题

CREATE TABLE national2 (
 name VARCHAR2(20)
)
select * from national2

INSERT INTO national2(name) VALUES('中国');
INSERT INTO national2(name) VALUES('美国');
INSERT INTO national2(name) VALUES('巴西');
INSERT INTO national2(name) VALUES('荷兰');

 

 SQL :<>不等

SELECT t1.name,t2.name  FROM national2 t1 ,national2 t2  where t1.name<>t2.name

 

 

 

 约束 ：(重点)

约束的分类（五种）

 

1.主键约束 ：

方式一：PRIMARYKEY

加了主键约束，pid不能重复和为null

CREATE TABLE person1(
pid VARCHAR2(20) PRIMARY KEY ,
name VARCHAR2(20),
age NUMBER(3),
birthday DATE,
sex VARCHAR2(20) DEFAULT'男'
)

 

方式二：constraint

 

CREATE TABLE person2(
pid VARCHAR2(20) ,
name VARCHAR2(20),
age NUMBER(3),
birthday DATE,
sex VARCHAR2(20) DEFAULT'男',
CONSTRAINT person2_pid_pk PRIMARY KEY(pid)
)

 插入数据，sex有默认可以不写

INSERT INTO person2(pid,name,age,birthday) VALUES('111','PO',12,TO_DATE('2019-02-12','yyyy-mm-dd'))

 

 pid重复

INSERT INTO person2(pid,name,age,birthday) VALUES('111','PO2',13,TO_DATE('2019-04-12','yyyy-mm-dd'))

 

 报错

 

 插入pid=null

INSERT INTO person2(pid,name,age,birthday) VALUES('','PO2',13,TO_DATE('2019-04-12','yyyy-mm-dd'))

 

 报错

 

2,非空约束 ：NOT NULL

CREATE TABLE person2(
pid VARCHAR2(20) ,
name VARCHAR2(20) NOT NULL,
age NUMBER(3) NOT NULL,
birthday DATE,
sex VARCHAR2(20) DEFAULT'男',
CONSTRAINT person2_pid_pk PRIMARY KEY(pid)
)

 

 3.唯一约束（UNIQUE）

 表示该字段值是唯一，不允许重复

CREATE TABLE person3(
pid VARCHAR2(20) ,
name VARCHAR2(20) UNIQUE NOT NULL,
age NUMBER(3),
birthday DATE,
sex VARCHAR2(20) DEFAULT'男',
CONSTRAINT person3_pid_pk PRIMARY KEY(pid)
)

 

INSERT INTO person3(pid,name,age,birthday) VALUES('111','PO1',13,TO_DATE('2019-04-12','yyyy-mm-dd'))

 

 第二条数据无法插入

INSERT INTO person3(pid,name,age,birthday) VALUES('222','PO1',13,TO_DATE('2019-03-12','yyyy-mm-dd'))

 

 

 

CREATE TABLE person3(
pid VARCHAR2(20) ,
name VARCHAR2(20) NOT NULL,
age NUMBER(3),
birthday DATE,
sex VARCHAR2(20) DEFAULT'男',
CONSTRAINT person3_pid_pk PRIMARY KEY(pid),
CONSTRAINT person3_name_uk UNIQUE(name)
)

 

 

4.检查约束（CHECK）

检查一个列中插入的内容是否合法，如年龄的范围

CREATE TABLE person4(
pid VARCHAR2(20) ,
name VARCHAR2(20) NOT NULL,
age NUMBER(3) CHECK(age BETWEEN 0 AND 150),
birthday DATE,
sex VARCHAR2(20) DEFAULT'男' CHECK(sex IN('男','女','中')),
CONSTRAINT person4_pid_pk PRIMARY KEY(pid),
CONSTRAINT person4_name_uk UNIQUE(name)
)

 

插入 报错非法sql

INSERT INTO person3(pid,name,age,birthday) VALUES('1','PO1',200,TO_DATE('2019-03-12','yyyy-mm-dd'))

性别

INSERT INTO person4(pid,name,age,birthday,sex) VALUES('2','PO2',100,TO_DATE('2019-03-12','yyyy-mm-dd'),'无')

 

 

5.主外键约束

CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR2(20),
bprice NUMBER(5,2)
) 

 

此时修改一下book表

想和person4表产生关联

CREATE TABLE person4(
pid VARCHAR2(20) ,
name VARCHAR2(20) NOT NULL,
age NUMBER(3) CHECK(age BETWEEN 0 AND 150),
birthday DATE,
sex VARCHAR2(20) DEFAULT'男' CHECK(sex IN('男','女','中')),
CONSTRAINT person4_pid_pk PRIMARY KEY(pid),
CONSTRAINT person4_name_uk UNIQUE(name)
)

 

DROP TABLE book;
CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR2(20),
bprice NUMBER(5,2),
pid VARCHAR2(20) 
)

 

不加这一行修改表会报：表已经存在的错误

DROP TABLE book;

此时person4表的内容

插入数据

INSERT INTO book(bid,bname,bprice,pid) VALUES('1','都是仙尊',100,1)

此时我插入的书是藏地密码，人是3(注意此时person4表没有这个人)

INSERT INTO book(bid,bname,bprice,pid) VALUES('2','藏地密码',100,3)

 

这样的结果显然是不对的

 

DROP TABLE book;
CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR2(20),
bprice NUMBER(5,2),
pid VARCHAR2(20) ,
CONSTRAINT person4_book_pid_fk FOREIGN KEY(pid) REFERENCES person4(pid)
)

 

REFERENCES person4(pid)：意思是参考person4表中的pid 
注意：这样修改表之后表中的数据清空了

此时插入数据

INSERT INTO book(bid,bname,bprice,pid) VALUES('2','藏地密码',100,4)

 

正常插入的数据

INSERT INTO book(bid,bname,bprice,pid) VALUES('2','藏地密码10',100,2)

 

 说明：

 执行删除person4

DROP TABLE person4;

 

 

 

6.级联删除

ON DELETE CASCADE：可以级联删除

DROP TABLE book;
DROP TABLE person4;

CREATE TABLE person4(
pid VARCHAR2(20) ,
name VARCHAR2(20) NOT NULL,
age NUMBER(3) ,
birthday DATE,
sex VARCHAR2(20) DEFAULT'男' ,
CONSTRAINT person4_pid_pk PRIMARY KEY(pid),
CONSTRAINT person4_name_uk UNIQUE(name),
CONSTRAINT person4_age_uk CHECK(age BETWEEN 0 AND 150),
CONSTRAINT person4_sex_uk CHECK(sex IN('男','女','中'))
);

CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR2(20),
bprice NUMBER(5,2),
pid VARCHAR2(20) ,
CONSTRAINT person4_book_pid_fk FOREIGN KEY(pid) REFERENCES person4(pid) ON DELETE CASCADE
)

INSERT INTO person4(pid,name,age,birthday) VALUES('1','PO1',100,TO_DATE('2019-03-12','yyyy-mm-dd'));
INSERT INTO person4(pid,name,age,birthday,sex) VALUES('2','PO2',100,TO_DATE('2019-03-12','yyyy-mm-dd'),'女')
INSERT INTO book(bid,bname,bprice,pid) VALUES('1','都是仙尊',100,1);
INSERT INTO book(bid,bname,bprice,pid) VALUES('2','藏地密码10',500,2)

 

此时删除操作：成功删除

delete from person4 where pid=1