SQL Table Primary Key

Create Primary key in a table

  • The purpose of a primary key is to identify the individual rows of a table with a unique key.
  • Columns included in a primary key must be set to NOT NULL.
  • A table can NOT have more than one PRIMARY KEY.
  • There are mainly two ways to specify a primary key, when you create a table.
    1. For a single column to be a primary key you can specify PRIMARY KEY at end of the column specification in nearly all RDBMS.
    2. For multiple columns to be a primary key you must use a CONSTRAINT statement at end of all columns specification.
  • Single column to be a primary key example:
    MySQL, Oracle, SQL server, PostgreSQL:
    CREATE TABLE customer (
      CUSTOMER_ID INT NOT NULL PRIMARY KEY,
      FIRST_NAME VARCHAR(30) NOT NULL,
      LAST_NAME VARCHAR(20) NULL,
      EMAIL VARCHAR(30) NULL
    )
    Alternatively:
    MySQL, SQL server, PostgreSQL (not Oracle):
    CREATE TABLE customer (
      CUSTOMER_ID INT NOT NULL,
      FIRST_NAME VARCHAR(30) NOT NULL,
      LAST_NAME VARCHAR(20) NULL,
      EMAIL VARCHAR(30) NULL,
      PRIMARY KEY (CUSTOMER_ID)
    )

    In this case it will not be possible to register rows where some have equal value in the column CUSTOMER_ID.

    Multiple columns to be a primary key example:
    MySQL, Oracle, SQL server, PostgreSQL:
    CREATE TABLE customer (
      CUSTOMER_ID INT NOT NULL,
      FIRST_NAME VARCHAR(30) NULL,
      LAST_NAME VARCHAR(40) NOT NULL,
      EMAIL VARCHAR(30) NULL,
      CONSTRAINT pk_CUSTOMER PRIMARY KEY (CUSTOMER_ID,LAST_NAME)
    )

    In this case it will not be possible to register rows where some have equal values in the combination of the columns CUSTOMER_ID and LAST_NAME.

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.