1 / 78

Declarative Constraints for Complex Business Rules and Improved Performance

Carl Dudley Tradba Ltd UKOUG Official carl.dudley@wlv.ac.uk. Declarative Constraints for Complex Business Rules and Improved Performance. Constraints. Working with Oracle since 1986 Oracle DBA - OCP Oracle7, 8, 9, 10 Oracle DBA of the Year – 2002 Oracle ACE Director

uta-winters
Download Presentation

Declarative Constraints for Complex Business Rules and Improved Performance

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. Carl Dudley Tradba Ltd UKOUG Official carl.dudley@wlv.ac.uk Declarative Constraints for Complex Business Rules and Improved Performance

  2. Constraints • Working with Oracle since 1986 • Oracle DBA - OCP Oracle7, 8, 9, 10 • Oracle DBA of the Year – 2002 • Oracle ACE Director • Regular Presenter at Oracle Conferences • Consultant and Trainer • Technical Editor for a number of Oracle texts • UK Oracle User Group Director • Member of IOUC • Day job – Tradba Ltd Constraints – Origins and Syntax NULLs and Foreign Key Constraints Deferring and Enforcing Constraints Data Dictionary Support Complex Constraints and Query Transformations

  3. Constraints – a Brief History • 1970 – Ted Codd • A Relational Model for Large Shared Databanks • 1984 DB2 • Rudimentary support • SQL 1986 • Initial requirements • 1987 OracleV6 • Documentation support • SQL 1989 • Referential Integrity • 1992 Oracle7 • Full support

  4. Declarative Constraints • Preserve data integrity through the use of constraints • Cover rows already present in the table plus any rows which are subsequently created • Implement simple business rules such as ‘salaries should not exceed $5000’ • More complex business rules need to be handled by application logic within transactions or by the use of database triggers • Not independent objects • Created and manipulated only via CREATE TABLE and ALTER TABLE

  5. Primary Key Constraints • Constraints can be used to enforce • Entity Integrity (no duplicate rows?) • Referential Integrity • EVERY table should have a primary key (to enforce entity integrity) • The primary key column(s) will be • UNIQUE and NOT NULL • Automatically indexed (use can be made of an existing index) CREATE TABLE emp (empno NUMBER(4) CONSTRAINT emp_pk PRIMARY KEY, :

  6. Primary Key Constraints • Unique and not null data values • Should not contain ‘meaningful’ data and should not be updated • Usually numeric columns and as short as possible • Can be composite - but usually single columns • Composite keys can be big and require complex join criteria • Cannot be assigned simple sequence numbers • Often named using primary key column or table name(s) with a ‘_pk’ suffix • Use ALTER TABLE to place a primary key on an existing table ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);

  7. Unique and NOT NULL Constraints • Unique Constraints are like primary keys but allow NULL values • Unlimited as all are considered unique! • Any number allowed on a table • Often named with unique key column name(s) plus a ‘_uk’ suffix • NOT NULL constraints do not allow NULLs in a column • Not named and usually defined when a table is created ALTER TABLE emp ADD CONSTRAINT ename_deptno_uk UNIQUE (ename,deptno); CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(20), sal NUMBER(7,2) NOT NULL, comm NUMBER(7,2), deptno NUMBER(2) NOT NULL);

  8. CHECK Constraints • Simple business rules can be enforced • Based on constants and column values of only the row being updated • References to data in other tables via subqueries is not possible • sysdate and user are not allowed due to implicit subquery • A common naming convention is to use a ‘_ck’ suffix • Optimised beneath the SQL layer • Not violated if evaluate to unknown • A NULL sal will allow a negative comm to pass the check • In this case, it may be advisable to declare both columns as NOT NULL ALTER TABLE emp ADD CONSTRAINT sal_ck CHECK (sal > 0 OR comm >= 0);

  9. CHECK Constraint Examples • Ensure gender values are always uppercase ‘m’ or ‘f’ CREATE TABLE emp ( gender VARCHAR2(1) CONSTRAINT gender_ck CHECK (gender IN UPPERCASE(‘m’,’f’)); • Ensure that commission is never more than ¼ of an employee’s salary ALTER TABLE emp ( ADD CONSTRAINT comm_ck CHECK (comm < sal*0.25); • Ensure that hiredate is not later than the current date ALTER TABLE emp ( ADD CONSTRAINT hiredate_ck CHECK (hiredate <= sysdate); • Error because sysdate implies a subquery

  10. Referential Integrity • Maintains integrity of master-detail relationships • Operations on the primary key of the parent table are constrained if dependent rows exist in the child table • Operations on the foreign key in the child table are prevented if they result in values which do not exist in the parent (referenced) table • In Oracle, foreign keys may be set to NULL regardless of any referential constraint deptno dname ------ ---------- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS empno ename mgr deptno ----- -------- ---- ------ 7369 SMITH 7566 20 7499 ALLEN 7698 10 7521 WARD 7698 30 7566 JONES 7521 10 7654 MARTIN 7521 10 7698 BLAKE 30 Foreign keys Dept Emp

  11. Foreign Key (Referential) Constraints • May be single or composite columns • Must match data type of the referenced column(s) • No limit to number of foreign keys • Referenced columns must already be PRIMARY KEY or UNIQUE columns • Referenced columns may be in the same table • Foreign key columns may be NULL or partly NULL (regardless of any Foreign Key constraint) • Indexes are not automatically created on the foreign key columns

  12. In-line and Out-of-line Constraints • An in-line constraint is specified on the same line as the column definition • Also known as a column-level constraint • Datatype definition is not actually required for foreign key CREATE TABLE ... : deptno NUMBER(4) CONSTRAINT emp_dept_fk REFERENCES dept(deptno); • An out-of-line constraint is specified in a separate clause • Also known as a table-level constraint • Suppose dept has a composite key of divno and deptno CREATE TABLE ... : divno NUMBER(3), deptno NUMBER(4), : CONSTRAINT emp_dept_fk FOREIGN KEY (divno,deptno) REFERENCES dept(divno,deptno);

  13. Four options are generally recognised for actions performed by foreign key constraints RESTRICT SET NULL SET DEFAULT CASCADE General Foreign Key Constraint Actions • Oracle supports : Restriction of UPDATEs to referenced columns Restriction of DELETEs to referenced columns DELETE CASCADE (deletion of dependent rows) DELETE SET NULL • No support for UPDATE CASCADE • Must be performed via triggers or application logic • The SQL standard proposes a ‘PENDANT’ facility • When the last remaining employee in a department is deleted, the department record must also be deleted

  14. The DELETE CASCADE Action • Syntax for DELETE CASCADE : • Oracle will report only on rows which are deleted from dept when this referential action occurs ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE; DELETE FROM dept WHERE deptno IN (10,20); 2 rows deleted

  15. Conterminous Paths TableA • Each table has one row with the value ‘x’ • What would be the effect of? : Delete Cascade Delete Cascade TableB TableC Delete Restrict Delete Cascade TableD DELETE FROM a WHERE col = ‘x’;

  16. Enabling/Disabling Constraints ALTER TABLE emp DISABLE CONSTRAINT emp_pk; • Relaxes the constraint • Often done to increase speed of DML (e.g. bulk data loads) • Drops any associated UNIQUE index by default • Enforces the constraint • Checks rows for violations • Any violations prevent the constraint being enabled • Locks out activity on the table • Builds any associated index (may take some time) • Constraints are enabled by default on creation ALTER TABLE emp ENABLE CONSTRAINT emp_pk;

  17. Foreign Key Indexes and Locking • Most foreign keys should be indexed • If the unique or primary key is updated or deleted • Indexes are even more important for ON DELETE CASCADE • If there are many joins between parent and child • When a foreign key is unindexed : • DML on the parent primary key results in a table level lock on the child preventing DML on it • The child table lock is obtained and released immediately for each update of row in parent • Lock exists for short period, but can cause significant contention on child • When a foreign key is indexed : • DML on parent primary key results in a row share table lock on child table • Prevents other transactions gaining table locks on the child table, but does not block DML on either the parent or the child table • Only rows relating to the parent primary key are locked in the child table

  18. Indexes on Foreign Keys • Foreign keys are not indexed by default • Very significant locking implications • MONITORING USAGE does not detect use of indexes for concurrency • 11gR2 invisible indexes on FKs are also 'invisible' for concurrency • Support document 1019527.6 has a script that generates advice/report • Not accurate if foreign key columns present in an index in different order • Interprets this as unusable index Changing data in table DEPT will lock table EMP Create an index on table EMP with the following columns to remove lock problem Column = DEPTNO (1) Changing data in table ITEM_CATEGORIES will lock table ITEMS Create an index on table ITEMS with the following columns to remove lock problem  Column = ITEM_CAT (1)  Column = ITEM_BUS_UNIT (2)    Changing data in table EMP will lock table EMP  Create an index on table EMP with the following columns to remove lock problem    Column = MGR (1)   

  19. X Y X Y X Y X Y Constraints and Entity Models • No constraints (other than referential) on the foreign key • Additional NOT NULL constraint on the foreign key • Additional UNIQUE constraint on the foreign key • Additional UNIQUE and NOT NULL constraints on the foreign key

  20. Constraints Constraints – Origins and Syntax NULLs and Foreign Key Constraints Deferring and Enforcing Constraints Data Dictionary Support Complex Constraints and Query Transformations

  21. Newemp Divno Deptno Ename ----- ------ ------ 2 1 Smith 1 2 Adams 2 1 Carter 2 NULL Best NULL NULL Cox NULL 9 Scott 1 NULL King 2 5 Ford Newdept Divno Deptno Desc ----- ------ ---------- 1 1 Finance 1 2 Sales 2 1 Operations 2 2 Design 2 3 Chemicals FK = divno,deptno PK = divno,deptno Foreign Keys and Nulls • Single column foreign key values must match primary key or be NULL • Composite foreign key values must match primary key or be wholly or partly NULL • Partly NULL keys are not checked for the integrity of the NOT NULL part • Scott passes the integrity check, but Scott’s NULL can not be updated • The row for Ford will be checked out

  22. Matching Nulls • A composite foreign key may be (i) all NULL (ii) all non-NULL (iii) partially NULL • There are three possible matching rules for such keys 1. Match Full All columns must be NULLorall columns must have matching values in the primary key 2. Match Partial All columns must be NULLor Some of the columns may be NULL and the remainder must match values in their respective primary key columns • Match none All columns must be NULLorone or more columns are NULL and the remainder maytake any value • Oracle by default uses the Match None rule

  23. Matching Partial NULLs • Partial NULLs are allowed in foreign keys (ANSI standard) • To prevent partial NULLs – use a CHECK constraint • This will force the ‘Match Full’ rule for NULLs • The ‘Match Partial’ rule can not be properly implemented using declarative integrity constraints – database triggers must be used CONSTRAINT divno_deptno_ck CHECK ( ((divno IS NOT NULL) AND (deptno IS NOT NULL)) OR (((divno IS NULL) AND (deptno IS NULL)))

  24. Constraints Constraints – Origins and Syntax NULLs and Foreign Key Constraints Deferring and Enforcing Constraints Data Dictionary Support Complex Constraints and Query Transformations

  25. Handling Exceptions • To deal with rows which are violating (and preventing) a constraint • Construct an exceptions table using the UTLEXCPT script • Issue a statement to create a constraint – for example : This will place the ROWIDs of any offending rows in the exceptions table so that the rows can be identified and dealt with 3. Optionally remove all rows causing violations (could be dangerous) ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY (empno) EXCEPTIONS INTO exceptions; ROW_ID OWNER TABLE_NAME CONSTRAINT ------------------ ---------- ----------- ---------- AAABFJAACAAAFA3AAN SCOTT EMP EMP_PK AAABFJAACAAAFA4AAN SCOTT EMP EMP_PK DELETE FROM emp WHERE ROWID IN (SELECT row_id FROM exceptions WHERE constraint = <constraint_name>);

  26. Deferred Constraints • Constraint checking can be deferred until end of transaction at commit time • If the constraint is violated, the entire transaction is rolled back • INITIALLY DEFERRED • On creation, constraint is not checked until commit • INITIALLY IMMEDIATE • On creation, constraint is checked after each DML statement (default) • DEFERRABLE, NOT DEFERRABLE • Governs whether behaviour of constraint can be subsequently changed to DEFERRED or IMMEDIATE ALTER TABLE table_name ADD CONSTRAINT ... : [INITIALLY DEFERRED | INITIALLY IMMEDIATE] [DEFERRABLE | NOT DEFERRABLE];

  27. Deferred Constraint Example • Scenario (hypothetical example for illustration only): • The dept table has a primary key on deptno • The emp table has a deferrable foreign key on deptno referencing dept, initially set to ‘IMMEDIATE’ ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno) ALTER TABLE emp ADD CONSTRAINT emp_dept_fk FOREIGN KEY (deptno) REFERENCES dept(deptno) INITIALLY IMMEDIATE DEFERRABLE;

  28. Deferred Constraint Example (continued) • It is required to update a department number from 10 to 99 • Changes will need to be made by separate update statements as follows 1. UPDATE dept SET deptno = 99 WHERE deptno = 10; 2. UPDATE emp SET deptno = 99 WHERE deptno = 10; • The first update fails with the following error message • The constraint is checked ‘immediately’ and therefore too early • Reversing the updates does not help • Maybe we can change the constraint mode ORA-02292: integrity constraint (SCOTT.EMP_DEPT_FK) violated - child record found

  29. Changing the Constraint Mode • Two methods available to toggle constraint modes • SET CONSTRAINT • Used to change the mode for a single transaction • ALTER SESSION • Changes mode for all deferrable constraints for an entire session • Reset to default (initial) validation using the keyword DEFAULT SET CONSTRAINT constraint_name,...,constraint_name IMMEDIATE | DEFERRED; SET CONSTRAINTS ALL IMMEDIATE | DEFERRED; ALTER SESSION SET CONSTRAINTS = IMMEDIATE | DEFERRED; ALTER SESSION SET CONSTRAINTS = DEFAULT;

  30. Processing the Update Transaction • Set the constraint to be deferred until the end of the transaction • Constraint checked when commit occurs • Execute both updates • These both succeed in changing deptno values in emp and deptfrom 10 to 99 • Issue the commit • The entire transaction will succeed as all the data is now consistent at time of commit SET CONSTRAINT emp_dept_pk DEFERRED;

  31. Index Support for Deferred Constraints • Index must be non-unique for deferred constraints • Index uniqueness could be violated DURING the transaction • Dropping a deferrable constraint does not drop the index (by default) • Creating a deferred constraint will use an existing non-unique index on the intended primary key column • Uniqueness will now be enforced • Index name will not be changed to constraint name • Any constraint built on an MV should be deferrable • The refresh process requires this

  32. Enforced Constraints • Existing data is not checked • Checks made only on changes after enabling the constraint • Used when constraints do not apply to historical data or when it is known that existing data already complies with the constraint • Example : No new employees can have a salary > $3000 • Constraint is created even though existing rows violate it • Attempt to insert a new row which violates the constraint • Rejected with the following error message ALTER TABLE emp ADD CONSTRAINT sal_ck CHECK (sal <=3000) ENABLE NOVALIDATE; INSERT INTO emp (empno,ename,sal,deptno) VALUES (8888,’COX’,5500,10); ORA-02290: check constraint (SCOTT.EMP_CK) violated

  33. Validating Enforced Constraints • After eliminating all violations, the constraint can be validated so that it acts upon all rows in the table • If King’s salary of $5000 is still present the following error message is generated ALTER TABLE emp ENABLE VALIDATE CONSTRAINT sal_ck; ORA-02293: cannot enable (SCOTT.EMP_CK) - check constraint violated

  34. Setting up Primary Keys with NOVALIDATE • Suppose you have a table of historical data that could already have duplicate rows which are of no immediate consequence • You want to restrict any new data to be unique • Create a non-unique index on the 'primary key' column(s) • Create a primary key constraint in NOVALIDATE state • The normal instigation of a primary key builds a unique index even in NOVALIDATE state • Any duplicate rows already present will foul the creation of the index • If no duplicates, there will still be a delay before the constraint is enforced due to creation of the unique index

  35. Non_unique Indexes for Primary Keys • Build a table with duplicate data already present CREATE TABLE empn AS SELECT * FROM emp; INSERT INTO empn SELECT * FROM empn WHERE empno = 7369; • Try to enforce a (non-DEFERRABLE) primary key with a unique index ALTER TABLE empn ADD CONSTRAINT empn_pk PRIMARY KEY(empno) NOVALIDATE; ORA-02437: cannot validate (SCOTT.EMPNOVAL_PK) - primary key violated • Enforce primary key with non-unique index ALTER TABLE empn ADD CONSTRAINT empn_pk PRIMARY KEY(EMPNO) USING INDEX (CREATE INDEX empn_pk ON empn(empno)) NOVALIDATE; Table altered. --succeeds because non-unique index can be built INSERT INTO empn SELECT * FROM empn; ORA-00001: unique constraint (SCOTT.EMPN_PK) violated

  36. RELY • Tells Oracle that it should rely on the data complying with the constraint • Basically you are asking the optimizer to trust you to guarantee the data • RELY allows the optimizer to 'use' a NOVALIDATE constraint • Main relevance is for materialized views ALTER TABLE emp ADD CONSTRAINT sal_ck CHECK (comm = sal) ENABLE NOVALIDATE RELY;

  37. Constraints and Indexes • Constraints are logical entities • Indexes are physical structures • Primary key and unique constraints do not theoretically require indexes • An index is used (and built if needed) to enhance performance • Full table scan could be used to check for duplicate values • A non-unique index can support a constraint • Must be used for deferred constraints • May be used if it already exists and has the chosen primary key column(s) as the leading edge • Remains live if the constraint is dropped, unless DROP INDEX is used • Can be selected from the set of suitable indexes with USING INDEX • Or built on creation of the constraint with CREATE INDEX

  38. 6^ 7^ 8^ 10 11 12 13 9 Support for Legal SQL Statements UPDATE emp SET empno = empno + 5; before after • Yet another reason why you should not attempt to substitute constraints with your own code • Non-unique index support tends to generate more redo

  39. KEEP allows Nulls ALTER TABLE emp DROP PRIMARY KEY [KEEP | DROP INDEX]; Default : KEEP for non-unique indexes DROP for unique indexes Non-Unique Indexes - Examples of Use ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY(empno) USING INDEX(CREATE UNIQUE INDEX twocol ON emp(empno,ename)); ORA-14196: Specified index cannot be used to enforce the constraint. Presence of additional columns works only for Non-unique indexes ALTER TABLE emp ADD CONSTRAINT pk_emp PRIMARY KEY(empno) USING INDEX(CREATE INDEX empno_ename ON emp(empno,ename)); • Existing indexes can be used • Could help minimize number of required indexes • Can overload unique index with extra columns to avoid table access

  40. Efficient Use of Integrity Constraints: A Procedure • Using states of integrity constraints in the following order can ensure the best benefits: • Place constraint in disable state • Perform the DML operation (load, export, import). • Enable the constraint in novalidate state • Fully enable the constraint (validate) • Some benefits of using constraints in this order are: • No locks are held • All constraints can go to enable state concurrently • Constraint enabling is done in parallel • Concurrent activity on table is permitted

  41. Constraints Constraints – Origins and Syntax NULLs and Foreign Key Constraints Deferring and Enforcing Constraints Data Dictionary Support Complex Constraints and Query Transformations

  42. Constraints in the Data Dictionary • Details of constraints can be found in user_constraints The constraint_type column can have the following values C : Check constraint (tables only) P : Primary key constraint R : Foreign key constraint U : Unique key constraint V : WITH CHECK OPTION constraint on a view O : Read only view (not table) F : Constraint involving a REF column S : Supplemental Logging H : Hash expression TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME STATUS -------------- ---------------- --------------- ----------------- -------- DEPT DEPT_PK P ENABLED EMP EMP_PK P ENABLED EMP EMP_JOB_CK C DISABLED EMP SYS_C001415 C ENABLED EMP EMP_DEPT_FK R DEPT_PK ENABLED EMP SYS_C011791 ?

  43. Constraints in the Data Dictionary (continued) • Columns suffering constraints are found in user_cons_columns SELECT constraint_name ,table_name ,column_name ,position FROM user_cons_columns; CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION ----------------- ------------ ------------ -------- EMP_JOB$DEPTNO_UK EMP JOB 1 EMP_JOB$DEPTNO_UK EMP DEPTNO 2 DEPT_PK DEPT DEPTNO 1 EMP_PK EMP EMPNO 1

  44. Example of INVALID constraint Constraints in the Data Dictionary (continued) CREATE VIEW v2 AS SELECT * FROM emp; ALTER VIEW v2 ADD PRIMARY key(empno) DISABLE NOVALIDATE; ALTER TABLE emp DROP PRIMARY KEY; SELECT constraint_name ,constraint_type ,table_name ,status ,validated ,rely ,invalid ,view_related FROM user_constraints WHERE invalid IS NOT NULL; CONSTRAINT_NAME C TABLE_NAME STATUS VALIDATED RELY INVALID VIEW_RELATED --------------- - ---------- -------- ------------- ---- ------- -------------- SYS_C0011765 P V2 DISABLED NOT VALIDATED INVALIDDEPEND ON VIEW

  45. Constraints in the data Dictionary (continued) • Supplemental logging shows up in user_constraints • Log groups are not shown • The constraint_type is shown as ‘?’ (NOT ‘S’) ALTER TABLE emp ADD PRIMARY key(empno); ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA(PRIMARY KEY) COLUMNS; ALTER TABLE emp ADD SUPPLEMENTAL LOG DATA(ALL) COLUMNS; ALTER TABLE emp ADD SUPPLEMENTAL LOG GROUP sal_comm(sal,comm); SELECT constraint_name, constraint_type, table_name FROM user_constraints WHERE table_name = 'EMP'; CONSTRAINT_NAME C TABLE_NAME ------------------------------ - ------------------------------ SYS_C0011794 P EMP SYS_C0011795 ? EMP SYS_C0011796 ? EMP

  46. Types of Constraints in the Dictionary • Definition of dba_constraints shows some of the types as ‘?’ or not at all • type# can have a value of 1-17 in cdef$ decode(c.type#, 1, 'C', 2, 'P', 3, 'U', 4, 'R', 5, 'V', 6, 'O', 7, 'C', '?'), : : and c.type# != 8 /* don't include hash expressions */ and c.type# != 12 /* don't include log groups */

  47. dbms_metadata Support SELECT dbms_metadata.get_dependent_ddl ( 'REF_CONSTRAINT', 'EMP' ) fks_on_emp FROM dual; FKS_ON_EMP ------------------------------------------------------------ ALTER TABLE "SCOTT"."EMP" ADD FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT"."DEPT”(“DEPTNO”) ENABLE ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT “MGR_FK” FOREIGN KEY (“MGR")REFERENCES "SCOTT".“EMP”(“EMPNO”) ENABLE • Script showing unindexed foreign key columns SELECT * FROM ( SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' MINUS SELECT i.table_name, ic.column_name, ic.column_position FROM user_indexes i, user_ind_columns ic WHERE i.index_name = ic.index_name ) ORDER BY table_name, column_position;

  48. Constraints Constraints – Origins and Syntax NULLs and Foreign Key Constraints Deferring and Enforcing Constraints Data Dictionary Support Complex Constraints and Query Transformations

  49. EMPDEP EMP ENAME OCCUPATION -------- ---------- WOODS CLERK JOHNSON MANAGER COX CLERK PITT CLERK SPINK SALESMAN DRAPER MANAGER EMP_V1 EMPNO JOB ----- -------- 7366 SALESMAN 7500 MANAGER 7902 MANAGER 7566 CLERK 7934 SALESMAN JOB -------- SALESMAN MANAGER CLERK primary key foreign key Foreign Keys Referencing Non-unique Columns • Problem : • Need to enforce a foreign key constraint on the occupation column in the empdep table based on data in the emp table • Requires a reference to a non-unique column (job) in the emp table • Reference a materialized view carrying only unique values of job

  50. Enforcing Foreign Keys Without Primary keys CREATE MATERIALIZED VIEW LOG ON emp WITH ROWID,PRIMARY KEY,SEQUENCE(job) INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW emp_v1 REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS SELECT job FROM emp GROUP BY job; ALTER MATERIALIZED VIEW emp_v1 ADD PRIMARY KEY (job); ALTER TABLE empdep ADD CONSTRAINT empdep_emp_v1 FOREIGN KEY (occupation) REFERENCES emp_v1; UPDATE empdep SET occupation = 'X'; ORA-02291: integrity constraint (SCOTT.EMPDEP_EMP_V1) violated - parent key not found job is not unique in emp job is unique in MV empdep must have only jobs in the emp table

More Related