How to Define Primary Key Constraint
Whenever we talk about database constraint the first thing which comes into our mind is the primary key constraint. So today in this tutorial we will see what is a Primary key constraint and the different ways of defining it on a table.
Definition
Primary key constraint is an Input/output Data constraint which serves the purpose of uniquely identifying the rows in a table.
What is a Primary Key Constraint?
Primary key constraint is the combination of NOT NULL and UNIQUE constraints. In a more proper manner you can define a primary key constraint as an Input/output Data constraint which serves the purpose of uniquely identifying the rows in a table.
Types of Primary Keys
There are two types of Primary keys:
- Simple Primary Key and
- Composite Primary Key.
Simple Primary Key
A primary key constraint that is defined using only one column of a table in the database is called Simple Primary Key Constraint.
Composite Primary Key
A primary key constraint that is defined using more than one column of a table in the database is called Composite Primary key constraint.
Primary key has a single function of identifying a unique row in the table. In case the simple primary key fails to identify that unique row, the user must then define a composite primary key. There are some restrictions on composite primary key, for example a composite primary key can be defined using up to 32 columns of a table in oracle database.
Quick Tip
How to ensure the uniqueness of a primary key constraint?
Oracle automatically creates a unique index so that the requirement of the uniqueness of the PRIMARY KEY constraint is fulfilled.
How to Create a Primary Key Constraint in Oracle Database
Primary key constraint can be defined in two ways:
- Using CREATE TABLE statement and
- Using ALTER TABLE statement
You can define a primary key constraint either during the creation of a table using CREATE TABLE statement or after creating a table using ALTER TABLE statement.
If you choose to define a primary key using CREATE TABLE statement then you have two different levels at which you have to define a primary key.
- At Column Level
- At Table Level.
Let’s learn all about the above mentioned ways of defining a Primary Key constraint one by one.
Defining Primary Key Using CREATE TABLE
As mentioned above we can define a primary key in two ways using create table. Let’s start with the first way which is Defining Primary key at Column Level.
Define Primary key at Column Level
In the create table statement you define any column as a primary key column just by putting reserved phrase “Primary key” right after defining the column. This means you have to put reserved phrase “Primary Key” after data type and size in column definition. For example
Example 1: Primary Key at Column Level
CREATE TABLE product_master
(
Product_id NUMBER(3) PRIMARY KEY,
Product_name VARCHAR2(30),
Product_price NUMBER(5)
);
As you can see in the above code the column product_id is a primary key column. But it is always a good practice to give a unique and meaningful name to your primary key constraint every time you create it. This will make the managing of your constraint much easier.
How to Name Your Constraint in Oracle Database?
In case you do not provide a meaningful name to your constraint then the oracle database server gives it a default name automatically. If you have several constraints on your table then using this default name makes it very difficult to find a specific constraint.
You can use keyword “CONSTRAINT” to give your constraint a meaningful name. Let’s modify the above code and give our constraint a name.
Example 2: How to name a primary key constraint
CREATE TABLE product_master
(
Product_id NUMBER(3) CONSTRAINT promstr_col1_pid_pk PRIMARY KEY,
Product_name VARCHAR2(30),
Product_price NUMBER(5)
);
That is how we define a primary key constraint at column level using Create Table Statement in Oracle database. Now let’s see the second way of creating a primary key.
Define Primary key at Table Level
Defining the primary key constraint at table level is one of my favorite ways as it helps me to manage all my constraints specially when dealing with a huge line of code.
Defining a constraint at table level separates the column definition from the constraint definition. In this way you first define all the columns of a table and then you define all your constraints in the Create Table Statement. For Example
Example 3: Primary Key Constraint at Table Level
Let’s again take the above example and see how we can define the primary key constraint promstr_col1_pid_pk at table level:
CREATE TABLE product_master
(
Product_id NUMBER(3),
Product_name VARCHAR2(30),
Product_price NUMBER(5),
CONSTRAINT promstr_col1_pid_pk PRIMARY KEY (product_id)
);
Please watch the video tutorial on the same topic for line by line explanation of the above code.
How to Define Primary Key Using ALTER TABLE statement?
You can use ALTER TABLE statement to add the constraint in an already created table or to change the definition of already defined constraint in the table.
Example 4:
Let’s say we have a table “Customer” with 3 columns Cust_id, cust_name, phone_no and we don’t have any Primary Key constraint on any column and now we want to add Primary Key constraint on cust_id column. To do this we can use ALTER TABLE statement:
ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id);
That is how we use ALTER TABLE statement to add a primary key constraint in an already created table.
How to Define a Composite Primary Key Constraint?
As said above that the primary key defined using more than one column is called a composite primary key. Let’s see how to define a composite primary key.
Example 5: Composite Primary Key
CREATE TABLE customer
(
cust_id NUMBER(3),
cust_name VARCHAR2(3),
phone_no NUMBER(10),
CONSTRAINT cust_cid_pk PRIMARY KEY ( cust_id, phone_no)
);
In the above code we create a table with the name ‘Customer’ which has 3 columns. The primary key constraint is defined using two columns cust_id and phone_no.
Example 6: Composite Primary Key Using ALTER TABLE
Similar to simple primary key you can add a composite primary key to an already created table using ALTER TABLE statement.
ALTER TABLE customer ADD CONSTRAINT cust_cid_pk PRIMARY KEY (cust_id,phone_number);
How To Enable and Disable a Primary Key Constraint?
If you were wondering what is the requirement of giving a name to a constraint then here is the answer. Modifying a constraint becomes easier if your constraint has a unique and meaningful name. If you do not provide a name to your constraint then oracle server gives a default name to it which is quite generic and it becomes difficult to find a specific constraint using that name.
So let’s modify the primary key constraint and see how to enable or disable it using the name of the constraint.
Example 7: Enable or Disable primary key
For example let’s say you want to disable cust_cid_pk constraint which we earlier defined on the Customer table.
Disable Primary key constraint
ALTER TABLE customer DISABLE CONSTRAINT cust_cid_pk;
If you want to enable this constraint then simply write
Enable Primary key constraint
ALTER TABLE customer ENABLE CONSTRAINT cust_cid_pk;
How to Check Constraint on a Table
As we know that DESC statement shows nothing about constraints on a table. But Oracle Database provides us several DATA DICTIONARIES for checking or describing all the constraints which we have defined on our table.
These Data Dictionaries are:
- USER_CONSTRAINTS
- USER_CONS_COLUMNS
Here USER_CONSTRAINTS gives a brief about the constraint on a table. USER_CONS_COLUMNS is a Data Dictionary which holds detailed information about columns of a table. Let’s see how to use them to get information about the primary key constraint.
For example let’s say you want to see constraint details on customer table
USER_CONSTRAINTS
SELECT
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME,
STATUS,
INDEX_NAME
FROM user_CONSTRAINTS WHERE table_name = ‘CUSTOMER’;
USER_CONS_COLUMNS
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
FROM user_cons_columns WHERE table_name = ‘CUSTOMER’;
Restrictions on a primary key constraint
- You cannot delete a Primary key if it is referenced by a foreign key in some other table.
- A table can have only One Primary key no matter whether it’s Simple Primary Key Or Composite Primary Key.
- Columns which are participating in Primary Key cannot have NULL values. This means you cannot leave them unattended or you cannot put NULL value into them.
- As primary key is all about row or record’s uniqueness thus it will not allow duplicate values.
- When a Primary Key constraint has been defined on multiple columns then its values can be duplicated provided the duplication is happening within one single column. However the combination of values of all the columns defining each primary key constraint should be unique.
- Data-types such as LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, BFILE, REF, TIMESTAMP WITH TIME ZONE, or user-defined type are not allowed with the columns which are part of Primary key. Any attempt of creating a primary key with the column of these data-types will raise SQL Error: ORA-02269.
- The size of the primary key cannot exceed approximately one database block.
- As I have already mentioned above that a composite primary key can have maximum of 32 columns.
- The Primary Key and Unique Key should never be designated as the same column or combination of columns.
- You cannot specify a primary key when creating a sub view in an inheritance hierarchy. The primary key can be specified only for the top-level (root) view.
- Unique cluster Index gets created automatically at the time of creating Primary key.
- Although it is not necessary for you to define a primary key yet it is always recommended to do so.
So that’s all about PRIMARY KEY. Hope it gives you a detailed insight into the concept. You can visit my YouTube channel for tutorials on Primary Key. Do like and Subscribe to my videos. Thanks for reading & have a great day!
