LLI Wiki — My Digital Knowledge Base

Oracle primary key syntax

Create, verify, drop, enable and disable an Oracle primary key, single or composite.

Oracle primary key syntax with its main variants.

Primary key at table creation

CREATE TABLE emp_tab
( emp_id number(10) NOT NULL,
  emp_name varchar2(50) NOT NULL,
  address varchar2(50),
  CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);

Check:

SELECT table_name, constraint_name, constraint_type, status
FROM dba_constraints
WHERE table_name = 'EMP_TAB';
TABLE_NAME CONSTRAINT_NAME C STATUS
EMP_TAB EMP_ID_PK P ENABLED
EMP_TAB SYS_C002840 C ENABLED
EMP_TAB SYS_C002839 C ENABLED

You can also query user_constraints. There are three constraints here: the primary key and two NOT NULL constraints set on two columns. The third column of the output is the constraint type: P = primary key, C = check constraint (NOT NULL is a check constraint).

Composite primary key

CREATE TABLE emp_tab
( emp_id number(10) NOT NULL,
  emp_name varchar2(50) NOT NULL,
  address varchar2(50),
  CONSTRAINT emp_id_pk PRIMARY KEY (emp_id, emp_name)
);

With ALTER TABLE

ALTER TABLE emp_tab
ADD CONSTRAINT emp_id_pk PRIMARY KEY (emp_id);

Drop the primary key

ALTER TABLE emp_tab DROP CONSTRAINT emp_id_pk;

Enable the primary key

ALTER TABLE emp_tab ENABLE CONSTRAINT emp_id_pk;

Disable the primary key

ALTER TABLE emp_tab DISABLE CONSTRAINT emp_id_pk;
Mastodon LLIMastodon SocialFosstodon