Oracle Constraints
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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
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 autonumber and real key are out of synch, then you are screwed. Just enter the same record several times to get different autonumbers 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 autonumber 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:
  1. NULL-able
  2. More than one column can have the same data type
  3. Has to take CHECK() constraints
  4. 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

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));

CREATE TABLE uclass (
class_id   NUMBER(7),
class_name VARCHAR2(35),
reg_fee    NUMBER(6,2),
reg_date   DATE);

CREATE TABLE person_uclass_ie (
person_id NUMBER(10),
class_id  NUMBER(7));

CREATE TABLE state_zip (
state    VARCHAR2(2),
zip_code VARCHAR2(5));

INSERT INTO state_zip VALUES ('WA', '98004');
INSERT INTO state_zip VALUES ('WA', '98101');
INSERT INTO state_zip VALUES ('OR', '97405');
INSERT INTO state_zip VALUES ('CA', '94002');
INSERT INTO state_zip VALUES ('NY', '10010');
COMMIT;
 
Primary Key - Type P
Create Single Column Primary Key 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';

exec dbms_stats.gather_index_stats(USER, 'PK_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';

exec dbms_stats.gather_index_stats(USER, 'PK_PERSON_UCLASS_IE');

SELECT index_type, uniqueness, clustering_factor
FROM user_indexes
WHERE table_name = 'PERSON_UCLASS_IE';

col column_name format a30

SELECT constraint_name, position, column_name
FROM user_cons_columns
WHERE table_name = 'PERSON_UCLASS_IE'
ORDER BY 2;

SELECT index_name, column_position, column_name, descend
FROM user_ind_columns
WHERE table_name = 'PERSON_UCLASS_IE'
ORDER BY 2;

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(1, 1);

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(2, 1);

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(3, NULL);

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(1, 1);

SELECT *
FROM person_uclass_ie;
Create Deferrable Primary Key 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;

SELECT *
FROM uclass;

ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id)
INITIALLY IMMEDIATE DEFERRABLE
NOVALIDATE
;

SELECT constraint_name, status, validated
FROM user_constraints
WHERE constraint_type = 'P';

INSERT INTO uclass (class_id) VALUES (1);
COMMIT;

INSERT INTO uclass (class_id) VALUES (101);
COMMIT;

DELETE FROM uclass
WHERE class_id = 101
AND rownum < 4;

COMMIT;
 
Unique - Type U
Create unique constraint on a single column 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';
 
Referential - Type R (Foreign Key)
Create Foreign Key an a single column ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCING <table_name> (<column_name>)
DEFERRABLE INITIALLY <IMMEDIATE | DEFERRED>;
ALTER TABLE person_uclass_ie
ADD CONSTRAINT fk_person_uclass_person_id
FOREIGN KEY (person_id)
REFERENCING person (person_id)
INITIALLY DEFERRED DEFERRABLE;

ALTER TABLE person
ADD CONSTRAINT pk_person
PRIMARY KEY (person_id)
INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0;

ALTER TABLE person_uclass_ie
ADD CONSTRAINT fk_person_uclass_person_id
FOREIGN KEY (person_id)
REFERENCING person (person_id)
INITIALLY DEFERRED DEFERRABLE;

SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'PERSON_UCLASS_IE';

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 = 'PERSON_UCLASS_IE';

-- check for index on FK
set linesize 121
col status format a6
col columns format a30 word_wrapped
col table_name format a30 word_wrapped

SELECT DECODE(b.table_name, NULL, '****', 'Ok' ) STATUS, a.table_name, a.columns, b.columns from (
  SELECT SUBSTR(a.table_name,1,30) table_name,
  SUBSTR(a.constraint_name,1,30) constraint_name, MAX(DECODE(position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position, 2,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 3,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 4,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 5,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 6,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 7,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 8,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 9,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,10,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,11,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,12,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,13,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,14,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,15,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,16,', '|| SUBSTR(column_name,1,30),NULL)) columns from user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND constraint_type = 'R'
GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a, (
  SELECT SUBSTR(table_name,1,30) table_name,
  SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) columns from user_ind_columns group by SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%';

-- FK should be indexed to prevent deadlocks
CREATE INDEX ix_puie_person_id
ON person_uclass_ie (person_id);

-- verify FK problem addressed
SELECT DECODE(b.table_name, NULL, '****', 'Ok' ) STATUS, a.table_name, a.columns, b.columns from (
  SELECT SUBSTR(a.table_name,1,30) table_name,
  SUBSTR(a.constraint_name,1,30) constraint_name, MAX(DECODE(position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position, 2,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 3,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 4,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 5,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 6,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 7,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 8,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position, 9,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,10,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,11,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,12,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,13,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,14,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,15,', '|| SUBSTR(column_name,1,30),NULL)) || max(DECODE(position,16,', '|| SUBSTR(column_name,1,30),NULL)) columns from user_cons_columns a, user_constraints b
WHERE a.constraint_name = b.constraint_name
AND constraint_type = 'R'
GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30) ) a, (
  SELECT SUBSTR(table_name,1,30) table_name,
  SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position, 1,
  SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position, 2,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 3,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 4,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 5,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 6,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 7,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 8,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position, 9,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,10,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,11,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,12,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,13,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,14,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,15,', '||SUBSTR(column_name,1,30),NULL)) || max(DECODE(column_position,16,', '||SUBSTR(column_name,1,30),NULL)) columns from user_ind_columns group by SUBSTR(table_name,1,30), SUBSTR(index_name,1,30) ) b where a.table_name = b.table_name (+) and b.columns (+) like a.columns || '%';

