sql primary key constraint

SQL Advance

homeBack

Next

SQL PRIMARY KEY Constraint

  • SQL UNIQUE Constraint

    The PRIMARY KEY constraint uniquely identifies each record in a database table.

    Primary keys must contain unique values.

    A primary key column cannot contain NULL values.

    Each table should have a primary key, and each table can have only ONE primary key.

  • SQL PRIMARY KEY Constraint on CREATE TABLE

    The following SQL creates a PRIMARY KEY on the “S.no” column when the “emp” table is created:

    MySQL:

    CREATE TABLE emp
    (
    S.no int NOT NULL,
    Name2 varchar(255) NOT NULL,
    Name1 varchar(255),
    City varchar(255),
    PRIMARY KEY (S.no)
    )

    SQL Server / Oracle / MS Access:

    CREATE TABLE emp
    (
    S.no int NOT NULL PRIMARY KEY,
    Name2 varchar(255) NOT NULL,
    Name1 varchar(255),
    City varchar(255)
    )

    To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

    MySQL / SQL Server / Oracle / MS Access:

    CREATE TABLE emp
    (
    S.no int NOT NULL,
    Name2 varchar(255) NOT NULL,
    Name1 varchar(255),
    City varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (S.no,Name2)
    )

    Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonID is made up of two columns (S.no and Name2).

  • SQL PRIMARY KEY Constraint on ALTER TABLE

    To create a PRIMARY KEY constraint on the “S.no” column when the table is already created, use the following SQL:

    MySQL / SQL Server / Oracle / MS Access:

    ALTER TABLE emp
    ADD PRIMARY KEY (S.no)

    To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

    MySQL / SQL Server / Oracle / MS Access:

    ALTER TABLE emp
    ADD CONSTRAINT pk_PersonID PRIMARY KEY (S.no,Name2)

    Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

  • To DROP a PRIMARY KEY Constraint

    To drop a PRIMARY KEY constraint, use the following SQL:

    MySQL:

    ALTER TABLE emp
    DROP PRIMARY KEY

    SQL Server / Oracle / MS Access:

    ALTER TABLE emp
    DROP CONSTRAINT pk_PersonID

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s