Validating constraints and oracle
--Two entries, one for NOT NULL constraint and one for PK constraint SELECT a.table_name, b.column_name, a.constraint_name, a.constraint_type, a.index_name FROM user_constraints a, user_cons_columns b WHERE a.table_name = 'TBL_TEST' AND a.constraint_name = b.constraint_name; -- TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME -- TBL_TEST | COL_2 | SYS_C001231845 | C | -- TBL_TEST | COL_1 | TBL_TEST_PK | P | TBL_TEST_PK -- Rechecking the column nullable status.Both the columns are now NOT NULL SELECT table_name, column_name, nullable FROM user_tab_cols WHERE table_name = 'TBL_TEST'; -- TABLE_NAME | COLUMN_NAME | NULLABLE -- TBL_TEST | COL_1 | N -- TBL_TEST | COL_2 | N -- Disabling the PK constraint ALTER TABLE tbl_test DISABLE PRIMARY KEY; -- OR -- ALTER TABLE tbl_test DISABLE CONSTRAINT tbl_test_pk; -- The column status is changed back as it was before adding the PK.So, in the example above, it iss possible to remove the unique index (if not required) and the composite index can be used for PK enforcement. The index associated with the PK constraint needn’t be unique.A non-unique index can also be be associated with the PK constraints.SELECT table_name, column_name, nullable FROM user_tab_cols WHERE table_name = 'TBL_TEST'; -- TABLE_NAME | COLUMN_NAME | NULLABLE -- TBL_TEST | COL_1 | Y -- TBL_TEST | COL_2 | N 3.If the PK constraint is disabled, there will be no index associated with it.If you have an index on that column, the search will be quite fast.The unique index will be the best but a non-unique index will also be a better option as compared to a full table scan.
I have tried to consolidate few aspects about PK constraint which I found particularly confusing / worth knowing. Primary key(PK) constraint and unique index are different.DROP TABLE tbl_test; CREATE TABLE tbl_test ( col_1 NUMBER, col_2 NUMBER, col_3 NUMBER); CREATE INDEX idx_col_1_2 ON tbl_test(col_1, col_2); -- Associating composite index with the PK constraint ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1) USING INDEX idx_col_1_2; SELECT constraint_name, constraint_type, index_name FROM user_constraints WHERE table_name = 'TBL_TEST'; -- CONSTRAINT_NAME | CONSTRAINT_TYPE | INDEX_NAME -- TBL_TEST_PK | P | IDX_COL_1_2 SELECT table_name, index_name, uniqueness FROM user_indexes WHERE table_name = 'TBL_TEST'; -- TABLE_NAME | INDEX_NAME | UNIQUENESS -- TBL_TEST | IDX_COL_1_2 | NONUNIQUE 8.Merits of allowing non-unique index for enforcing PK constraints: a) The non-unique indexes facilitates the use of “INITIALLY DEFERRED” clause with the constraint until the transaction has been committed if the PK constraint has been defined as “DEFERRABLE” at the time of creating.– How PK constraints and indexes are related/different?
– How Oracle is using a non-unique index to enforce PK constraints?If there are more than one indexes on the column on which you want to add PK constraint, we can selectively choose the index to be assoicated with the PK using “USING INDEX“.