ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
Data Dictionary Objects
ALL_CONS_COLUMNS
CDB_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
ALL_CONSTRAINTS
CDB_IND_SUBPARTITIONS
ICOL$
ALL_INDEXES
CON$
IND$
ALL_IND_PARTITIONS
CCOL$
USER_CONS_COLUMNS
ALL_IND_SUBPARTITIONS
DBA_CONS_COLUMNS
USER_CONSTRAINTS
CDB_CONS_COLUMNS
DBA_CONSTRAINTS
USER_INDEXES
CDB_CONSTRAINTS
DBA_INDEXES
USER_IND_PARTITIONS
CDB_INDEXES
DBA_IND_PARTITIONS
USER_IND_SUBPARTITIONS
Privileges Related To Constraints
To create a foreign key constraint on an object in a different schema you must have the REFERENCES privilege on the columns of the referenced key in the parent table or view.
Constraint Types & Codes
Type Code
Type Description
Acts On Level
C
Check on a table
Column
O
Read Only on a view
Object
P
Primary Key
Object
R
Referential AKA Foreign Key
Column
U
Unique Key
Column
V
Check Option on a view
Object
For information on JSON see the link at page bottom
Alter Constraint
Constraints can not be altered. They must be dropped and recreated. Some modifications are allowed via ALTER TABLE.
Definitions
DISABLE
Allows incoming data, regardless of whether it conforms to the constraint
ENABLE
Ensures that all incoming data conforms to the conditions defined by the constraint
FREELISTS
Specifies the number of lists of available index blocks. Oracle ignores this if the tablespace in which the object resides is in automatic segment-space management (ASSM) mode.
INITRANS
Specify the initial number of concurrent transaction entries allocated within each data block allocated to the database object. This value can range from 1 to 255 and defaults to 2 for indexes.
NORELY
Do not enforce the enabled constraint's conditions
NOVALIDATE
Validates changes but does not validate data previously existing in the table
PCTFREE
Control the amount of free space in the index data block for updating. In a Primary Key constraint's index it would be rare that this not be set to zero (0).
RELY
Enforce the enabled constraint
Surrogate Key 1
Exposed locators such as IDENTITY are not surrogate keys. A surrogate is totally hidden from users and maintained by the system -- think of how an index works. If you change a natural key, the DRI actions will cascade it for you.
Hell does not break loose. But if your auto-number and real key are out of synch, then you are screwed. Just enter the same record several times to get different auto-numbers on duplicate rows.
Drop all but one of the dups and then try to find all of the referenced rows in other tables.
If you maintain the exposed locator by hand, you have extra work, extra disk seeks and will eventually make a mistake, thus destroying your data integrity. How would you validate and verify your data?
The UPC code just went from 10 to 13 digits. It does not matter if you used an auto-number or the UPC, the UPC has to be updated. If you had used a proper RDBMS design and SQL-92, you would do an ALTER DOMAIN and a single update with CASCADE.
This is one of the MANY reasons that rows are not records. This is a problem in SQL Server because it is still based on a contiguous storage model, but other products are not, so wind up using tools to write scripts.
~ Joe Celko in comp.databases.ms-sqlserver 10/13/2005
Surrogate Key 2
I think that you missed the concept of IDENTITY and the Relational Model. A data type in SQL has to:
NULL-able
More than one column can have the same data type
Has to take CHECK() constraints
Appropriate computations can done on it (numeric, string or temporal)
IDENTITY has none of the properties of a data type because it is not a data type at all. It is an exposed physical locator attached to a table, not a property of a column.
It is derived from the physical storage used on one machine, like pointer chains in the old navigational DBs or row_ids or hash tables.
What does this mean in your Logical data model? Since it has to reference something in the reality of that data model to be a valid RDBMS, how do you validate and verify it?
I would guess that you do none of these basic things, but are mimicking a sequential tape file application which depends on counting records in procedural code. Do you have cursors, too?
The whole idea of SQL is to use sets and declarative code. This is probably just the tip of the iceberg and all you will have is more and more kludges piled on each other.
The thing will run for awhile, but it will choke from lack of data integrity or the inability to scale up or to port to another platform.
Fix the design, then fix the application.
~ Joe Celko in comp.databases.ms-sqlserver 11/25/2007
TABLESPACE
The tablespace where any associated index will be built
VALIDATE
Validate previously existing data as well as all changes
Tables and Data for Constraint Demos
DDL & DML
conn uwclass/uwclass@pdbdev
CREATE TABLE person (
person_id NUMBER(10),
last_name VARCHAR2(30) NOT NULL,
per_age NUMBER(3),
per_state VARCHAR2(2),
per_zip VARCHAR2(5),
ssn VARCHAR2(11),
status VARCHAR2(1));
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
PRIMARY KEY (<column_name>)
USING INDEX
PCTFREE <percentage of block available for update>
INITRANS <integer>
MAXTRANS <integer>
STORAGE (FREELISTS <integer>)
TABLESPACE <tablespace_name>;
desc person
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'PERSON';
ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
USING INDEX
PCTFREE 0
TABLESPACE uwdata;
desc person
set linesize 121
SELECT constraint_name, constraint_type, index_name, generated, status, deferrable
FROM user_constraints
WHERE table_name = 'PERSON';
-- note that a unique index is created
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON';
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON';
col column_name format a30
SELECT constraint_name, position, column_name
FROM user_cons_columns
WHERE table_name = 'PERSON';
SELECT index_name, column_position, column_name, descend
FROM user_ind_columns
WHERE table_name = 'PERSON';
INSERT INTO person
(person_id, last_name)
VALUES
(1, 'Morgan');
INSERT INTO person
(person_id, last_name)
VALUES
(2, 'Millsap');
INSERT INTO person
(person_id, last_name)
VALUES
(1, 'Lofstrom');
SELECT *
FROM person;
Create Composite Primary Key
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
PRIMARY KEY <column_name, column_name, ....>
USING INDEX
PCTFREE <percentage of block available for update>
TABLESPACE <tablespace_name>;
desc person_uclass_ie
SELECT COUNT(*)
FROM user_constraints
WHERE table_name = 'PERSON_UCLASS_IE';
ALTER TABLE person_uclass_ie
ADD CONSTRAINT pk_person_uclass_ie
PRIMARY KEY (person_id, class_id)
USING INDEX
PCTFREE 0;
desc person_uclass_ie
SELECT constraint_name, constraint_type, index_name, generated, status, deferrable
FROM user_constraints
WHERE table_name = 'PERSON_UCLASS_IE';
-- note that a unique index is created
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON_UCLASS_IE';
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name_list>)
NOT
DEFERRABLE
USING INDEX
PCTFREE <integer>
TABLESPACE <tablespace_name>;
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
PRIMARY KEY (<column_name_list>)
DEFERRABLE INITIALLY <IMMEDIATE | DEFERRED>
USING INDEX
PCTFREE <integer>
TABLESPACE <tablespace_name>;
desc uclass
SELECT COUNT(*)
FROM user_constraints
WHERE table_name = 'UCLASS';
ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id)
INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0;
desc uclass
-- note NOT NULL not created on class_id
SELECT constraint_name, constraint_type, index_name, generated, status, deferrable
FROM user_constraints
WHERE table_name = 'UCLASS';
-- note a non-unique index is created
SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'UCLASS';
col column_name format a30
SELECT constraint_name, position, column_name
FROM user_cons_columns
WHERE table_name = 'UCLASS';
SELECT index_name, column_position, column_name, descend
FROM user_ind_columns
WHERE table_name = 'UCLASS';
INSERT INTO uclass
(class_id)
VALUES
(1);
INSERT INTO uclass
(class_id)
VALUES
(2);
INSERT INTO uclass
(class_id)
VALUES
(1);
SELECT *
FROM uclass;
COMMIT;
SELECT *
FROM uclass;
Disabling and Enabling Primary Key Constraints
ALTER TABLE <table_name> DISABLE PRIMARY KEY;
CREATE TABLE t (
rid NUMBER(5));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (rid)
USING INDEX
PCTFREE 0;
SELECT index_name, index_type, uniqueness, pct_free
FROM user_indexes
WHERE table_name = 'T';
ALTER TABLE t DISABLE PRIMARY KEY;
SELECT index_name, index_type, uniqueness
FROM user_indexes
WHERE table_name = 'T';
ALTER TABLE t ENABLE PRIMARY KEY;
SELECT index_name, index_type, uniqueness, pct_free
FROM user_indexes
WHERE table_name = 'T';
Disable Validate to make a table read only
ALTER TABLE <table_name> MODIFY CONSTRAINT <constraint_name>
DISABLE VALIDATE;
SELECT constraint_name, status, validated
FROM user_constraints;
ALTER TABLE uclass MODIFY CONSTRAINT pk_uclass
DISABLE VALIDATE;
SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_type = 'P';
INSERT INTO uclass (class_id) VALUES (201);
COMMIT;
Enable Primary Key without validation of pre-existing data
ALTER TABLE <table_name> ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE uclass ENABLE NOVALIDATE PRIMARY KEY;
SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_type = 'P';
INSERT INTO uclass (class_id) VALUES (101);
COMMIT;
ALTER TABLE uclass DROP PRIMARY KEY;
INSERT INTO uclass (class_id) VALUES (101);
INSERT INTO uclass (class_id) VALUES (101);
INSERT INTO uclass (class_id) VALUES (101);
COMMIT;
ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
UNIQUE (<column_name>)
USING INDEX
PCTFREE <pct of block available for update>
TABLESPACE <tablespace_name>;
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state);
DELETE state_zip
WHERE ROWID IN (
SELECT LEAD(ROWID)
OVER (PARTITION BY state ORDER BY NULL)
FROM state_zip);
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state);
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'STATE_ZIP';
col column_name format a30
SELECT constraint_name, column_name, position
FROM user_cons_columns
ORDER BY constraint_name, position;
SELECT * FROM state_zip;
INSERT INTO state_zip
(state, zip_code)
VALUES
('WA', '98004');
Create unique constraint with USING INDEX clause
ALTER TABLE <table_name>
ADD CONSTRAINT <primary_key_constraint_name>
UNIQUE <column_name, column_name, ....>
USING INDEX
PCTFREE <pct of block available for update>
TABLESPACE <tablespace_name>;
ALTER TABLE state_zip
DROP CONSTRAINT uc_state_zip_state;
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state)
USING INDEX
PCTFREE 0
TABLESPACE uwdata;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'STATE_ZIP';
SELECT index_name
FROM user_indexes
WHERE table_name = 'STATE_ZIP';
-- SYSDATE, SYSTIMESTAMP, and USER can not be used in a check constraint
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_reg_date
CHECK (reg_date > SYSDATE);
Check Constraint Based on a POSIX Regular Expression
CREATE TABLE pn_regexp(
phone_no VARCHAR2(14));
ALTER TABLE pn_regexp
ADD CONSTRAINT cc_ph_format
CHECK (REGEXP_LIKE(phone_no, '^\([[:digit:]]{3}\) [[:digit:]]{3}-[[:digit:]]{4}$'));
INSERT INTO pn_regexp (phone_no) VALUES ('(111) 222-3333');
INSERT INTO pn_regexp (phone_no) VALUES ('111-222-3333');
Check Constraint for IP Addresses
CREATE TABLE ipaddrs(
ip_address VARCHAR2(39));
ALTER TABLE ipaddrs
ADD CONSTRAINT cc_ip_address
CHECK ((testcol LIKE '___:___:___:___')
OR (testcol LIKE '____:____:____:____:____:____:____:____'));
Deferrable Constraints
Note: The following correspondence is from Julian Dyke and valuable enough I thought I should share it here in the library.
From: Daniel A. Morgan [mailto:dan @ morganslibrary.org]
Sent: 05 November 2007 01:41
To: info @ juliandyke.com
Subject: Deferrable Constraints
I've stumbled trying to answer a student question and wonder if you might know the answer. A thorough search of the docs has proven that either the answer is not documented or I am not good at entering search criteria.
I create a deferrable referential constraint such as this:
ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
INITIALLY DEFERRED DEFERRABLE;
Look in the data dictionary
SELECT constraint_name, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';
which is technically correct because my SET CONSTRAINTS only affects my session and not others. But the question is where in Oracle is it storing the fact that within my specific session I have altered the behaviour of that constraint?
Thanks.
Here is Julian's initial reply.
The answer is that SET CONSTRAINTS ALL IMMEDIATE is implemented as a transaction callback which is a type of state object.
When you initially create a transaction you allocate a ktxcb structure from a segmented array. You can see these structures in X$KTCXB and once the transaction has started in V$TRANSACTION.
If you do a state object dump (SYSTEMSTATE level 10), you can see the transactions currently in progress for each session (and process).
When you issue SET CONSTRAINTS ALL IMMEDIATE, a transaction callback state object is created. This is chained from the transaction object.
Both transaction (ktcxb) and transaction callback (ktccts?) are segmented arrays with names "transaction" and "txncallback" respectively.
The amount of memory allocated to these structures can be seen in V$SGASTAT (again with names "transaction" and "txncallback" respectively).
I can't find any X$ tables which externalise the transaction callback structure. However, it is easy to find in the system state dump.
Following is an extract from a level 10 SYSTEMSTATE dump which following the execution of SET CONSTRAINTS ALL IMMEDIATE in a transaction:
I don't know what happens if with SET CONSTRAINTS ALL DEFERRED yet.
Any use?
Julian
More later if we figure it out.
Defer One Constraint
SET CONSTRAINT <constraint_name> DEFERRED;
SET CONSTRAINT fk_person_state_zip DEFERRED;
Defer All Constraints
SET CONSTRAINT ALL DEFERRED;
SET CONSTRAINTS ALL DEFERRED;
Stop Constraint Deferment
SET CONSTRAINT <constraint_name> IMMEDIATE;
SET CONSTRAINT fk_person_state_zip IMMEDIATE;
Stop All Constraint Deferment
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL IMMEDIATE;
Note: A non-deferrable constraint is generally policed by a unique index (a unique index is created unless a suitable index already exists).
A deferrable constraint must be policed by a non-unique index (as it's possible for a point of time during a transaction for duplicate values to exist).
This is why it is not possible to alter a constraint from non-deferrable to deferrable. Doing so would require Oracle to drop and recreate the index.
A PK enforces uniqueness procedurally without relying on a unique index. The main advantage of a non-unique index is the constraint can be disabled and re-enabled without the index being dropped and recreated.
ALTER CONSTRAINT
Alter Constraint syntax does not exist. Look at the ALTER TABLE demos above and below
DROP CONSTRAINT
Generic Constraint Drop. Will drop any constraint by name.
ALTER TABLE <table_name> DROP CONSTRAINT <primary_key_constraint_name> [ONLINE];
SELECT constraint_name, table_name
FROM user_constraints;
ALTER TABLE person DROP CONSTRAINT pk_person ONLINE;
SELECT constraint_name, table_name
FROM user_constraints;
Specific Primary Key Drop
ALTER TABLE <table_name> DROP PRIMARY KEY [ONLINE];
ALTER TABLE uclass DROP PRIMARY KEY ONLINE;
SELECT constraint_name, table_name
FROM user_constraints
WHERE constraint_type = 'P';
Index Drop After PK Drop Demo
conn sys@pdbdev as sysdba
GRANT select ON ind$ TO uwclass;
conn uwclass/uwclass@pdbdev
CREATE TABLE t (
idcol NUMBER(5),
chcol VARCHAR2(5));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(idcol)
USING INDEX
PCTFREE 0;
SELECT constraint_name, table_name, index_name
FROM user_constraints
WHERE constraint_type = 'P';
col object_name format a30
SELECT object_id, object_name
FROM user_objects
WHERE object_type = 'INDEX';
SELECT property
FROM sys.ind$
WHERE obj# = 63069;
-- property = 4097
ALTER TABLE t DROP PRIMARY KEY;
SELECT table_name, index_name
FROM user_indexes
ORDER BY 1;
CREATE UNIQUE INDEX ix_t
ON t(idcol);
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY(idcol)
USING INDEX;
SELECT constraint_name, table_name, index_name
FROM user_constraints
WHERE constraint_type = 'P';
SELECT object_id, object_name
FROM user_objects
WHERE object_type = 'INDEX';
SELECT property
FROM sys.ind$
WHERE obj# = 63070;
-- property = 1
ALTER TABLE t DROP PRIMARY KEY;
SELECT table_name, index_name
FROM user_indexes
ORDER BY 1;
Specific unique constraint drop
ALTER TABLE DROP UNIQUE (<column_name>) [ONLINE];
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state)
USING INDEX
PCTFREE 0;
ALTER TABLE state_zip DROP UNIQUE (state) ONLINE;
Drop unique constraint with dependencies
ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>
CASCADE;
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state, zip_code)
USING INDEX
PCTFREE 0;
ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code);
ALTER TABLE state_zip
DROP CONSTRAINT uc_state_zip_state;
ALTER TABLE state_zip
DROP CONSTRAINT uc_state_zip_state
CASCADE
ONLINE;
*
ERROR at line 1:
ORA-14419: DROP CONSTRAINT ONLINE does not support the CASCADE option.
ALTER TABLE state_zip
DROP CONSTRAINT uc_state_zip_state
CASCADE;
Drop foreign key
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
ALTER TABLE person DROP CONSTRAINT fk_person_state_zip;
Drop Primary Key with a Foreign Key dependency
ALTER TABLE <table_name>
DROP CONSTRAINT <primary_key_constraint_name>
CASCADE CONSTRAINTS;
ALTER TABLE state_zip DROP PRIMARY KEY;
ALTER TABLE state_zip DROP PRIMARY KEY CASCADE;
SELECT table_name, constraint_name, constraint_type
FROM user_constraints;
Table Drop with Foreign Key Constraint
DROP TABLE <table_name> CASCADE CONSTRAINTS [PURGE];
SELECT table_name, constraint_name, r_constraint_name
FROM user_constraints
WHERE constraint_type = 'R';
SELECT table_name
FROM user_constraints
WHERE constraint_name = 'PK_STATE_ZIP';
DROP TABLE state_zip;
DROP TABLE state_zip CASCADE CONSTRAINTS;
SELECT object_name, original_name, type
FROM recyclebin;
FLASHBACK TABLE state_zip TO BEFORE DROP;
SELECT constraint_name, constraint_type
FROM user_constraints
WHERE table_name = 'STATE_ZIP';
Rename Constraint
Rename Constraint
ALTER TABLE <table_name>
RENAME CONSTRAINT <current_constraint_name>
TO <new_constraint_name>;
SELECT constraint_name
FROM user_constraints;
ALTER TABLE state_zip
RENAME CONSTRAINT "BIN$HY86N3B2RQi/1ODAiR9CTw==$0"
TO pk_state_zip;
SELECT constraint_name
FROM user_constraints;
Deferrable Constraint Demos
A deferrable constraint is checked AFTER an AFTER trigger fires
CREATE TABLE t (
idcol NUMBER,
txcol VARCHAR2(10));
ALTER TABLE t
ADD CONSTRAINT pk_t
PRIMARY KEY (idcol)
INITIALLY DEFERRED DEFERRABLE
USING INDEX;
CREATE OR REPLACE TRIGGER trig
AFTER INSERT
ON t
FOR EACH ROW
BEGIN
dbms_output.put_line(:NEW.idcol);
END trig;
/
set serveroutput on
INSERT INTO t
(idcol, txcol)
VALUES
(42, 'Test');
INSERT INTO t
(idcol, txcol)
VALUES
(42, 'Test');
COMMIT;
Affect on performance
SQL> startup
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 369101924 bytes
Database Buffers 159383552 bytes
Redo Buffers 5828608 bytes
Database mounted.
Database opened.
SQL> conn uwclass/uwclass@pdbdev
Connected.
SQL> CREATE TABLE test (
2 col1 NUMBER(20));
Table created.
SQL> ALTER TABLE test
2 ADD CONSTRAINT pk_test
3 PRIMARY KEY (col1);
Table altered.
SQL> set timing on
SQL> BEGIN
2 FOR i IN 1 .. 1000000 LOOP
3 INSERT INTO test (col1) VALUES (i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> DROP TABLE test PURGE;
Table dropped.
Elapsed: 00:00:02.59
SQL> CREATE TABLE test (
2 col1 NUMBER(20));
Table created.
Elapsed: 00:00:00.10
SQL> ALTER TABLE test
2 ADD CONSTRAINT pk_test
3 PRIMARY KEY (col1)
4 DEFERRABLE INITIALLY DEFERRED;
Table altered.
Elapsed: 00:00:00.11
SQL> BEGIN
2 FOR i IN 1 .. 1000000 LOOP
3 INSERT INTO test (col1) VALUES (i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed.
Constraint Related Queries
List a child table's referential constraints and their associated parent table
SELECT t.owner CHILD_OWNER,
t.table_name CHILD_TABLE,
t.constraint_name FOREIGN_KEY_NAME,
r.owner PARENT_OWNER,
r.table_name PARENT_TABLE,
r.constraint_name PARENT_CONSTRAINT
FROM user_constraints t, user_constraints r
WHERE t.r_constraint_name = r.constraint_name
AND t.r_owner = r.owner
AND t.constraint_type='R'
AND t.table_name = <child_table_name>;
List foreign keys and referenced table and columns
SELECT DECODE(c.status,'ENABLED','C','c') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(p.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p,
all_constraints c
WHERE c.owner = upper('UWCLASS')
AND c.table_name = upper('PERSON')
AND c.constraint_type = 'R'
AND p.owner = c.r_owner
AND p.constraint_name = c.r_constraint_name
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
UNION ALL
SELECT DECODE(c.status,'ENABLED','P','p') t,
SUBSTR(c.constraint_name,1,31) relation,
SUBSTR(cc.column_name,1,24) columnname,
SUBSTR(c.table_name,1,20) tablename
FROM all_cons_columns cc, all_constraints p, all_constraints c
WHERE p.owner = upper('UWCLASS')
AND p.table_name = upper('PERSON')
AND p.constraint_type in ('P','U')
AND c.r_owner = p.owner
AND c.r_constraint_name = p.constraint_name
AND c.constraint_type = 'R'
AND cc.owner = c.owner
AND cc.constraint_name = c.constraint_name
AND cc.table_name = c.table_name
ORDER BY 1, 4, 2, 3
/
Procedure to DISABLE all constraints
CREATE OR REPLACE PROCEDURE disable_fk_constraint AUTHID DEFINER IS
CURSOR fke_cur IS
SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED';
ExStr VARCHAR2(4000);
BEGIN
FOR fke_rec IN fke_cur LOOP
ExStr := 'ALTER TABLE ' || fke_rec.table_name ||
'DISABLE CONSTRAINT ' ||
fke_rec.constraint_name;
BEGIN
EXECUTE IMMEDIATE ExStr;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END disable_fk_constraint;
/
Find Unindexed Foreign Keys
This script was found at OraQA and attributed to Tom Kyte of Oracle though the link to his site was not working.
I have now been contacted by the original author, Timo Raitalaakso, who has given me this link: [Click Here].
set linesize 121
col status format a6
col columns format a30 word_wrapped
col table_name format a30 word_wrapped
