Oracle Database - Primary Key

Card Puncher Data Processing

About

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

A table may have only one primary key.

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;





Discover More
Card Puncher Data Processing
Oracle Database - (Integrity) Constraints

Constraints on tables. Violation of constraint is not a syntax error but a run-time error. See A UNIQUE key integrity constraint requires that every value in a column or set of columns (key)...
Card Puncher Data Processing
Oracle Database - FOREIGN KEY Constraints

A foreign key is a referential constraint between two tables. The foreign key identifies: a column or a set of columns in one (Child/dependent) table that refers to set of columns in another...



Share this page:
Follow us:
Task Runner