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:
- NULL-able
- More than one column can have the same data type
- Has to take CHECK() constraints
- Appropriate computations can done on it (numeric, string or temporal)
IDENTITY has none of the properties of a data type because it is not a data type at all. It is an exposed physical locator attached to a table, not a property of a column. It is derived from the physical storage used on one machine, like pointer chains in the old navigational DBs or row_ids or hash tables. What does this mean in your Logical data model? Since it has to reference something in the reality of that data model to be a valid RDBMS, how do you validate and verify it? I would guess that you do none of these basic things, but are mimicking a sequential tape file application which depends on counting records in procedural code. Do you have cursors, too? The whole idea of SQL is to use sets and declarative code. This is probably just the tip of the iceberg and all you will have is more and more kludges piled on each other. The thing will run for awhile, but it will choke from lack of data integrity or the inability to scale up or to port to another platform. Fix the design, then fix the application. ~ Joe Celko in comp.databases.ms-sqlserver 11/25/2007 | TABLESPACE | The tablespace where any associated index will be built | VALIDATE | Validate previously existing data as well as all changes | | Tables 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 < 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, 'Cline', 57, 'WA', '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, 'Cline', 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, 'Cline', 57, 'WA', '98005');
INSERT INTO person (person_id, last_name, per_age, per_state, per_zip) VALUES (2, 'Cline', 57, 'OR', '98005');
INSERT INTO person (person_id, last_name, per_age, per_state, per_zip) VALUES (2, 'Cline', 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, '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; | 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 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:damorgan@u.washington.edu] 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. | | 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; | | 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 || '%'; | |