List all the Primary Keys in Oracle Database

The below script will give you list of Primary Keys in a database and columns which are part of the primary key definition.

If you don't know how to add a primary key on a table then you need to check out the "Adding Primary Key on a Table" first.


SELECT CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'DEPT' AND CONSTRAINT_TYPE = 'P'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

If you do not want to limit your result set to a table then remove the TABLE_NAME condition.

Please let me know if you have a query which is much simple than this.....

Add Primary Key in Oracle

One of the cirtical factor in database/table designing is to prevent duplicate records. There are several ways to do this. You can place Unique Index or Unique Constraint or add a primary key. Well there is only slight difference between these things.

I will go one by one to describe about these but for now lets concentrate on adding a primary key to a table.

There are two ways to add a primary key constraint:
1) While Creating the Table using CREATE TABLE command.
2) Adding Primary key after table creation using ALTER TABLE statement.

The simple thing is to create the table first and define the Primary key later. Again each one of us will have different opinion but to me this is simple step. Though you will end up in having multiple SQL statements but you will have more control on what you are doing.

The pre-requisite to define a Primary Key on a column is, that column should be NOT Nullable.Check out Adding NOT NULL constraint article to meet the pre-requisite of Primary key.

To verify whether the column is NOT NULL or not, run the below query:

SELECT column_name,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'

If NULLABLE is "N" means column has Not Null Constraint.

Lets say I want to add Primary Key constraint on DEPTNO Column.

ALTER TABLE:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

We are trying to add a Primary Key Constraint on DEPT table and the column is DEPTNO.The Name of the Constraint is PK_DEPT. Its very important to give a name to the constraint.As the general standards Primary key constraint name should always start with PK_ and followed by the table Name Or you can have _PK.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO NUMBER PRIMARY KEY, DNAME VARCHAR2(30))

Add NOT NULL Constraint in Oracle

Today I will explain about the different ways to add a NOT NULL constraint on a column.

Advantages of NOT NULL constraint:
1) You can prevent users to enter null values into this column.
2) The pre-requisite to enforce Primary Key on a Column.

Now Lets get into the details:
There are two ways which i can think of
1) While creating the table using CREATE TABLE statement.
2) Modifiying the table definition using ALTER TABLE statement.

CREATE TABLE:
CREATE TABLE DEPT(DEPTNO NUMBER NOT NULL, DNAME VARCHAR2(30))

When you are creating a table as part the Column Definition you can specify the Constraint. This is very easy. But most of the cases we get into a position where we have a table in PROD or some where and want to define a Not Null constraint on the column.

ALTER TABLE:

Created a table called Dept to store Department information.

CREATE TABLE DEPT(DEPTNO NUMBER, DNAME VARCHAR2(30));

ALTER TABLE DEPT MODIFY DEPTNO NOT NULL;

That's it. Now if you use any of following queries you can see the column is now not nullable.

SELECT column_name,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'

or

DESC DEPT