6.1 How do I…Create a primary key constraint? Problem

In my data model, I have defined a primary key for all of my tables. I want to create primary keys in the database to enforce uniqueness. How do I create a primary key constraint on a table?

Technique

You can create a primary key constraint with the table as part of a CREATE TABLE statement, or later using the ALTER TABLE statement. Within a CREATE TABLE statement, a PRIMARY KEY clause is used to define the columns contained in the primary key. A named primary key can be created in the CREATE TABLE statement using the CONSTRAINT…PRIMARY KEY clause. Within an ALTER TABLE statement, a primary key can be created with the ADD PRIMARY KEY clause, or a named primary key with the ADD CONSTRAINT…PRIMARY KEY clause. Care must be taken to define NOT NULL constraints for columns of a table that absolutely require values at all times. A UNIQUE constraint ensures that no two rows of a table have duplicate values in a specific column or a set of columns. A UNIQUE NOT NULL constraint will disallow null values as well as duplicates. If the columns that are part of the primary key do not explicitly contain NOT NULL constraints, Oracle will automatically apply a NOT NULL constraint to each column comprising the primary key, and a UNIQUE index is created on the primary key columns. A UNIQUE NOT NULL constraint is inherently implied with a primary key. When defining a column using the CREATE TABLE or ALTER TABLE statements, you can specify a default value for a column by using a DEFAULT keyword.

Steps 1. Run SQL*Plus and connect as the WAITE user account. Run the statement in the CHP6_1.SQL file to create the table and primary key. The file contains a CREATE TABLE statement that creates a table with a primary key. Although it is a good idea to create a primary key for every table, doing so is optional. Figure 6.1 shows the results of running the statement in SQL*Plus.

Line 1 contains the CREATE TABLE keywords used to create a new table. Lines 2 through 6 identify the columns that make up the table. In line 4, although not a column defining the primary key, a NOT NULL constraint on the CUST_NAME column ensures that a value is entered for the CUST_NAME column for every row of the CUST06 table. The UNIQUE keyword contained in line 5 eliminates duplication of phone numbers. You can also explicitly define a DEFAULT value for a column as in line 6. When inserting a row into the CUST06 table, if a value is not given for the LAST_UPD_DT column, a default value of SYSDATE will be used as its column value in the inserted row. Line 7 defines the primary key as a concatenated key containing two columns. Note that line 6 was terminated with a comma.

2. Run the commands in the CHP6_2.SQL file. This file uses an ALTER TABLE statement to create a named constraint. Figure 6.2 shows the use of the ALTER TABLE command in SQL*Plus.

Comments

The columns within a primary key cannot be NULL. Creating a primary key on a table will create NOT NULL constraints on the columns included in the key. A primary key can contain 16 columns at most, and the total size of a key value in bytes should be less than half the block size of the database. The LONG and LONG RAW datatypes cannot be included in a primary key. Only one primary key can be defined for a table, but a table can have multiple unique keys. Declaring primary keys generally improves performance, especially if the primary key consists of a single column and furthermore if the column is of integer datatype.

A unique constraint is implied by the primary key, and Oracle automatically creates a unique index on the columns contained in the primary key. With Oracle8, you can also use non-unique indexes to enforce unique and primary key constraints. Non-unique indexes are not dropped when the constraint is disabled, as would happen with unique indexes.

If a name is not specified by the user when creating a constraint, Oracle generates a unique constraint name in the form SYS_Cn, where n is an integer. A user-assigned constraint name will not change during a system export and import, whereas an Oracle-generated constraint name will typically change. Although it is not necessary, naming constraints makes it much easier to administer them as user-defined names are usually easier to remember and understand. A constraint should be named whenever it needs to be referenced after it is defined. This is also very useful because whenever a constraint is violated, the constraint name will be included as a part of the error message to easily diagnose the error.

Default values should be used whenever you know a column should default to a specific value. The datatype of the default value created must match the datatype of the column. The column must also be long enough to hold the value created. A DEFAULT expression cannot contain references to other columns.

To create constraints on a table, the user account must have the ability to create tables (CREATE TABLE or CREATE ANY TABLE privilege); or the ability to alter tables (ALTER TABLE or ALTER ANY TABLE privilege).