创建主键、外键和唯一约束
Oracle中创建主键、外键和唯一约束的代码
Oracle.首先创建学生信息表studentinfo和学生成绩表testinfo。
--学生信息表
CREATE TABLE studentInfo (
stuNo CHAR(10) NOT NULL ,
stuName VARCHAR2(20) NOT NULL,
stuSex NUMBER(1),
stuBirthday DATE DEFAULT SYSDATE ,
stuAddress VARCHAR2(20)
);
--学生成绩表
CREATE TABLE testInfo (
stuNo CHAR(10) NOT NULL ,
classNo CHAR(5) NOT NULL,
testScore NUMBER(3,1)
);
--约束条件：设置主键
alter table testinfo add constraint fk_1 foreign key (stuno) references studentinfo(stuno);
--约束条件：设置外键
alter table studentinfo add constraint pk_1 primary key (stuno);
--约束条件：设置唯一
alter table testinfo add constraint uniq_1 unique (stuno,classno);
--插入测试数据
INSERT INTO STUDENTINFO VALUES('001','ZHANGSAN',1,'03-1月-86','南京市玄武区');
INSERT INTO STUDENTINFO VALUES('002','LISI',1,'05-12月-86','南京市玄武区');
INSERT INTO STUDENTINFO VALUES('003','WANGWU',0,'03-10月-85','南京市白下区');
INSERT INTO STUDENTINFO VALUES('004','ZHANGSAN',1,'23-10月-88','南京市下关区');
INSERT INTO STUDENTINFO VALUES('005','SMITH',0,'31-10月-89','南京市玄武区');
INSERT INTO STUDENTINFO VALUES('006','ALLEN',1,'25-12月-86','南京市栖霞区');
INSERT INTO STUDENTINFO VALUES('007','SCOTT',1,'08-11月-86','南京市浦口区');
INSERT INTO STUDENTINFO VALUES('008','BRUCE LEE',0,'03-11月-80','南京市雨花区');
INSERT INTO STUDENTINFO VALUES('009','BRUCE LEE',1,'09-12月-87','南京市六合区');
INSERT INTO STUDENTINFO VALUES('010','ZHANGSAN',1,'13-11月-82','南京市玄武区');
INSERT INTO TESTINFO VALUES('001','02',90.5);
INSERT INTO TESTINFO VALUES('001','01',80);
INSERT INTO TESTINFO VALUES('001','03',60);
INSERT INTO TESTINFO VALUES('001','04',50);
INSERT INTO TESTINFO VALUES('001','05',70);
Oracle 有如下类型的约束:
NOT NULL
UNIQUE Key
PRIMARY KEY
FOREIGN KEY
CHECK
Oracle使用SYS_Cn格式命名约束.
创建约束:
在建表的同时创建
建表后创建
可定义列级或表级约束.
可通过数据字典表查看约束.
建表时创建约束
create table OTL_NICOTINE_GRADE ( ID NUMBER not null, SEASON_NO NUMBER(4) not null, RECEIPT_NO NUMBER(8) not null,
GRADE VARCHAR2(10) not null, PROPORTION NUMBER(5,2) not null, WEIGHT NUMBER(10,2) not null, VALUE NUMBER(12,2) not
null, constraint PK_OTL_NICOTINE_GRADE primary key (ID, GRADE), constraint FK_OTL_NICO_REFERENCE_OTL_CHEC foreign
key (ID) references OTL_CHECK_CHEM (ID) )
建表后添加约束
ALTER TABLE (table_name) ADD ( CONSTRAINT (foreign key constraint name) FOREIGN KEY (field name ) REFERENCES
primary_table_name ( primary_table_primary_index_field )
SQL> create table emplyees(
2 employee_id number(6),
3 last_name varchar2(25) not null,
4 salary number(8,2),
5 commission_pct number(2,2),
6 hire_date date,
7 constraint emp_hire_date_1 not null,
8 CONSTRAINT dept_dname_uk UNIQUE(emp_name)
9 );
表已创建。
SQL>
1 select constraint_name,table_name
2 from dba_constraints
3 where table_name='EMPLYEES'
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_C003012 EMPLYEES
EMP_HIRE_DATE_1 EMPLYEES
定义主键约束PRIMARY KEY
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
外键约束FOREIGN KEY
SQL> CREATE TABLE emp(
2 empno NUMBER(4),
3 ename VARCHAR2(10) NOT NULL,
4 job VARCHAR2(9),
5 mgr NUMBER(4),
6 hiredate DATE,
7 sal NUMBER(7,2),
8 comm NUMBER(7,2),
9 deptno NUMBER(7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno));
外键约束的关键字
FOREIGN KEY
定义子表的哪一列作为外键约束
REFERENCES
指示主表和参照的列
ON DELETE CASCADE
删除主表记录时将子表相关记录删除
ON DELETE SET NULL
将外键引用置为空值
CHECK 约束
定义每一记录都要满足的条件
条件表达式不允许有:
CURRVAL, NEXTVAL, LEVEL, ROWNUM
SYSDATE, UID, USER, USERENV 函数
参照其他记录的值
..., deptno NUMBER(2),
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99),...
加约束
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
可加或删除约束，但不能修改
可使约束生效和失效
使用MODIFY子句可加 NOT NULL约束
加 FOREIGN KEY 约束到EMP表
SQL> ALTER TABLE emp
2 ADD CONSTRAINT emp_mgr_fk
3 FOREIGN KEY(mgr) REFERENCES emp(empno);
删除约束
删除约束emp_mgr_fk
SQL> ALTER TABLE emp
2 DROP CONSTRAINT emp_mgr_fk;
删除主键约束和相关的外键约束
SQL> ALTER TABLE dept
2 DROP PRIMARY KEY CASCADE;
使约束失效
在ALTER TABLE 语句中执行DISABLE子句可使完整性约束失效
使用 CASCADE 选项可使依赖的完整约束失效
SQL> ALTER TABLE emp
2 DISABLE CONSTRAINT emp_empno_pk CASCADE;
使用ENABLE子句将失效的约束生效
SQL> ALTER TABLE emp
2 ENABLE CONSTRAINT emp_empno_pk;
当使UNIQUE 或 PRIMARY KEY约束生效时,会自动创建 UNIQUE 或PRIMARY KEY 索引.
延迟约束验证
ALTER TABLE AAA ADD (CONSTRAINT AAA_PK PRIMARY KEY(a) DEFERRABLE) ;
ALTER TABLE BBB
ADD (CONSTRAINT BBB_FK FOREIGN KEY(a)
REFERENCES AAA(a)
ON DELETE CASCADE DEFERRABLE)
CREATE OR REPLACE TRIGGER ID_TRG AFTER UPDATE ON AAA FOR EACH ROW
BEGIN
UPDATE BBB SET a=:NEW.a WHERE a=:OLD.a;
END;
查看约束
通过查看 USER_CONSTRAINTS 表可得到用户的所有约束.
SQL> SELECT constraint_name, constraint_type,
2 sarch_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';
CONST
RAINT_NAME C SEARCH_CONDITION
SYS_C00674 C EMPNO IS NOT NULL
SYS_C00675 C DEPTNO IS NOT NULL
EMP_EMPNO_PK P
...
查看约束建立在哪些列
通过查询USER_CONS_COLUMNS 视图可获得约束建立在哪些列上
SQL> SELECT constraint_name, column_name
2 FROM user_cons_columns
3 WHERE table_name = 'EMP';
CONSTRAINT_NAME COLUMN_NAME
------------------------- ----------------------
EMP_DEPTNO_FK DEPTNO
EMP_EMPNO_PK EMPNO
EMP_MGR_FK MGR
SYS_C00674 EMPNO
SYS_C00675 DEPTNO
------------------------- ----------------------
constraint_type
约束的类型有如下几种：
C (check constraint on a table)
P (primary key)
U (unique key)
R (Referential AKA Foreign Key)
V (with check option, on a view)
O (with read only, on a view)
【编辑推荐】
如何调整Oracle性能
Oracle学习入门心得
Oracle数据库中使用外部表的心得
Oracle数据库防火墙为数据库部署第一道防线