Background documentationPrimary key Locate this document in the navigation structure

 

For some applications, it is useful to uniquely identify the rows in a table by one or more columns so that you can process them in a specified sequence, for example. You can do this by assigning a primary key. The column names that are to create the table key are represented by the keywords PRIMARY KEY. The input values of the key columns defined in this way must not be a NULL value.

You can use the primary key to insert rows in a table in the same way as you insert rows in a base table when no primary key is defined. However, the system outputs an error message if you attempt to insert an existing value into the primary key column a second time, since the uniqueness of the column is ensured by defining the primary key.

A primary key can consist of multiple columns. However, it is unusual for a key to be constructed from more than five columns, since this makes it difficult for users to enter unique values. The arrangement of the columns behind the keywords PRIMARY KEY defines the key sequence.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Primary Key

You can specify a primary key when you define the table or add it to an existing table at a later stage.

Simple Primary Key

You can define primary keys that refer to one table column only.

You can use the CREATE TABLE statement to define primary keys when you create a table.

CREATE TABLE hotel.person

(pno FIXED(6) PRIMARY KEY,

 name CHAR(20),

 city CHAR(20))

or

CREATE TABLE hotel.person

(pno FIXED(6),

 name CHAR(20),

 city CHAR(20),

PRIMARY KEY (pno))

Both statements allows to uniquely identify the rows in the person table on the basis of the person number pno.

See also: SQL Reference Manual, CREATE TABLE Statement (create_table_statement)

You can use the ALTER TABLE statement to add primary keys to existing tables.

  1. Delete the person table.

    DROP TABLE hotel.person

  2. Define the person table as illustrated below:

    CREATE TABLE hotel.person

    (pno FIXED(6),

    name CHAR(20),

     city CHAR(20))

  3. Add a primary key:

    ALTER TABLE hotel.person ADD PRIMARY KEY (pno)

It must be possible to uniquely identify the rows in the person table on the basis of the person number.

Primary Key in Several Columns

You can define primary keys across several columns of a table.

You can use the ALTER TABLE statement to add primary keys to existing tables.

  1. Delete the person table.

    DROP TABLE hotel.person

  2. Define the person table as illustrated below:

    CREATE TABLE hotel.person

    (pno FIXED(6),

    name CHAR(20),

     city CHAR(20))

  3. Add a primary key:

    ALTER TABLE hotel.person ADD PRIMARY KEY (pno,name)

It must be possible to uniquely identify the rows in the person table on the basis of the person number and name.

See also: SQL Reference Manual, ALTER TABLE Statement (alter_table_statement), ADD Definition (add_definition)

Changing a Primary Key

You can use the ALTER TABLE statement to change existing primary keys.

ALTER TABLE hotel.person ALTER PRIMARY KEY (pno)

It must be possible to uniquely identify the rows in the person table on the basis of the person number.

See also: SQL Reference Manual, ALTER Definition (alter_definition)

Deleting a Primary Key

You can use the ALTER TABLE statement to delete primary keys.

ALTER TABLE hotel.person DROP PRIMARY KEY

See also: SQL Reference Manual, DROP Definition (drop_definition)

More Information

More examples for Data Definition