SELECT * FROM person;

SELECT * FROM person_uclass_ie;

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(2, 202);
COMMIT;

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(3, 202);
COMMIT;

SELECT * FROM person_uclass_ie;

INSERT INTO person
(person_id, last_name)
VALUES
(3, 'Havemeyer');

INSERT INTO person_uclass_ie
(person_id, class_id)
VALUES
(3, 202);
COMMIT;

SELECT * FROM person;

SELECT * FROM person_uclass_ie;
Create Composite Foreign Key ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name, column_name, ....>)
REFERENCING <table_name> (<column_name,column_name,....>);
ALTER TABLE state_zip
ADD CONSTRAINT pk_state_zip
PRIMARY KEY (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);

col column_name format a30

SELECT constraint_name, column_name, position
FROM user_cons_columns
WHERE table_name = 'PERSON'
ORDER BY 1,3;

UPDATE person
SET per_state = 'WA', per_zip = '98004'
WHERE person_id = 1;

UPDATE person
SET per_state = 'WA', per_zip = '98005'
WHERE person_id = 2;
Create deferrable foreign key ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name, column_name, ....>)
REFERENCING <table_name> (<column_name,column_name,....>)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE person
DROP CONSTRAINT fk_person_state_zip;

ALTER TABLE person
ADD CONSTRAINT fk_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
INITIALLY DEFERRED DEFERRABLE;

UPDATE person
SET per_state = 'WA', per_zip = '98004'
WHERE person_id = 1;

UPDATE person
SET per_state = 'WA', per_zip = '98005'
WHERE person_id = 2;

COMMIT;
SET CONSTRAINTS SET CONSTRAINTS ALL <IMMEDIATE | DEFERRED>;
SELECT constraint_name, constraint_type, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';

SET CONSTRAINTS ALL IMMEDIATE;

SELECT constraint_name, constraint_type, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';

SELECT * FROM person;

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Millsap', 57, 'TX', '98005');

SET CONSTRAINTS ALL DEFERRED;

SELECT constraint_name, constraint_type, deferrable, deferred
FROM user_constraints
WHERE table_name = 'PERSON';

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Millsap', 57, 'WA', '98005');

COMMIT;
Disable constraint ALTER TABLE <table_name>
DISABLE CONSTRAINT <constraint_name>;
ALTER TABLE person
DISABLE CONSTRAINT fk_person_state_zip;

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Millsap', 57, 'WA', '98005');

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Millsap', 57, 'OR', '98005');

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Millsap', 57, 'XX', 'ABCDE');

COMMIT;

-- fix the data if the constraint does not re-enable and repeat enable
Enable constraint with EXCEPTIONS INTO clause ALTER TABLE <table_name>
ENABLE CONSTRAINT <constraint_name>;
ALTER TABLE person
DISABLE CONSTRAINT pk_person;

SELECT constraint_name, status
FROM user_constraints
WHERE table_name = 'PERSON';

@?\rdbms\admin\utlexcpt.sql

desc exceptions

ALTER TABLE person
ENABLE PRIMARY KEY
EXCEPTIONS INTO exceptions;

SELECT * FROM exceptions;

ALTER TABLE person
ENABLE CONSTRAINT fk_person_state_zip
EXCEPTIONS INTO exceptions;

SELECT * FROM exceptions;
On Delete Cascade ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCES <table_name> (<column_name>)
ON DELETE CASCADE;
INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(1, 'Morgan', 54, 'WA', '98004');

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, 'Millsap', 57, 'NY', '10010');
COMMIT;

ALTER TABLE person
ADD CONSTRAINT fkocd_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
ON DELETE CASCADE;

SELECT constraint_name, delete_rule
FROM user_constraints;

SELECT * FROM person;

