PRIMARY KEY Constraint

Ø  A Primary key constraint designates a column as the primary key of a table or view.
Ø  A composite primary key designates a combination of columns as the PRIMARY KEY.
Ø  When the constraint is declared at column level only PRIMARY KEY keyword is enough.
Ø  A composite PRIMARY KEY is always defined at table level only.
Ø  A PRIMARY KEY constraint combines a NOT NULL and UNIQUE constraint in one declaration.
Restrictions:
Ø  A Table or View can have only one Primary key.
Ø  Primary key cannot be implemented on columns having
Ø  LOB, LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, BFILE, REF, TIMESTAMP WITH TIME ZONE.
Ø  A composite primary key cannot have more than 32 columns.
Ø  Same column or combination of columns cannot be designated as both primary key and UNIQUE key.
Ø  Primary key cannot be specified when creating a sub table or sub view in an inheritance hierarchy.
Ø  The Primary key can be specified only for the top level (root) table or view.
Syntax:
·         Create Table <Table_Name>
                        (
                                    Column_Name1 <data type> (width)
                                    CONSTRAINT ColNamePK PRIMARY KEY,
                                    Column_Name2 <data type> (width),
                                    Column_NameN <data type> (width)
                        );
Illustration: 1
Column Level Syntax:
·         Create Table Locations
                        (
                                    LocationID Number(4)
                                    Constraint LocIDPK Primary key,
                                    StAddress varchar2(40) NOT NULL,
                                    PostalCode varchar2(6)
                                    Constraint PCNN NOT NULL,
                                    City varchar2(30)
                                    Constraint cityNN NOT NULL
                        );
Illustration: 2
Table Level Syntax:
·         Create Table Locations
                        (
                                    LocationID Number(4),
                                    StAddress varchar2(40) NOT NULL,
                                    PostalCode varchar2(6)
                                    Constraint PCNN NOT NULL,
                                    City varchar2(30)
                                    Constraint cityNN NOT NULL
                                    Constraint LocIdPK primary key(LocationID)
                        );
Illustration: 2
·         Create Table SalesInfo
                        (
                                    SaleID Number(6),
                                    CustID Number(6),
                                    ProdID Number(6),
                                    Quantity Number(6) NOT NULL,
                                    SaleDate Date NOT NULL,
                                    SaleDesc Long NOT NULL,
                                    Constraint ProdCustIDPK
                                    Primary key(SaleID,ProdID,CustID)

                        );