Posted in faq's-common doubts

Oracle Constraints – Complete reference for the new Oracle DBA Career

   February 7, 2011





As a new oracle DBA you can find detailed information and examples of Oracle Constraints in the below link

http://psoug.org/reference/constraints.html

Oracle Constraints
Version 10.2
 
General Constraint Information
Data Dictionary Objects Related To Constraints con$
ccol$
ind$
icol$

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 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
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, ‘Cline’);

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

COMMIT;
Drop Primary Key
(method 1)		 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’;
Drop Primary Key
(method 2)		 ALTER TABLE <table_name>
DROP CONSTRAINT <primary_key_constraint_name>;
ALTER TABLE uclass
ADD CONSTRAINT pk_uclass
PRIMARY KEY (class_id)
INITIALLY DEFERRED DEFERRABLE
USING INDEX
PCTFREE 0;

ALTER TABLE person DROP CONSTRAINT pk_person;

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

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’;
Drop unique constraint ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>
ALTER TABLE state_zip DROP CONSTRAINT uc_state_zip_state;
Alternative drop unique constraint 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);
 
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;
Disable constraint ALTER TABLE <table_name>
DISABLE CONSTRAINT <constraint_name>;
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’;

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, ‘Cline’, 57, ‘WA’, ‘98005’);

ALTER TABLE person
DISABLE CONSTRAINT fk_person_state_zip;

INSERT INTO person
(person_id, last_name, per_age, per_state, per_zip)
VALUES
(2, ‘Cline’, 57, ‘WA’, ‘98005’);

COMMIT;
Enable constraint with EXCEPTIONS INTO clause ALTER TABLE <table_name>
ENABLE CONSTRAINT <constraint_name>;
SELECT constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = ‘PERSON’;

ALTER TABLE person
ENABLE CONSTRAINT fk_person_state_zip;

SELECT constraint_name, status
FROM user_constraints
WHERE table_name = ‘PERSON’
AND constraint_type = ‘R’;

@?\rdbms\admin\utlexcpt.sql

desc exceptions

ALTER TABLE person
ENABLE CONSTRAINT fk_person_state_zip
EXCEPTIONS INTO exceptions;

SELECT * FROM exceptions;

COMMIT;

SELECT * FROM person;
Drop foreign key ALTER TABLE <table_name>
DROP CONSTRAINT <constraint_name>;
ALTER TABLE person
DROP CONSTRAINT fk_person_state_zip;
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, ‘Cline’, 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;
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;
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;
 
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 (here)
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 VALUES (401, ‘Check Constraints’, 23.0);
INSERT INTO uclass VALUES (401, ‘Check Constraints’, 23.1);

— SYSDATE 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
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.
 
 
Rename Constraint ALTER TABLE <table_name>
RENAME CONSTRAINT <current_constraint_name>
TO <new_constraint_name>;
SELECT constraint_name
FROM user_constraints;

ALTER TABLE person
RENAME CONSTRAINT pk_person
TO person_pk;

SELECT constraint_name
FROM user_constraints;
 
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

 
 
Contact Us ? Legal Notices and Terms of Use ? Privacy Statement

 

select a.owner,a.constraint_name,a.constraint_type,a.table_name,

           b.owner,b.constraint_name,b.constraint_type,b.table_name

            from dba_constraints a , dba_constraints b where a.constraint_type = ‘R’  and a.constraint_name = ‘FK_UDNS_USNS’

     and a.r_constraint_name = b.constraint_name

Author: admin