Search the Reference Library pages:  

Oracle Constraints
Version 11.1
 
General Constraint Information
Data Dictionary Objects Related To Constraints
con$ ind$ icol$
ccol$    
     
DBA ALL USER
dba_cons_columns all_cons_columns user_cons_columns
dba_constraints all_constraints user_constraints
dba_indexes all_indexes user_indexes
dba_ind_partitions all_ind_partitions user_ind_partitions
dba_ind_subpartitions all_ind_subpartitions 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 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
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 For Constraint Demo

Table DDL
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 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 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 <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';

@? dbms\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 [postal_code.zip]

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
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 with Julian Dyke in the UK and valuable enough I thought I should share it here in the library.
From: Daniel A. Morgan [mailto:[email protected]
Sent: 05 November 2007 01:41
To: [email protected]
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.
 
DROP CONSTRAINT

Generic Constraint Drop. Will drop any constraint by name
ALTER TABLE <table_name>
DROP CONSTRAINT <primary_key_constraint_name>;
SELECT constraint_name, table_name
FROM user_constraints;

ALTER TABLE person DROP CONSTRAINT pk_person;

SELECT constraint_name, table_name
FROM user_constraints;

Specific Primary Key Drop
ALTER TABLE <table_name> DROP PRIMARY KEY
ALTER TABLE uclass DROP PRIMARY KEY;

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>);
ALTER TABLE state_zip
ADD CONSTRAINT uc_state_zip_state
UNIQUE (state)
USING INDEX
PCTFREE 0;

ALTER TABLE state_zip DROP UNIQUE (state);

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

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;
 
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 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.
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 || '%';
 
Related Topics
DBMS_ERRLOG
Nested Table Constraints
Type O Constraints
Read Only on a view
Type V Constraints
Check option on a view
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----