ITPub博客

首页 > 数据库 > Oracle > SQL-Oracle07sql约束

SQL-Oracle07sql约束

Oracle 作者:qqlon 时间:2013-11-24 16:48:20 0 删除 编辑

思考题:

现在又一张国家表,里面只有一个国家名称的字段,内容如下:“中国、美国、巴西、荷兰”,
现在要求通过查询实现对战功能:
中国--美国
中国--巴西
中国--荷兰
美国--中国
美国--巴西
美国--荷兰

问:该程序如何实现?

分析:本程序只能使用笛卡尔积完成。

 


CREATE TABLE national(

 name VARCHAR2(30)
);
--增加测试数据
INSERT INTO national(name) values ('中国') ;
INSERT INTO national(name) values ('美国') ;
INSERT INTO national(name) values ('巴西') ;
INSERT INTO national(name) values ('荷兰') ;

查询时肯定是表自己关联自己

SELECT t1.name,t2.name
FROM national t1,national t2
WHERE t1.name<>t2.name;

 

约束(重点)

在数据库表的开发中,约束是必不可少的支持,使用约束可以更好
的保证数据库中数据的完整性。

约束的分类:

在实际中,约束主要分为以下五种约束:

主键约束:主键表示是一个唯一的标识,本身不能为空
          eg 身份证编号是唯一的,不可重复的,不可为空
唯一约束:在一个表中只允许建立一个主键约束,而其他列如果
不希望出现重复值的话,则就可以使用唯一约束。
检查约束:检查一个列的内容是否合法
 eg:年龄,只能在0~150间
 eg:性别,只能是男、女,中性
非空约束:姓名字段的内容不能为空
外键约束:在两张表中进行约束操作。

 

 

 

主键约束(PRIMARY KEY)

主键约束一般都在id上使用,而且本身已经默认了内容不能为空。
主键约束可以在建立表的时候指定:
eg:建立person表,在pid上增加主键约束。

CREATE TABLE person(

 pid VARCHAR2(18) PRIMARY KEY ,
 name VARCHAR2(200) ,
 age  NUMBER(3) ,
 birthday DATE  ,
 sex  VARCHAR2(2) DEFAULT '男'
);

 

或者
CREATE TABLE person(

 pid VARCHAR2(18) PRIMARY KEY ,
 name VARCHAR2(200) ,
 age NUMBER(3) ,
 birthday DATE ,
 sex  VARCHAR2(2) DEFAULT '男' ,
 CONSTRAINT person_pid_pk PRIMARY KEY(pid)  //可指定约束名称
) ;


非空约束(NOT NULL)

使用非空约束,表示一个字段的内容不允许为空,即:插入数据的时候
必须插入内容
DROP TABLE person ;
CREATE TABLE person
(
 pid VARCHAR2(18),
 name VARCHAR2(200)  NOT NULL,
 age NUMBER(3) NOT NULL,
 birthday DATE ,
 sex  VARCHAR2(2) DEFAULT '男',
 CONTRAINT person_pid_pk PRIMARY KEY(pid)
) ;

 

它再错误提示中,可以发现,已经明确的指明了错误所发生的字段名称。

ORA-01400:无法将NUll插入("SCOTT","PERSON","NAME")
ORA-01400:无法将NULL插入("SCOTT","PERSON","AGE")

 

 

 

唯一约束(UNIQUE)

表示一个字段中的内容时唯一的,其他列不允许重复。
假设:现在姓名中不允许出现重名的情况。
CREATE TABLE person
(
 pid  VARCHAR2(18)
 name VARCHAR2(200) UNIQUE NOT NULL ,
 age NUMBER(3) NOT NULL ,
 birthday DATE ,
 sex VARCHAR2(2) DEFAULT '男' ,
 CONSTRAINT person_pid_pk PRIMARY KEY(pid)
) ;


eg:向表中插入数据,数据插入两条

INSERT INTO person() VALUES
('',NULL,30,TO_DATE('1976-02-13','yyyy-mm-dd'),'女');

以创建1行

INSERT INTO person() VALUES
('',NULL,30,TO_DATE('1976-02-13','yyyy-mm-dd'),'女');

第1行出现错误:
ORA-00001:违反唯一的约束条件(SCOTT.SYS_C005282)

第一条数据正常插入,第二条数据,名字违反了唯一约束条件,
所以出现了以下的错误信息。
ORA-00001:违反唯一的约束条件(SCOTT.SYS_C005282)
 此错误信息,也是使用自动的约束名称编号,所以要想
指定约束的名称,也可以使用

 

CREATE TABLE person
(
 pid  VARCHAR2(18)
 name VARCHAR2(200) NOT NULL ,
 age NUMBER(3) NOT NULL ,
 birthday DATE ,
 sex VARCHAR2(2) DEFAULT '男' ,
 CONSTRAINT person_pid_pk PRIMARY KEY(pid),
 CONSTRAINT person_name_uk UNIQUED(name)
) ;

错误信息上已经明确的显示出了自己定义的约束名称


检查约束(CHECK)

