Oracle:Primary key syntax

let us check the syntax for primary key with various options:

a) primary key with create table:

sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);

to verify:

sys@LION> select table_name,constraint_name,constraint_type,status from dba_constraints where table_name='ERSH_TAB';

TABLE_NAME CONSTRAINT_NAME C STATUS
------------- ----------------- ------ --------
ERSH_TAB EMP_ID_PK P ENABLED
ERSH_TAB SYS_C002840 C ENABLED
ERSH_TAB SYS_C002839 C ENABLED

Note: you can query user_constraints also for the output here.
here , you can see three constraints present out of that last two are not null constraints as two of my columns , i have set not null constraints . and the first one is the primary key constraint.
the third column of the output shows the same .
P= primary key
C=check constraints(not null is known as check constraints)

b) Composite primary key with create table:
sys@LION>CREATE TABLE ersh_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)
);

c) Primary key with alter table statement:

sys@LION>ALTER TABLE ersh_tab
add CONSTRAINT emp_id_pk PRIMARY KEY (emp_id);

d) how to drop a primary key constraint:

sys@lion>alter table ersh_tab drop constraint emp_id_pk;


e) How to enable a primary key:

sys@lion> ALTER TABLE ersh_tab ENABLE CONSTRAINT emp_id_pk;

f) How to disable a primary key:

sys@lion> ALTER TABLE ersh_tab DISABLE CONSTRAINT emp_id_pk;

Thanks,
have a nice day...........

Try not to become a man of success but a man of value.
Albert Einstein