Oracle Database - Primary Key

About

In Oracle Database, a Primary Key is a constraint implemented as:

a “not null” constraint
combined with an “Unique” constraint .

A table may have only one primary key.

Articles Related

Composite

A composite primary key is a primary key composed of more than one column.

Management

Declaration

Outline

CREATE TABLE locations_demo
    ( location_id    NUMBER(4) CONSTRAINT loc_id_pk PRIMARY KEY
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city           VARCHAR2(30)
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    ) ;

Inline

CREATE TABLE locations_demo
    ( location_id    NUMBER(4) 
    , street_address VARCHAR2(40)
    , postal_code    VARCHAR2(12)
    , city           VARCHAR2(30)
    , state_province VARCHAR2(25)
    , country_id     CHAR(2)
    , CONSTRAINT loc_id_pk PRIMARY KEY (location_id));

Outline ALTER

ALTER TABLE sales ADD CONSTRAINT sales_pk PRIMARY KEY (prod_id, cust_id) DISABLE;

List

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