38 Managing Constraints

• ·         To deactivate an integrity constraint-DISABLE CONSTRAINT
• ·         Disables dependent integrity constraints- CASCADE clause
• ·         To add, modify, or drop columns from a table- ALTER TABLE
• ·         To activate an integrity constraint currently disabled- ENABLE CONSTRAINT
• ·         Removes a constraint from a table- DROP CONSTRAINT
• ·         Allows user to delete a column from a table-DROP COLUMN
• ·         Defines the actions the database server takes when a user attempts to delete or update a key to which existing foreign keys point- CASCADE CONSTRAINTS

Using Oracle Application Express, click the SQL Workshop tab in the menu bar. Click the Ob-ject Browser and verify that you have a table named copy_d_clients and a table named copy_d_events. If you don’t have these tables in your schema, create them before completing the exercises below. Here is how the original tables are related. The d_clients table has a primary key client_number. This has a primary-key constraint and it is referenced in the foreign-key constraint on the d_events table.

CREATE TABLE copy_d_clients

AS ( SELECT * FROM d_clients);

DESCRIBE copy_d_clients ;

DESCRIBE d_clients;

SELECT * FROM d_clients ;

SELECT * FROM copy_d_clients ;

SELECT *

FROM user_constraints

WHERE LOWER(table_name) IN ( 'd_clients', 'copy_d_clients');

CREATE TABLE copy_d_events

AS ( SELECT * FROM d_events);

DESCRIBE copy_d_events ;

DESCRIBE d_events;

SELECT * FROM d_events ;

SELECT * FROM copy_d_events ;

SELECT *

FROM user_constraints

WHERE LOWER(table_name) IN ( 'd_events', 'copy_d_events');

1.  What are four functions that an ALTER statement can perform on constraints?

·         ADD (uses modify clause to add not null on a column though)

·         DROP

·         ENABLE/DISABLE

2.  Since the tables are copies of the original tables, the integrity rules are not passed onto the new tables; only the column datatype definitions remain. You will need to add a PRIMARY KEY constraint to the copy_d_clients table. Name the primary key copy_d_clients_pk . What is the syntax you used to create the PRIMARY KEY constraint to the copy_d_clients.table?

ALTER TABLE copy_d_clients

ADD  CONSTRAINT copy_d_clt_client_number_pk PRIMARY KEY (client_number);

SELECT *

FROM user_constraints

WHERE LOWER(table_name) =  'copy_d_clients' and constraint_type = 'P';

3.  Create a FOREIGN KEY constraint in the copy_d_events table. Name the foreign key copy_d_events_fk. This key references the copy_d_clients table client_number column. What is the syntax you used to create the FOREIGN KEY constraint in the copy_d_events table?

ALTER TABLE  copy_d_events

ADD CONSTRAINT copy_d_eve_client_number_fk FOREIGN KEY (client_number) REFERENCES  copy_d_clients (client_number) ENABLE;

SELECT *

FROM user_constraints

WHERE LOWER(table_name) =  'copy_d_events' and constraint_type = 'R';

4.  Use a SELECT statement to verify the constraint names for each of the tables. Note that the tablenames must be capitalized.

SELECT constraint_name, constraint_type, table_name

FROM user_constraints

WHERE table_name =  UPPER('copy_d_events') ;

SELECT chld.table_name "Subject", chldcols.column_name "Subject Column Name", chld.constraint_name "constraint_name in Subject",  chld.constraint_type  "constraint_type in Subject",  prnt.table_name "Parent of FK",  prntcols.column_name "Parent's Column Name", prnt.constraint_name "Parent PK"

FROM user_constraints chld LEFT OUTER JOIN user_constraints prnt ON  chld.r_constraint_name = prnt.constraint_name

LEFT OUTER JOIN user_cons_columns chldcols ON chld.constraint_name = chldcols.constraint_name

LEFT OUTER JOIN user_cons_columns prntcols ON prnt.constraint_name = prntcols.constraint_name

WHERE chld.table_name = UPPER('copy_d_events');

SELECT constraint_name, constraint_type, table_name

FROM user_constraints

WHERE table_name =  UPPER('copy_d_clients') ;

SELECT chld.table_name "Subject", chldcols.column_name "Subject Column Name", chld.constraint_name "constraint_name in Subject",  chld.constraint_type  "constraint_type in Subject",  prnt.table_name "Parent of FK",  prntcols.column_name "Parent's Column Name", prnt.constraint_name "Parent PK"

FROM user_constraints chld LEFT OUTER JOIN user_constraints prnt ON  chld.r_constraint_name = prnt.constraint_name

LEFT OUTER JOIN user_cons_columns chldcols ON chld.constraint_name = chldcols.constraint_name

