Oracle Database - Primary Key

> Database > Oracle Database

1 - About

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

A table may have only one primary key.

Advertising

3 - Composite

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

4 - Management

4.1 - 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; 

4.2 - 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;