SELECT * FROM state_zip;

DELETE FROM state_zip
WHERE zip_code = '10010';
On Delete Set NULL ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>)
REFERENCES <table_name> (<column_name>)
ON DELETE SET NULL;
ALTER TABLE person
DROP CONSTRAINT fkocd_person_state_zip;

ALTER TABLE person
ADD CONSTRAINT fkdsn_person_state_zip
FOREIGN KEY (per_state, per_zip)
REFERENCES state_zip (state, zip_code)
ON DELETE SET NULL;

SELECT constraint_name, delete_rule
FROM user_constraints;

SELECT * FROM person;

SELECT * FROM state_zip;

DELETE FROM state_zip
WHERE zip_code = '98004';

SELECT * FROM state_zip;

SELECT * FROM person;
Using a virtual column -- requires the prior creation of the postal_codes table [ Click Here]

CREATE TABLE virtual (
zip_code   VARCHAR2(10),
zip_unique VARCHAR2(5) AS (SUBSTR(zip_code,1,5))
REFERENCES postal_code(zip_code));

SELECT zip_code, COUNT(*)
FROM postal_code
WHERE zip_code IN ('98000', '98040')
GROUP BY zip_code;

INSERT INTO virtual (zip_code) VALUES ('98040-0521');
INSERT INTO virtual (zip_code) VALUES ('98000-0521');
 
Check - Type C
Create Check Constraint with EQUALS ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> = <condition>);
col search_condition format a40

SELECT table_name, constraint_name, search_condition
FROM user_constraints
WHERE constraint_type = 'C';

desc person

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status = 'X');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'X'
WHERE person_id = 1;

UPDATE person
SET status = NULL
WHERE person_id = 2;

UPDATE person
SET status = 'Z'
WHERE person_id = 3;

ALTER TABLE person
DROP CONSTRAINT cc_person_status;
Create Check Constraint With NOT EQUALS ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> != <value>);
ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status != 'X');

UPDATE person
SET status = NULL;

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status != 'X');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'A'
WHERE person_id = 1;

UPDATE person
SET status = '4'
WHERE person_id = 2;

UPDATE person
SET status = 'X'
WHERE person_id = 3;
Create Check Constraint with LIKE ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> LIKE <condition>);
ALTER TABLE person
ADD CONSTRAINT cc_person_ssn
CHECK (ssn LIKE '___-__-____');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET ssn = '333-22-4444'
WHERE person_id = 1;

UPDATE person
SET ssn = '123-45-6789'
WHERE person_id = 2;

UPDATE person
SET ssn = 'Oops'
WHERE person_id = 3;

ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_class_name
CHECK (class_name LIKE 'Ora%');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'UCLASS';

INSERT INTO uclass VALUES (101, 'Oracle');
INSERT INTO uclass VALUES (201, 'Orxcle');
Create Check Constraint with NOT LIKE ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> NOT LIKE <condition>);
ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_class_name
CHECK (class_name NOT LIKE '%O%');

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'UCLASS';

INSERT INTO uclass VALUES (101, 'Basic Oracle');
INSERT INTO uclass VALUES (201, 'Oracle SQL');
INSERT INTO uclass VALUES (301, 'oracle SQL');
Create Check Constraint with IN ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name>
IN (<comma delimited list of values>);
ALTER TABLE person DROP CONSTRAINT cc_person_status;

UPDATE person
SET status = NULL;

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status IN ('N', 'Y'));

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'Y'
WHERE person_id = 1;

UPDATE person
SET status = 'N'
WHERE person_id = 1;

UPDATE person
SET status = 'y'
WHERE person_id = 1;
Create Check Constraint with NOT IN ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name>
NOT IN (<comma delimited list of values>);
ALTER TABLE person DROP CONSTRAINT cc_person_status;

UPDATE person
SET status = NULL;

ALTER TABLE person
ADD CONSTRAINT cc_person_status
CHECK (status NOT IN ('A','B','C','D'));

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET status = 'A'
WHERE person_id = 1;

UPDATE person
SET status = 'D'
WHERE person_id = 1;

UPDATE person
SET status = 'E'
WHERE person_id = 1;
Create Check Constraint with BETWEEN ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name> BETWEEN <lower_value> AND <higher_value>);
ALTER TABLE person
ADD CONSTRAINT cc_person_age
CHECK (per_age BETWEEN 18 AND 60);

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET per_age = 57
WHERE person_id = 1;

UPDATE person
SET per_age = 59
WHERE person_id = 2;

UPDATE person
SET per_age = 17
WHERE person_id = 3;
Create Check Constraint with NOT BETWEEN ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name>
NOT BETWEEN <lower_value> AND <higher_value>);
ALTER TABLE person
ADD CONSTRAINT cc_person_age
CHECK (per_age NOT BETWEEN 18 AND 60);

SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'PERSON';

UPDATE person
SET per_age = 54
WHERE person_id = 1;

UPDATE person
SET per_age = 17
WHERE person_id = 3;
Create Check Constraint with Boolean Operator ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
CHECK (<column_name>) > (<condition>);
ALTER TABLE person
ADD CONSTRAINT cc_person_age
CHECK (per_age > 30);

UPDATE person
SET per_age = 57
WHERE person_id = 1;

UPDATE person
SET per_age = 27
WHERE person_id = 3;
Functions Can Be Used In A Check Constraint But Not To Make Assignments ALTER TABLE uclass
ADD CONSTRAINT cc_uclass_reg_fee
CHECK (reg_fee =  ROUND(reg_fee,0));

INSERT INTO uclass
(class_id, class_name, reg_fee)
VALUES
(401, 'Check Constraints', 23.0);

INSERT INTO uclass
(class_id, class_name, reg_fee)
VALUES
(401, 'Check Constraints', 23.1);

-- 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);
 
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';

and I see this:

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK_PERSON_STATE_ZIP            DEFERRABLE     DEFERRED

all is well.

Then I do this:

SET CONSTRAINTS ALL IMMEDIATE;

and if I again query the data dictionary I still see this:

CONSTRAINT_NAME                DEFERRABLE     DEFERRED
------------------------------ -------------- ---------
FK_PERSON_STATE_ZIP            DEFERRABLE     DEFERRED

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:


SO: 0x3dace118, type: 49, owner: 0x3daa4198, flag: -/-/-/0x00 if:
0x1 c: 0x1
proc=0x3f256af0, name=txncallback, file=ktccts.h LINE:336, pg=0
(cmtcbk)
type: constraint commit callback act: 1
Dump of memory from 0x3C434118 to 0x3C4341A8
3C434110 00000301 00000357 [....W...]
3C434120 3DAA4198 3F256AF0 3DAA41C8 3DACE128 [.A.=.j%?.A.=(..=]
3C434130 00000000 00000000 00000000 00000000 [................]
3C434140 00000000 00000000 3C434148 3C434148 [........HAC<HAC<]
3C434150 200010B4 00001024 00000000 37D204FC [... $..........7]
3C434160 37D20504 00000000 00000000 02010200 [...7............]
3C434170 00000000 00001014 6E617274 74636173 [........transact]
3C434180 206E6F69 20006F63 7FFF7FFF 7FFF7FFF [ion co. ........]
3C434190 00000200 00000000 3C434198 3C434198 [.........AC<.AC<]
3C4341A0 37D20514 00000000 [...7....]

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 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;
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 / as sysdba

GRANT select ON ind$ TO uwclass;

conn uwclass/uwclass

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
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

SELECT DECODE(b.table_name, NULL, '****', 'Ok' ) STATUS, a.table_name, a.columns, b.columns FROM (
  SELECT SUBSTR(a.table_name,1,30) table_name,
  SUBSTR(a.constraint_name,1,30) constraint_name,
  MAX(DECODE(position,
  1,       SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  2,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  3,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  4,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  5,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  6,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  7,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  8,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
  9,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 10,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 11,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 12,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 13,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 14,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 15,', '|| SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(position,
 16,', '|| SUBSTR(column_name,1,30),NULL)) columns
  FROM user_cons_columns a, user_constraints b
  WHERE a.constraint_name = b.constraint_name
  AND constraint_type = 'R'
  GROUP BY SUBSTR(a.table_name,1,30), SUBSTR(a.constraint_name,1,30)) a,
    (
  SELECT SUBSTR(table_name,1,30) table_name,
  SUBSTR(index_name,1,30) index_name, MAX(DECODE(column_position,
  1, SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  2,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  3,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  4,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  5,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  6,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  7,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  8,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
  9,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 10,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 11,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 12,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 13,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 14,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 15,', '||SUBSTR(column_name,1,30),NULL)) || MAX(DECODE(column_position,
 16,', '||SUBSTR(column_name,1,30),NULL)) columns
  FROM user_ind_columns
  GROUP BY SUBSTR(table_name,1,30), SUBSTR(index_name,1,30)) b
WHERE a.table_name = b.table_name(+)
AND b.columns(+) LIKE a.columns || '%';
Script to check for valid date constraints {$ORACLE_HOME}/rdbms/admin/utlconst.sql

Related Topics
DBMS_ERRLOG
Nested Table Constraints (REF & DEREF)
Type O Constraints (Read Only on a view)
Type V Constraints (Check option on a view)

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved