Oracle/PLSQL: Primary Keys

Oracle/PLSQL: Primary Keys

Primary Key 是由單一欄位或組合欄位組成，用來定義唯一的一筆記錄
Priamry Key 可以包含空值(null)，但一個表只能有一個Primary Key


Using a CREATE TABLE statement

The syntax for creating a primary key using a CREATE TABLE statement is:

    CREATE TABLE table_name
    (column1 datatype null/not null,
    column2 datatype null/not null,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column1, column2, . column_n)
    );


For example:

    CREATE TABLE supplier
    ( supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

We could also create a primary key with more than one field as in the example below:

    CREATE TABLE supplier
    ( supplier_id numeric(10) not null,
    supplier_name varchar2(50) not null,
    contact_name varchar2(50),
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
    );

Using an ALTER TABLE statement

The syntax for creating a primary key in an ALTER TABLE statement is:

    ALTER TABLE table_name
    add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);


For example:

    ALTER TABLE supplier
    add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);

We could also create a primary key with more than one field as in the example below:

    ALTER TABLE supplier
    add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name);

Drop a Primary Key

The syntax for dropping a primary key is:

    ALTER TABLE table_name
    drop CONSTRAINT constraint_name;


For example:

    ALTER TABLE supplier
    drop CONSTRAINT supplier_pk;

via: Tech on the Net