LEFT OUTER JOIN user_cons_columns prntcols ON prnt.constraint_name = prntcols.constraint_name

WHERE chld.table_name = UPPER('copy_d_clients');

a.   The constraint name for the primary key in the copy_d_clients table is _______________.

COPY_D_CLT_CLIENT_NUMBER_PK

b.  The constraint name for the foreign key in the copy_d_events table is _______________.

COPY_D_EVE_CLIENT_NUMBER_FK

5.  Drop the PRIMARY KEY constraint on the copy_d_clients table. Explain your results.

ALTER TABLE copy_d_clients

DROP CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK  ;

I get message:

 ORA-02273: this unique/primary key is referenced by some foreign keys

ALTER TABLE copy_d_clients

DROP CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK CASCADE  ;

SELECT constraint_name, constraint_type, table_name

FROM user_constraints

WHERE table_name =  UPPER('copy_d_events') AND constraint_type = 'R';

The CASCADE option of the DROP clause causes any dependent constraints also to be dropped.

(After this I recreated the constraints following point 2 and 3 above)

6.  Add the following event to the copy_d_events table. Explain your results.

ID	NAME	EVENT_DATE	DESCRIPTION	COST	VENUE_ID	PACKAGE_CODE	THEME_CODE	CLIENT_NUMBER
140	Cline Bas Mitzvah	15-Jul-2004	Church and Private Home formal	4500	105	87	77	7125

INSERT INTO copy_d_events(client_number,id,name,event_date,description,cost,venue_id,package_code,theme_code)

VALUES(7125,140,'Cline Bas Mitzvah',TO_DATE('15-Jul-2004','dd-Mon-yyyy'),'Church and Private Home formal',4500,105,87,77);

ORA-02291: integrity constraint (HKUMAR.COPY_D_EVE_CLIENT_NUMBER_FK) violated - parent key not found

SELECT * FROM copy_d_clients WHERE client_number = 7125;

no data found

That is why I got above error ORA-02291.

7.  Create an ALTER TABLE query to disable the primary key in the copy_d_clients table. Then add the values from #5 to the copy_d_events table. Explain your results.

ALTER TABLE copy_d_clients

DISABLE CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK ;

I get message ORA-02297: cannot disable constraint (HKUMAR.COPY_D_CLT_CLIENT_NUMBER_PK) - dependencies exist.

ALTER TABLE copy_d_clients

DISABLE CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK CASCADE;

Table altered.

8.  Repeat question 5: Insert the new values in the copy_d_events table. Explain your results.

INSERT INTO copy_d_events(client_number,id,name,event_date,description,cost,venue_id,package_code,theme_code)

VALUES(7125,140,'Cline Bas Mitzvah',TO_DATE('15-Jul-2004','dd-Mon-yyyy'),'Church and Private Home formal',4500,105,87,77);

1 row(s) inserted.

9.  Enable the primary-key constraint in the copy_d_clients table. Explain your results.

ALTER TABLE copy_d_clients

ENABLE CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK CASCADE;

ORA-00933: SQL command not properly ended

ALTER TABLE copy_d_clients

ENABLE CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK ;

In case of enable I don’t have option like CASCADE.

10. If you wanted to enable the foreign-key column and reestablish the referential integrity between these two tables, what must be done?

ALTER TABLE copy_d_events

ENABLE CONSTRAINT COPY_D_EVE_CLIENT_NUMBER_FK;

ORA-02298: cannot validate (HKUMAR.COPY_D_EVE_CLIENT_NUMBER_FK) - parent keys not found

So first I need to fix the row with client_number to a valid value/null.

But since client_number is not nullable, I will either have to delete invalid row or update that row.

DELETE FROM copy_d_events WHERE

client_number  NOT IN ( SELECT client_number FROM copy_d_clients);

1 row(s) deleted.

ALTER TABLE copy_d_events

ENABLE CONSTRAINT COPY_D_EVE_CLIENT_NUMBER_FK;

Table altered.

11. Why might you want to disable and then re-enable a constraint?

Generally to make bulk operations fast, where my input data is diligently sanitized and I am sure, it is safe to save some time in this clumsy process.

12. Query the data dictionary for some of the constraints that you have created. How does the data dictionary identify each constraint type?

Queries are same as in point 2,3, 4 above.

I can check value of CONSTRAINT_TYPE in all_constraints/user_constraints view.

·         C - Check constraint

Sub-case - if I see SEARCH_CONDITION something like "FIRST_NAME" IS NOT NULL , its a NOT NULL constraint.

·         P - Primary key

·         R - Referential integrity (fk)

·         U - Unique key