In the first post, we will revisit Primary Keys. All examples shown are validated against Oracle Database 12c Enterprise Edition Release 12.1.0.1.0.
A Primary Key is a column in a table or a view whose values uniquely identify rows in that table or view. If a combination of columns is used to uniquely identify rows in a table or a view, then, it is called a Composite Primary Key. It is implemented by means of a Primary Key Constraint. A Primary Key Constraint combines a NOT NULL constraint and a unique constraint at the same time. A Primary Key Constraint prevents the Primary Key column or Composite Primary Key columns from containing NUll values and also prevents multiple rows from having the same value in Primary Key column and having the same combination of values in the case of Composite Primary Key columns
There are some restrictions around Primary Key Constraint:
1) Column or columns constituting the Primary Key cannot contain NULL values. If we try to insert NULL values into such columns, we get the following error:
ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE"."COLUMN")
2) A Composite Primary Key can consist of a maximum of 32 columns only. If it exceeds 32 columns, you will get below error:
SQL Error: ORA-02257: maximum number of columns exceeded
02257. 00000 - "maximum number of columns exceeded"
*Cause: The number of columns in the key list exceeds the maximum number.
*Action: Reduce the number columns in the list.
3) A table or a view cannot have more than one primary key. You will get the below error in case creation of a table with two primary keys is attempted:
SQL Error: ORA-02260: table can have only one primary key
02260. 00000 - "table can have only one primary key"
*Cause: Self-evident.
*Action: Remove the extra primary key.
4) Columns based on data types as BLOB, CLOB, NCLOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type cannot be part of Primary Key
The following error is thrown in case we try to create a table with BLOB data type as a primary key:
SQL Error: ORA-02329: column of datatype LOB cannot be unique or a primary key
02329. 00000 - "column of datatype %s cannot be unique or a primary key"
*Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
on a column of datatype VARRAY, nested table, object, LOB, FILE
or REF.
*Action: Change the column datatype or remove the constraint. Then retry
the operation.
Likewise, you will get the following error in case a LONG column is used:
SQL Error: ORA-02269: key column cannot be of LONG datatype
02269. 00000 - "key column cannot be of LONG datatype"
*Cause: Self-evident.
*Action: Change the datatype of the column,
or remove the column from the key.
5) The same column or combination of columns cannot be used for both a primary key and a unique key. This is only to be expected as a primary key already covers for the unique key as well the only difference being that while unique key columns can have NULL values, primary key columns cannot
Like other constraints (barring NOT NULL), a Primary Key Constraint can be defined syntactically in two ways:
1) Inline specification: When the constraint is defined as part of the definition of an individual column, it is called inline specification
2) Out of Line specification: When the constraint is defined as part of the table definition, it is called Out of Line specification
A Primary Key Constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement
In the first example, we create a Primary Key Constraint on a table with inline specification using the CREATE TABLE statement:
create table employees
(employee_number number(6,0) primary key,
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2)
);
This creates a record in the user_constraints table that can be verified by running below query:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
We can see that Oracle has given a default name for the Primary Key Constraint and also associated an index with this Primary Key Constraint. The details of the index can be seen using below query:
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
We can also name the Primary Key Constraint as EMP_NUMBER_PK if we replace the line
employee_number number(6,0) primary key
with
employee_number number(6,0) constraint emp_number_pk primary key
in the above CREATE TABLE statement
In the second example, we create a Composite Primary Key called EMPLOYEE_PK on a view based on the created table called EMPLOYEES with out of line specification using the CREATE VIEW statement:
create table employees
(employee_number number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2),
constraint employee_pk primary key (employee_number,first_name,last_name)
);
To get more details about the created constraint, index and columns used in the constraint, the following queries can be used:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
select constraint_name, column_name, position from user_cons_columns where constraint_name = 'EMPLOYEE_PK';
The position column in the result of the last query indicates the order of the columns that was used to create the Primary Key Constraint
In the third example, we create a table with no primary key, and then, add a Primary Key Constraint using ALTER command as shown below:
create table employees
(employee_number number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2)
);
Running below query returns no results:
select constraint_name, constraint_type, index_name, status from user_constraints where table_name = 'EMPLOYEES';
The ALTER command is as follows:
alter table employees add constraint employee_pk primary key (employee_number, first_name, last_name);
Then, the below queries can be used to check for the details of the Primary Key Constraint. They will give the same results as seen in second example:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
select constraint_name, column_name, position from user_cons_columns where constraint_name = 'EMPLOYEE_PK';
It is also possible to create a Primary Key Constraint with the deferrable initially deferred clause, disable it and, then, enable it at a later point in time as shown in fourth example
create table employees
(employee_number number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2),
constraint employee_pk primary key (employee_number,first_name,last_name) deferrable initially deferred
);
The DEFERRABLE INITIALLY DEFERRED clause means that the Primary Key Constraint is not enforced till a commit happens
Running below query shows that the status of the constraint is 'ENABLED'
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
The results of below query suggest that an index is created as before:
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
It can be seen that unlike in the earlier case, this index is nonunique
Now, let us disable the Primary Key Constraint using below ALTER command:
alter table employees disable primary key;
Running below query shows that the status of the constraint is 'DISABLED'
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
We can see that the INDEX_NAME has a null value and that the status of the constraint is 'DISABLED'
Let us add a few records that violate the PRIMARY KEY CONSTRAINT using below insert commands:
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (101,'Michael','Bourne','mikey@mail.com','486534368',to_date('01-JAN-17','DD-MON-RR'),'MANAGER',6567,0.15);
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (101,'Michael','Bourne','mikey@mail.com','486534368',to_date('01-JAN-17','DD-MON-RR'),'MANAGER',6567,0.15);
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (102,'Sarah','Connor','sara@mail.com','746543727',to_date('05-DEC-16','DD-MON-RR'),'VP',9800,0.18);
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (103,'Serena','Tucker','serene@mail.com','723635373',to_date('01-DEC-16','DD-MON-RR'),'CONSULTANT',5645,0.1);
To confirm the records in the table, let us run below select statement:
select employee_number, first_name, last_name, email, hire_date,job_id,salary from employees;
We can see that there are two duplicate records
At a later point in time, we can enable the Primary Key Constraint using the below ALTER command:
alter table employees enable novalidate primary key;
The NOVALIDATE keyword means that existing records will not be validated but new record additions will be subjected to the scrutiny of the Primary Key Constraint
Running below query shows that the status of the constraint is 'ENABLED'
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
Trying to insert a duplicate record will result in below error:
ORA-00001: unique constraint (SCHEMA.PRIMARY_KEY_CONSTRAINT_NAME) violated
ORA-06512: at line 1
Use the script at \dbhome_1\RDBMS\ADMIN\utlexcpt.sql to create an exception table called PRIMARY_KEY_EXCEPTIONS
create table primary_key_exceptions(row_id rowid,
owner varchar2(128),
table_name varchar2(128),
constraint varchar2(128));
Then, run below command that will push all the records violating the Primary Key Constraint into the newly created exception table
alter table employees enable primary key exceptions into primary_key_exceptions;
We see the following error:
ALTER TABLE EMPLOYEES ENABLE PRIMARY KEY EXCEPTIONS INTO PRIMARY_KEY_EXCEPTIONS
Error report:
SQL Error: ORA-02437: cannot validate (SCHEMA.EMPLOYEE_PK) - primary key violated
02437. 00000 - "cannot validate (%s.%s) - primary key violated"
*Cause: attempted to validate a primary key with duplicate values or null
values.
*Action: remove the duplicates and null values before enabling a primary
key.
Querying the exceptions table, PRIMARY_KEY_EXCEPTIONS, we see the erroneous records
select row_id, table_name, constraint from primary_key_exceptions;
Below query can be used to trace the records that violate the Primary Key Constraint in the original table and corrective action can be taken:
select * from employees where rowid in (select row_id from primary_key_exceptions);
Existing Primary Key Constraint can be renamed using the following command:
alter table employees rename constraint employee_pk to emp_pk;
Now, the following query will show the renamed Primary Key Constraint:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
Finally, to drop the Primary Key Constraint, use the following command:
alter table employees drop primary key;
The last statement can be verified by using below query that will return no results:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
However, if the index associated with the Primary Key, needs to be retained, then, we need to use the
alter table EMPLOYEES drop primary key keep index;
The following query shows that the associated index is not dropped
select index_name, index_type, table_name, uniqueness, status from user_indexes WHERE table_name = 'EMPLOYEES';
So, this concludes the post on Primary Keys
What is a Primary Key?
A Primary Key is a column in a table or a view whose values uniquely identify rows in that table or view. If a combination of columns is used to uniquely identify rows in a table or a view, then, it is called a Composite Primary Key. It is implemented by means of a Primary Key Constraint. A Primary Key Constraint combines a NOT NULL constraint and a unique constraint at the same time. A Primary Key Constraint prevents the Primary Key column or Composite Primary Key columns from containing NUll values and also prevents multiple rows from having the same value in Primary Key column and having the same combination of values in the case of Composite Primary Key columns
Restrictions
There are some restrictions around Primary Key Constraint:
1) Column or columns constituting the Primary Key cannot contain NULL values. If we try to insert NULL values into such columns, we get the following error:
ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE"."COLUMN")
2) A Composite Primary Key can consist of a maximum of 32 columns only. If it exceeds 32 columns, you will get below error:
SQL Error: ORA-02257: maximum number of columns exceeded
02257. 00000 - "maximum number of columns exceeded"
*Cause: The number of columns in the key list exceeds the maximum number.
*Action: Reduce the number columns in the list.
3) A table or a view cannot have more than one primary key. You will get the below error in case creation of a table with two primary keys is attempted:
SQL Error: ORA-02260: table can have only one primary key
02260. 00000 - "table can have only one primary key"
*Cause: Self-evident.
*Action: Remove the extra primary key.
4) Columns based on data types as BLOB, CLOB, NCLOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type cannot be part of Primary Key
The following error is thrown in case we try to create a table with BLOB data type as a primary key:
SQL Error: ORA-02329: column of datatype LOB cannot be unique or a primary key
02329. 00000 - "column of datatype %s cannot be unique or a primary key"
*Cause: An attempt was made to place a UNIQUE or a PRIMARY KEY constraint
on a column of datatype VARRAY, nested table, object, LOB, FILE
or REF.
*Action: Change the column datatype or remove the constraint. Then retry
the operation.
Likewise, you will get the following error in case a LONG column is used:
SQL Error: ORA-02269: key column cannot be of LONG datatype
02269. 00000 - "key column cannot be of LONG datatype"
*Cause: Self-evident.
*Action: Change the datatype of the column,
or remove the column from the key.
5) The same column or combination of columns cannot be used for both a primary key and a unique key. This is only to be expected as a primary key already covers for the unique key as well the only difference being that while unique key columns can have NULL values, primary key columns cannot
Define Primary Key Constraint
Like other constraints (barring NOT NULL), a Primary Key Constraint can be defined syntactically in two ways:
1) Inline specification: When the constraint is defined as part of the definition of an individual column, it is called inline specification
2) Out of Line specification: When the constraint is defined as part of the table definition, it is called Out of Line specification
A Primary Key Constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement
In the first example, we create a Primary Key Constraint on a table with inline specification using the CREATE TABLE statement:
create table employees
(employee_number number(6,0) primary key,
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2)
);
This creates a record in the user_constraints table that can be verified by running below query:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
We can see that Oracle has given a default name for the Primary Key Constraint and also associated an index with this Primary Key Constraint. The details of the index can be seen using below query:
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
We can also name the Primary Key Constraint as EMP_NUMBER_PK if we replace the line
employee_number number(6,0) primary key
with
employee_number number(6,0) constraint emp_number_pk primary key
in the above CREATE TABLE statement
In the second example, we create a Composite Primary Key called EMPLOYEE_PK on a view based on the created table called EMPLOYEES with out of line specification using the CREATE VIEW statement:
create table employees
(employee_number number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2),
constraint employee_pk primary key (employee_number,first_name,last_name)
);
To get more details about the created constraint, index and columns used in the constraint, the following queries can be used:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
select constraint_name, column_name, position from user_cons_columns where constraint_name = 'EMPLOYEE_PK';
The position column in the result of the last query indicates the order of the columns that was used to create the Primary Key Constraint
In the third example, we create a table with no primary key, and then, add a Primary Key Constraint using ALTER command as shown below:
create table employees
(employee_number number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2)
);
Running below query returns no results:
select constraint_name, constraint_type, index_name, status from user_constraints where table_name = 'EMPLOYEES';
The ALTER command is as follows:
alter table employees add constraint employee_pk primary key (employee_number, first_name, last_name);
Then, the below queries can be used to check for the details of the Primary Key Constraint. They will give the same results as seen in second example:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
select constraint_name, column_name, position from user_cons_columns where constraint_name = 'EMPLOYEE_PK';
Disabling & Enabling Primary Key Constraint
It is also possible to create a Primary Key Constraint with the deferrable initially deferred clause, disable it and, then, enable it at a later point in time as shown in fourth example
create table employees
(employee_number number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte),
email varchar2(25 byte),
phone_number varchar2(20 byte),
hire_date date,
job_id varchar2(10 byte),
salary number(8,2),
commission_pct number(2,2),
constraint employee_pk primary key (employee_number,first_name,last_name) deferrable initially deferred
);
The DEFERRABLE INITIALLY DEFERRED clause means that the Primary Key Constraint is not enforced till a commit happens
Running below query shows that the status of the constraint is 'ENABLED'
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
The results of below query suggest that an index is created as before:
select index_name, index_type, table_name, uniqueness, status from user_indexes where table_name = 'EMPLOYEES';
It can be seen that unlike in the earlier case, this index is nonunique
Now, let us disable the Primary Key Constraint using below ALTER command:
alter table employees disable primary key;
Running below query shows that the status of the constraint is 'DISABLED'
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
We can see that the INDEX_NAME has a null value and that the status of the constraint is 'DISABLED'
Let us add a few records that violate the PRIMARY KEY CONSTRAINT using below insert commands:
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (101,'Michael','Bourne','mikey@mail.com','486534368',to_date('01-JAN-17','DD-MON-RR'),'MANAGER',6567,0.15);
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (101,'Michael','Bourne','mikey@mail.com','486534368',to_date('01-JAN-17','DD-MON-RR'),'MANAGER',6567,0.15);
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (102,'Sarah','Connor','sara@mail.com','746543727',to_date('05-DEC-16','DD-MON-RR'),'VP',9800,0.18);
insert into employees (employee_number,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct) values (103,'Serena','Tucker','serene@mail.com','723635373',to_date('01-DEC-16','DD-MON-RR'),'CONSULTANT',5645,0.1);
To confirm the records in the table, let us run below select statement:
select employee_number, first_name, last_name, email, hire_date,job_id,salary from employees;
At a later point in time, we can enable the Primary Key Constraint using the below ALTER command:
alter table employees enable novalidate primary key;
The NOVALIDATE keyword means that existing records will not be validated but new record additions will be subjected to the scrutiny of the Primary Key Constraint
Running below query shows that the status of the constraint is 'ENABLED'
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
Trying to insert a duplicate record will result in below error:
ORA-00001: unique constraint (SCHEMA.PRIMARY_KEY_CONSTRAINT_NAME) violated
ORA-06512: at line 1
Use the script at \dbhome_1\RDBMS\ADMIN\utlexcpt.sql to create an exception table called PRIMARY_KEY_EXCEPTIONS
create table primary_key_exceptions(row_id rowid,
owner varchar2(128),
table_name varchar2(128),
constraint varchar2(128));
Then, run below command that will push all the records violating the Primary Key Constraint into the newly created exception table
alter table employees enable primary key exceptions into primary_key_exceptions;
We see the following error:
ALTER TABLE EMPLOYEES ENABLE PRIMARY KEY EXCEPTIONS INTO PRIMARY_KEY_EXCEPTIONS
Error report:
SQL Error: ORA-02437: cannot validate (SCHEMA.EMPLOYEE_PK) - primary key violated
02437. 00000 - "cannot validate (%s.%s) - primary key violated"
*Cause: attempted to validate a primary key with duplicate values or null
values.
*Action: remove the duplicates and null values before enabling a primary
key.
Querying the exceptions table, PRIMARY_KEY_EXCEPTIONS, we see the erroneous records
select row_id, table_name, constraint from primary_key_exceptions;
Below query can be used to trace the records that violate the Primary Key Constraint in the original table and corrective action can be taken:
select * from employees where rowid in (select row_id from primary_key_exceptions);
Renaming Primary Key Constraint
Existing Primary Key Constraint can be renamed using the following command:
alter table employees rename constraint employee_pk to emp_pk;
Now, the following query will show the renamed Primary Key Constraint:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
Dropping Primary Key Constraint
Finally, to drop the Primary Key Constraint, use the following command:
alter table employees drop primary key;
The last statement can be verified by using below query that will return no results:
select constraint_name, constraint_type, index_name,status from user_constraints where table_name = 'EMPLOYEES';
However, if the index associated with the Primary Key, needs to be retained, then, we need to use the
alter table EMPLOYEES drop primary key keep index;
The following query shows that the associated index is not dropped
select index_name, index_type, table_name, uniqueness, status from user_indexes WHERE table_name = 'EMPLOYEES';
So, this concludes the post on Primary Keys
