SELECT * FROM     ALL_CONS_COLUMNS;
SELECT * FROM     ALL_CONSTRAINTS;
SELECT * FROM     ALL_INDEXES;
SELECT * FROM     ALL_IND_PARTITIONS;
SELECT * FROM     ALL_IND_SUBPARTITIONS;

Oracle/PLSQL: Retrieve primary key information

http://www.techonthenet.com/oracle/questions/find_pkeys.php

Question: How do I determine if a table has a primary key and if it has one, how do I determine what columns are in the primary key?

Answer: You can retrieve primary key information with the following SQL statement:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

If you knew the table name that you were looking for, you could modify the SQL as follows:

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Make sure to type the table_name in uppercase, as Oracle stores all table names in uppercase.

Let’s quickly explain the output from this query.

table_name is the name of the table (stored in uppercase).

column_name is the name of the column that is a part of the primary key. (also stored in uppercase)

position is the position in the primary key. A primary key can contain more than one column, so understanding the order of the columns in the primary key is very important.

status indicates whether the primary key is currently enabled or disabled.

owner indicates the schema that owns the table.

Oracle constraints

Oracle Tips by Burleson Consulting

Several types of Oracle constraints can be applied to Oracle tables to enforce data integrity, including:

• Oracle “Check” Constraint: This constraint validates incoming columns at row insert time. For example, rather than having an application verify that all occurrences of region are North, South, East, or West, an Oracle check constraint can be added to the table definition to ensure the validity of the region column.

• Not Null Constraint: This Oracle constraint is used to specify that a column may never contain a NULL value. This is enforced at SQL insert and update time.

• Primary Key Constraint: This Oracle constraint is used to identify the primary key for a table. This operation requires that the primary columns are unique, and this Oracle constraint will create a unique index on the target primary key.

• References Constraint: This is the foreign key constraint as implemented by Oracle. A references constraint is only applied at SQL insert and delete times. At SQL delete time, the references Oracle constraint can be used to ensure that an employee is not deleted, if rows still exist in the DEPENDENT table.

• Unique Constraint: This Oracle constraint is used to ensure that all column values within a table never contain a duplicate entry.

Oracle constraint views:

Oracle Constraint Standard

Oracle Primary key constraints will follow this naming convention:

PK_nnnnn

Where nnnn = The table name that the index is built on.

UK_nnnnn_nn

Where nnnn = The table name that the index is built on.

nn = A number that makes the constraint unique.

FK_pppp_cccc_nn

Where pppp = The parent table name

cccc = The child parent table name

nn = A number that makes the constraint unique

http://www.dba-oracle.com/concepts/constraints_foreign_key_unique_not_null.htm

Constraints

Type Code
Type Description
Acts On Level
C
Check on a table
Column
O
Read Only on a view
Object
P
Primary Key
Object
R
Referential AKA Foreign Key
Column
U
Unique Key
Column
V
Check Option on a view
Object

SELECT constraint_name, constraint_type, column_name
from user_constraints natural join user_cons_columns
where table_name = 'TABLE';