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;