1 / 17

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

大型数据库完整性典型实现方式. 在大型数据库中的完整性控制 触发器. 在大型数据库中的完整性控制. RDBMS 中的完整性 实体完整性 参照完整性 用户自定义完整性 约束 Constraint NULL/NOT NULL Unique Primary Key Foreign Key References check. 例 1. create table customers (cid char(4) not null, cname varchar(13), city varchar(20),

bisa
Télécharger la présentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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; 数据库原理与技术（实例）-张祖平

More Related