使用检查约束来判断一个列中插入的内容是否合法,
例如:年龄的取值范围、性别的取值范围。

CREATE TABLE person
(
 pid  VARCHAR2(18) ,
 name  VARCHAR2(200)  NOT NULL ,
 age NUMBER(3) NOT NULL  CHECK(age BETWEEN 0 AND 150)
 birthday DATE ,
 sex  VARCHAR2(2) DEFAULT '男' CHECK(sex IN('男','女','中性')),
 CONSTRAINT person_pid_pk PRIMARY KEY(pid) ,
 CONSTRAINT  person_name_uk UNIQUE(name)
) ;

eg:插入错误的年龄
INSERT INTO person (pid,name,age,birthday,sex) VALUES
('111111111111111111','张三',300,TO_DATE('1976-02-13','yyyy-mm-dd'),'女') ;
第1行出现错误:
ORA-02290:违反检查约束条件(SCOTT.SYS_C005289)

错误信息:
第1行出现错误:
ORA-02290:违反检查约束条件(SCOTT.SYS_C005289)

eg:插入正确的性别
INSERT INTO person(pid,name,age,birthday,sex) VALUES
('111111111111111111','张三',30,TO_DATE('1976-02-13','yyyy-mm-dd'),'无') ;

错误信息:
ORA-02290:违反检查约束条件(SCOTTSYS_C005290)
以上的两条错误信息中所有约束错误都是采用自动命名的形式,那么在CHECK

 

CREATE TABLE person
(
 pid varchar2(18)
 name varchar2(200) NOT NULL ,
 age number(3) NOT NULL ,
 birthday DATE ,  
 sex varchar2(2)  DEFAULT '男',
 CONSTRAINT person_pid_pk PRIMARY KEY(pid),
 CONSTRAINT  person_name_uk UNIQUE(name),
 CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
 CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
) ;


再次插入错误的年龄:
INSERT INTO person(pid,name,age,birthday,sex)VALUES
('111111111111111111','张三',300,TO_DATE('1976-02-13','yyyy-mm-dd'),'女') ;

第1行出现错误:
ORA-02290:违反检查约束条件(SCOTT.PERSON_AGE_CK)


再次插入错误性别:

INSERT INTO person(pid,name,age,birthday,sex)VALUES
('111111111111111111','张三',300,TO_DATE('1976-02-13','yyyy-mm-dd'),'JGH') ;

第1行出现错误:
ORA-02290:违反检查约束条件(SCOTT.PERSON_SEX_CK)
======================
======================
======================
主外键约束(FOREIGN KEY)

针对两张表的约束。


eg:要求完成一个程序,一本书属于一个人。
 书本身应该是一张表,一个书中必然有一个字段表示属于哪个人的
CREATE TABLE person
(
 pid varchar2(18) ,
 name varchar2(200) NOT NULL ,
 age number(3) NOT NULL ,
 birthday DATE ,  
 sex varchar2(2)  DEFAULT '男',
 CONSTRAINT person_pid_pk PRIMARY KEY(pid),
 CONSTRAINT  person_name_uk UNIQUE(name),
 CONSTRAINT person_age_ck CHECK(age BETWEEN 0 AND 150),
 CONSTRAINT person_sex_ck CHECK(sex IN('男','女','中'))
) ;


