«Back to Home

Oracle Jump Start
Topics

How To Use Primary Key In Oracle

Primary key in oracle
 
In Oracle, a primary key is a single field or a combination of fields. Primary key in a relational database is unique record for all the all tables. None of the fields, which are part of the primary key can contain a null value. There can be only one primary key in a table.
 
A primary key cannot contain more than 32 columns.
 
A primary key can be defined in either a create table statement and alter table statement.
Create table statement using Primary key
 
You can create a primary key in create table statement.
 
Syntax
  1. Create table table_name  
  2. (  
  3. Column 1 datatype(),  
  4. Column 2 datatype(),  
  5. . .  
  6. . .  
  7. . .  
  8. Column n datatype()  
  9. Constraint constraint_name primary key(column 1, column 2…..column n)  
  10. );  
  11. Commit;  
Example
 
Look at an example, we can create a primary key in create table statement.
  1. Create table customer  
  2. (  
  3. Customer_id number(20),  
  4. Customer_name varchar2(15),  
  5. Customer_address varchar2(100),  
  6. Customer_mob number(10),  
  7. Constraint pk_customer_id primary key (customer_id)  
  8. );  
  9. Commit;  
1 
 
Drop Primary key
 
You can drop a primary key, using an alter table statement.
 
Syntax
 
Drop a primary key, using an alter table statement.
  1. alter table table_name  
  2. Drop constraint constraint_name;  
Example
  1. Alter table customer  
  2. Drop constraint pk_customer_id;  
  3. Commit;  
2

Disable Primary Key
 
You can disable the primary key, using an alter table statement.
 
Syntax
 
Disable a primary key, using an alter table statement.
  1. Alter table table_name  
  2. Disable constraint constraint_name;  
Example
  1. Alter table customer  
  2. Disable constraint pk_customer_id;  
  3. Commit;  
3

Enable a primary key
 
You can enable a primary key, using an alter table statement.
 
Syntax
  1. Alter table table_name  
  2. Enable constraint constraint_name;  
Example
  1. Alter table customer  
  2. Enable constraint pk_customer_id;  
  3. Commit;  
4

Summary
 
In this article, you learnt what primary key is and how to use it.