Data Integrity is one of the important dimension to be maintained in database. The article explains the implementation and use of Primary key constraint in Oracle.
Primary key is one of the data integrity constraints which technically combine the UNIQUE and NOT NULL constraints. It does not allow the column value to be NULL or get duplicated.
1. A table can have only one primary key.
2. Multiple columns can be clubbed under composite primary key.
3. Oracle internally creates a unique index to prevent duplication in the column values.
COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]
CONSTRAINT [constraint name] PRIMARY KEY [column (s)]
4.1. The following example shows how to use PRIMARY KEY constraint at column level.
CREATE TABLE TEST ( ID NUMBER CONSTRAINT TEST_PK PRIMARY KEY, ... );
4.2. The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.
CREATE TABLE TEST ( ..., CONSTRAINT TEST_PK PRIMARY KEY (ID) );
Constraints on a table can be queried from the dictionary view [ALL | DBA | USER]_CONSTRAINTS and USER_CONS_COLUMNS.
Below query lists the constraints contained by the table, their associated indexes (if any) and status (ENABLED or DISABLED).
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME , STATUS FROM USER_CONSTRAINTS WHERE TABLE_NAME = '<TABLE_NAME>';
1. Primary Key can be added/disabled/enabled using ALTER TABLE command. Single r Column or multiple columns can be specified in the syntax. In this case, constraint name is generated by the Oracle server in 'SYS_*' form.
Below ALTER command adds the primary key to a table
ALTER TABLE [TABLE NAME] ADD PRIMARY KEY(COLUMN NAME)
Below ALTER command syntax enables/disables the primary key of a table
ALTER TABLE [TABLE NAME] [ENABLE | DISABLE] PRIMARY KEY
Disabling the primary key does the below changes
• Primary key constraint is ineffective
• Oracle created unique Index is dropped
• Status is DISABLED in USER_CONSTRAINTS dictionary view
2. Primary key can be dropped using ALTER TABLE command. It drops the constraint and associated index.
ALTER TABLE DROP RPIMARY KEY
An index can be associated to a primary key using ‘USING INDEX’ clause in the ALTER table.
ALTER TABLE [TABLE NAME] ADD CONSTRAINT [CONSTRAINT NAME] PRIMARY KEY(col_1) USING INDEX [INDEX AME];
An Index can be re-associated to an existing Primary key by disabling it and associating an index while enabling it.
ALTER TABLE [TABLE NAME] ENABLE CONSTRAINT [CONSTRAINT NAME] USING INDEX [INDEX NAME];
A non unique index can also be associated with a PK. Bitmap indexes cannot be associated with PK.
If a Primary key is dropped, explicitly associated indexes are not dropped. However, if the index is implicitly created by the oracle server during the creation of Primary Key constraint, it would be dropped during dropping and disabling of primary key. It can be retained using KEEP INDEX clause.