CREATE TABLE book
(
 bid  NUMBER PRIMARY KEY NOT NULL ,
 bname  VARCHAR(30) ,
 bprice  NUMBER(5,2) ,
 pid  VARCHAR2(18) ,
 CONSTRAINT person_book_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;


先创建两张表,然后插入一些测试数据:
INSERT INTO person(pid,name,age,birthday,sex)VALUES
('111111111111111111','张三',30,TO_DATE('1976-02-13','yyyy-mm-dd'),'女') ;
INSERT INTO book(bid,bname,bprice,pid) VALUES
(1,'JAVA SE',89.9,'111111111111111111') ;

确实可符合要求

但是若写为:

INSER INTO book(bid,bname,bprice,pid) VALUES
(2,'JAVA SE',89.9,'000000000000000000') ;

此编号的人员并不存在,则此数据肯定不能被插入。(因为没有此书对应的人,但是不设置外键约束,则可以插入书)
此时,如果要想解决这样的问题,则肯定要使用主外键关联,关联之后子表的数据要跟随父表的数据内容。


INSERT INTO book(bid,bname,bprice,pid) VALUES
(2,'JAVA SE',89.9,'000000000000000000') ;

违反完整约束条件(SCOTT.PERSON_BOOK_PID_FK)-未找到父项关键字

此时,可以保证两张表的数据的完整性,不会出现找不到对应数据的情况。
在使用主-外键关联的时候也要有以下注意点:
 在子表中设置的外键在父表中必须是主键
 
 删除时应该先删除子表,在删除父表
DROP TABLE person ;
DROP TABLE book ;

错误:
表中的唯一/主键被外键引用


DROP TABLE book ;

ORA-02289:表已删除,

说明现在根本无法删除子表,因为存在这样的关联关系。
 
DROP TABLE book ;
DROP TABLE person ;

 


此时可以使用强制性的删除手段
(级联删除约束)
DROP TABLE book CASCADE CONSTRAINT ;
 不管约束,而直接删除,但是这种做法一般不使用。

 


回顾:

 emp和dept表,实际上这两张表也是有主-外键关联的。
eg:现在向emp表中增加一个50部门的员工
 在dept表中本身并不存在50部门的部门编号

INSERT INTO emp(empno,ename,job,sal,mgr,hiredate,comm,deptno)
VALUES (8888,'张三','经理',90000,null,sysdate,null,50) ;

ORA-02291:违反完整约束条件(SCOTT.FK_DEPTNO)-未找到父项关键字


在主-外键关联中可以使用级联删除的情况。

 以现在数据库中的数据为例 .


INSERT INTO emp(empno,ename,job,sal,mgr,hiredate,comm,deptno)
VALUES (8888,'','',90000,null,sysdate,null,50) ;


DELETE FROM person WHERE pid='111111111111111111';

要删除person表中标号为“111111111111111111”的人员,
但是此人员现在在book表中存在一本书。

 

SQL>DELETE FROM person WHERE pid='111111111111111111' ;
DELETE FROM person WHERE pid='111111111111111111' ;
*
第1行出现错误:
ORA-02292:违反完整约束条件(SCOTT.PERSON_BOOK_PID_FK)-已找到子记录

因为在子表中存在此项的关联,所以无法删除,那么,此时如果要想完成
删除操作的话,则必须先将book表中对应的数据删除掉。

 如果希望一个表中的数据在删除时,可以自动删除掉其对应的子表记录,
则就可以使用级联删除的操作。

 

CREATE TABLE book
(
 bid  NUMBER PRIMARY KEY NOT NULL ,
 bname VARCHAR(30) ,
 bprice  VARCHAR(5,2) ,
 pid  VARCHAR2(18) ,
 CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
) ;

更改表结构之后,之后再次执行删除操作

 

----------------
----------------
----------------
修改约束


如果一张表已经建立完成以后,可以为其添加约束。

DROP TABLE person CASCADE CONSTRAINT ;
CREATE TABLE person
(
 pid VARCHAR2(18) ,
 name VARCHAR2(200)  NOT NULL ,
 age NUMBER(3) NOT NULL ,
 birthday DATE ,
 sex VARCHAR2(2) DEFAULT '男',

) ;
此时,需要为表中添加若干个约束,添加约束的语法如下:
ALTER TABLE 表名称 ADD COSTRAINT 约束名称 约束类型(约束字段)
关于约束类型的命名一定要统一。

 PRIMARY KEY:主键字段_PK
 UNIQUE :字段_UK
 CHECK:字段_CK
 FOREIGN KEY:父字段_子字段_FK


eg:为person表添加该有的约束。

ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);
ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(pid);
ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);
ALTER TABLE person ADD CONSTRAINT person_sex_CK CHECK(sex IN ('男','女','中'));

 

DROP TABLE book ;
CREATE TABLE book
(
 bid NUMBER,
 bname VARCHAR(30) ,
 bprice  NUMBER(5,2) , 
 pid VARCHAR2(18)
) ;
ALTER TABLE book ADD CONSTRAINT book_pid_PK PRIMARY KEY(bid) ;
ALTER TABLE book ADD CONSTRAINT person_book_pid_FK FOREIGN KEY(pid) REFERENCES person(pid) ON;
DELETE CASCADE ;

 既然可以增加约束,那么就可以删除约束,删除约束的时候要指定约束的名称。
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称
eg:删除person表中的age和sex上的约束。

 

ALTER TABLE person DROP CONSTRAINT person_age_CK ;
ALTER TABLE person DROP CONSTRAINT person_sex_CK ;
ALTER TABLE book DROP CONSTRAINT person_book_pid_FK ;

 

 

ROWNUM(重点)
ROWNUM:表示行号,实际上是一个列,但是这个列是一个伪列,此列可以在每张表中出现。


eg:在查询雇员表上,加入ROWNUM

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp ;

ROWNUM可以自动编号,

如果只想显示前五行,
条件中ROWNUM<=5

SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp
WHERE ROWNUM<=5 ;

查询中间记录:
如果现在要想进行中间的截取操作,则只能采用子查询,
例如现在假设每页显示5条,第2页应该显示6~10条,那么对于数据库操作来讲,
它再查询的时候应该首先查询出1~10条,之后再在查询的结果中取出后5条。


SELECT ROWNUM ,empno,ename,job,sal,hiredate
FROM emp WHERE ROWNUM<=5;

 

SELECT *
FROM(SELECT ROWNUM rn,empno,ename,job,sal,hiredate
FROM emp WHERE ROWNUM<=10) temp
WHERE temp.rn>5;//6-10的记录


SELECT *
FROM(SELECT ROWNUM rn,empno,ename,job,sal,hiredate
FROM emp WHERE ROWNUM<=15) temp
WHERE temp.rn>10;//11-14的记录

 

 

 

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26231480/viewspace-1116486/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-09-01