This
practice covers the creation of referential
integrity constraints.
INSTRUCTIONS
1.
Create the products table and add the constraints to the customers and
orders table by running the SQL statements shown below.
|
SQL>
CREATE TABLE products (
prod_code NUMBER(6),
2
description VARCHAR2(30),
3
price
NUMBER(8,2) )
4
TABLESPACE users; Table
created. SQL>
ALTER TABLE customers 2
ADD ( CONSTRAINT customers_cust_code_pk
3
PRIMARY KEY(cust_code) DEFERRABLE INITIALLY IMMEDIATE
4
USING INDEX TABLESPACE indx,
5
CONSTRAINT custmoers_region_ck
6
CHECK (region IN ('East', 'West', 'North', 'South'))); Table
altered. SQL>
ALTER TABLE orders
2
ADD ( CONSTRAINT orders_ord_id_pk PRIMARY KEY(ord_id)
3
USING INDEX TABLESPACE indx,
4
CONSTRAINT orders_cust_code_fk FOREIGN KEY (cust_code)
5
REFERENCES customers (cust_code)
6
DEFERRABLE INITIALLY IMMEDIATE,
7
CONSTRAINT orders_date_of_dely_ck
8
CHECK
(date_of_dely >= ord_date)); Table
altered. SQL>
ALTER TABLE products
2
ADD CONSTRAINT products_prod_code_uk UNIQUE (prod_code)
3
DEFERRABLE DISABLE; Table
altered. |
2.
Run the following insert statements to add two rows in the products
table.
|
SQL>
connect system/manager Connected. SQL>
INSERT INTO system.products
2 VALUES(4000,'UNIX
Monitor',3620); 1
row created. SQL>
INSERT INTO system.products
2 VALUES(4000,'NT
Monitor', 2400); 1
row created. SQL>
COMMIT; |
3.
Enable the unique constraint on the PRODUCT table. Was it successful?
|
SQL>
ALTER TABLE system.products
2 ENABLE
CONSTRAINT products_prod_code_uk; ALTER
TABLE system.products * ERROR
at line 1: ORA-02299:
cannot validate (SYSTEM.PRODUCTS_PROD_CODE_UK) - duplicate keys found |
4.
Query the data dictionary to check the names and types of indexes
created to validate the constraints.
Hint:
The indexes are only created for primary key and unique constraints that are
enabled and the indexes will be created with the same name as the constraints.
|
SQL>
SELECT
index_name,table_name,uniqueness 2
FROM
dba_indexes 3
WHERE
index_name in 4
( SELECT
constraint_name 5
FROM
dba_constraints 6
WHERE table_name IN ('PRODUCTS','ORDERS',
'CUSTOMERS') 7
AND
owner='SYSTEM' 8
AND
constraint_type in ('P','U')) ; INDEX_NAME
TABLE_NAME UNIQUENES ------------------------------
---------- --------- CUSTOMERS_CUST_CODE_PK
CUSTOMERS NONUNIQUE ORDERS_ORD_ID_PK
ORDERS UNIQUE |
This
practice covers maintaining referential
integrity constraints.
INSTRUCTIONS
1.
Query the data dictionary to check for constraints, whether they are
deferrable, and their status.
Hint:
Use the DBA_CONSTRAINTS view to get this information.
|
SQL>
COLUMN constraint_name
FORMAT a25 SQL>
COLUMN table_name
FORMAT a10 SQL>
COLUMN constraint_type
FORMAT a1 SQL>
COLUMN deferrable
FORMAT a15 SQL>
COLUMN status
FORMAT a10 SQL>
SELECT constraint_name,
table_name,
2 constraint_type,
deferrable, status
3 FROM
dba_constraints
4 WHERE
table_name IN
5 ('PRODUCTS','ORDERS','CUSTOMERS')
6 AND
owner='SYSTEM'; CONSTRAINT_NAME
TABLE_NAME C DEFERRABLE
STATUS ----------------------
---------- - -------------- -------- CUSTOMERS_REGION_CK
CUSTOMERS C NOT
DEFERRABLE ENABLED CUSTOMERS_CUST_CODE_PK
CUSTOMERS P DEFERRABLE
ENABLED ORDERS_CUST_CODE_FK
ORDERS
R DEFERRABLE
ENABLED ORDERS_DATE_OF_DELY_CK
ORDERS C
NOT DEFERRABLE ENABLED ORDERS_ORD_ID_PK
ORDERS
P NOT DEFERRABLE ENABLED PRODUCTS_PROD_CODE_UK
PRODUCTS U
DEFERRABLE DISABLED 6
rows selected. |
2.
Enable the constraint on the products table that was created in the
previous exercise. As you
recall, there are duplicate primary key values for prod_code 4000 so you
must enable the constraint using the NOVALIDATE
clause. Query the data
dictionary to verify the effect of the change
|
SQL>
ALTER TABLE system.products 2
ENABLE NOVALIDATE
CONSTRAINT products_prod_code_uk; Table
altered. SQL>
SELECT constraint_name,
table_name,
2
constraint_type,
validated, status
3
FROM
dba_constraints
4
WHERE
table_name = 'PRODUCTS'
5
AND
owner='SYSTEM'; CONSTRAINT_NAME
TABLE_NAME C
VALIDATED STATUS ---------------------
----------- - ------------- ------- PRODUCTS_PROD_CODE_UK
PRODUCTS U
NOT VALIDATED ENABLED |
3.
Test that the primary key constraint on the products table disables
inserts that violate the change by trying to insert a row with a prod_code
of 4000 as shown below.
|
SQL>
INSERT INTO system.products
2 VALUES(4000,'Monitor',3000); INSERT
INTO system.products * ERROR
at line 1: ORA-00001:
unique constraint (SYSTEM.PRODUCTS_PROD_CODE_UK) violated
|
4.
Take the necessary steps to identify existing constraint violations
in the PRODUCTS table, modify product codes as needed, and guarantee that
all existing as well as new data does not violate the constraint. Remember
that we used the NOVALIDATE clause when the constraint was enabled above
since there were two rows that had prod_code 4000. (Assume that the table
has several thousands of rows and it is too time-consuming to verify each
row manually.)
Hint:
Use the following steps:
A. Create the EXCEPTIONS table.
.
|
SQL>
CONNECT system/manager Connected. SQL>
@?/rdbms/admin/utlexcpt |
B.
Run the command to enable the constraint and trap the exceptions.
|
SQL>
ALTER TABLE system.products
2
ENABLE CONSTRAINT
products_prod_code_uk
3
EXCEPTIONS INTO
system.exceptions; ALTER
TABLE system.products * ERROR
at line 1: ORA-02299:
cannot validate (SYSTEM.PRODUCTS_PROD_CODE_UK) - duplicate keys
Found |
C. Use the ROWID in the EXCEPTIONS table to list the rows in the PRODUCTS table that violate the constraint. Do not list LOB columns.
|
SQL>
SELECT rowid, prod_code,
description
2 FROM
system.products
3 WHERE
rowid IN
4
( SELECT row_id
5
FROM
exceptions
6
WHERE
table_name='PRODUCTS' ) ; ROWID
PROD_CODE DESCRIPTION ------------------
---------- ------------------------------ AAAIG0AAIAAACKCAAA
4000 UNIX Monitor AAAIG0AAIAAACKCAAB
4000 NT Monitor |
D.
Rectify the errors.
|
SQL>
UPDATE system.products
2 SET
prod_code='4001'
3 WHERE
rowid = ( SELECT max(row_id)
4
FROM
exceptions
5
WHERE
table_name='PRODUCTS' ) ; 1
row updated. |
E.
Enable the constraint.
|
SQL>
ALTER TABLE system.products
2
ENABLE CONSTRAINT
products_prod_code_uk
3
EXCEPTIONS INTO
system.exceptions; Table
altered. |
5.
Query the data dictionary to check the names and types of indexes
created to validate the constraints. Note
this query was run in the previous exercise and the output listed two
indexes, one for the orders table and one for the customers table.
Why are two of the indexes created as nonunique?
|
SQL>
SELECT
index_name,table_name,uniqueness 2
FROM dba_indexes 3
WHERE
index_name in 4
( SELECT constraint_name 5
FROM dba_constraints 6
WHERE table_name IN 7
('PRODUCTS','ORDERS',
'CUSTOMERS') 8
AND owner='SYSTEM' 9
AND constraint_type
in ('P','U')) ; INDEX_NAME
TABLE_NAME
UNIQUENES -----------------------
------------------ --------- CUSTOMERS_CUST_CODE_PK
CUSTOMERS
NONUNIQUE ORDERS_ORD_ID_PK
ORDERS
UNIQUE PRODUCTS_PROD_CODE_UK
PRODUCTS
NONUNIQUE |
6.
Insert the following rows into the orders and customer tables and
observe the errors. Why did the
first insert fail but not the second? Roll
back the changes.
|
SQL>
INSERT INTO system.orders
2
VALUES (800,'01-JAN-98','J01',NULL); INSERT
INTO system.orders * ERROR
at line 1: ORA-02291:
integrity constraint (SYSTEM.ORDERS_CUST_CODE_FK) violated –
parent key not found SQL>
INSERT INTO system.customers
2
VALUES ('J01','Sports
Store', 'East'); 1
row created. SQL>
rollback; Rollback
complete. |
7.
In the step above, the insert for the customer table should have been
executed first so that the order table's foreign key referencing the
customer table would not have failed. Insert
the same two rows in the same order once again but first defer the
constraints that are deferrable so that the constraints will only be checked
at commit.
|
SQL>
ALTER SESSION SET CONSTRAINTS=deferred; Session
altered. SQL>
INSERT INTO system.orders
2 VALUES
(800,'01-JAN-98','J01',NULL); 1
row created. SQL>
INSERT INTO system.customers
2
VALUES ('J01','Sports Store', 'East'); 1
row created. SQL>
commit; Commit
complete. |