大型数据库完整性典型实现方式

1. 大型数据库完整性典型实现方式 • 在大型数据库中的完整性控制 • 触发器 数据库原理与技术（实例）-张祖平

2. 在大型数据库中的完整性控制 • RDBMS中的完整性 • 实体完整性 • 参照完整性 • 用户自定义完整性 • 约束Constraint • NULL/NOT NULL • Unique • Primary Key • Foreign Key References • check 数据库原理与技术（实例）-张祖平

3. 例1 • create table customers (cid char(4) not null, cname varchar(13), city varchar(20), discnt real check(discnt <= 15.0), primary key(cid)); • Cid not null, primary key, unique • Discnt <=15.0 数据库原理与技术（实例）-张祖平

4. 例题 create table orders ( ordno integer not null, month char(3), cid char(4) not null, aid char(3) not null, pid char(3) not null, qty integer not null check(qty >= 0), dollars float default 0.0check(dollars >= 0.0), primary key ( ordno ), foreign key (cid) references customers, foreign key (aid) references agents, foreign key (pid) references products); 数据库原理与技术（实例）-张祖平

5. 关于 FK…REFs • Now with this FOREIGN KEY . . . REFERENCES clause for cid in orders table, if try to insert an orders row with cid value that isn't in customers, insert will fail and give an error condition. • Can also have larger tuples matching: Create table employees ( ..…. foreign key (cityst, staddr, zip) references ziptst(cityst, staddr, zip); 数据库原理与技术（实例）-张祖平

6. 完整的表定义格式 • CREATE TABLE tablename ((colname datatype [DEFAULT {default_constant | NULL}] [col_constr {col_constr. . .}] | table_constr {,{colname datatype [DEFAULT {default_constant | NULL}] [col_constr {col_constr. . .}] | table_constr} . . .}); • col_constr • table_constr 数据库原理与技术（实例）-张祖平

7. 列Constraints. The col_constr form that constrains a single column value follows: {NOT NULL | [CONSTRAINT constraint-name] UNIQUE | PRIMARY KEY | CHECK (search_cond) | REFERENCES table-name [(col-name) ] [ON DELETE CASCADE]} 数据库原理与技术（实例）-张祖平

8. 例3 • Create table dept(DNO CHAR(3) PRIMARY KEY, DN VARCHAR2(32) , DEAN CHAR(8), TEL CHAR(8), /* , have or no */ CHECK(SUBSTR(tel,1,3)='887‘) ); • Col-cons and tab_cons • CREATE TABLE STUD(sno CHAR(8) constraint stud_pk PRIMARY KEY,/* DEMO */ sn CHAR(8),bd DATE, DNO CHAR(3) constraint stud_fk REFERENCES DEPT(DNO) ON DELETE CASCADE ); 数据库原理与技术（实例）-张祖平

9. 例4 ORACLE 标准修改表的语法 • ALTER TABLE tablename [ADD ({colname datatype [DEFAULT {default_const|NULL}] [col_constr {col_constr...}] | table_constr} {, ...})] [DROP {COLUMN col-name | (col-name {, col-name…})}] [MODIFY (col-name data-type [DEFAULT {default_const|NULL}] [[NOT] NULL]{, . . .})] [DROP CONSTRAINT constr_name] [DROP PRIMARY KEY] [disk storage and other clauses (not covered, or deferred)] [any clause above can be repeated, in any order] [ENABLE and DISABLE clauses for constraints]; 数据库原理与技术（实例）-张祖平

10. 例5 • Alter table orders add constraint order_check_dollars check(dollars>=10); • Alter table orders modify primary key disable; • Alter table stud drop column bd; • Alter table orders modify order_check_dollars disable; 数据库原理与技术（实例）-张祖平

11. 2 触发器TRIGGER • CREATE TRIGGER trigger_name BEFORE | AFTER {INSERT | DELETE | UPDATE [OF colname {, colname...}]} ON tablename [REFERENCING corr_name_def {, corr_name_def...}] [FOR EACH ROW | FOR EACH STATEMENT] [WHEN (search_condition)] {statement --action (single statement) | BEGIN ATOMIC statement; { statement;...} END} 数据库原理与技术（实例）-张祖平

12. 例6 • create trigger discnt_max after insert on customers referencing new x for each row when (x.discnt > 15.0) begin raise_application_error(-20003, 'invalid discount on insert'); end; / 数据库原理与技术（实例）-张祖平

13. 例7 • create trigger foreigncid after delete on customers referencing old ocust for each row -- no WHEN clause -- PL/SQL form starts here begin update orders set cid = null where cid = :ocust.cid; end; 数据库原理与技术（实例）-张祖平

14. 例8 (问题1) • Emp(EMPNO,ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO ) • 1 SAL+COMM<=5000 • 2 SAL+COMM<=5000 WHEN JOB<>’PRESIDENT’ • 3 SAL GRADE (TABLE salgrade ) • GRADE LOSAL HISAL • ---------- ---------- ---------- • 1 700 1200 • 2 1201 1400 • 3 1401 2000 • 4 2001 3000 • 5 3001 9999 数据库原理与技术（实例）-张祖平

15. 例8 (问题1) • 1 Alter table emp add constraint emp_check_sal check(sal+comm<=5000); • 2 Alter table emp add constraint emp_check_sal check(sal+comm<=5000) when job<>’PRESIDENT’; • 有语法错误！！ 数据库原理与技术（实例）-张祖平

16. 例8 (解决3) • Create trigger trg_emp before insert or update on emp for each row when (new.job<>’PRESIDENT’) BEGIN if nvl(:new.sal,0)+nvl(:new.comm,0)>=5000 then raise_application_error(-20003, 'invalid sal or comm'); End if; End; 数据库原理与技术（实例）-张祖平

17. 例8 (解决3) • Create or replace trigger trg_emp before insert or update on emp for each row declare hi number(8,2); lo number(8,2); BEGIN Select losal,hisal into lo,hi from salgrade where job=:new.job; if :new.sal>hi or :new.sal<lo then raise_application_error(-20003, 'invalid sal'); End if; End; 数据库原理与技术（实例）-张祖平