A primary key has the following features:

> One and only one primary key must be specified. It uses the reserved words PRIMARY KEY in the CREATE TABLE statement.

> A simple key (one column) can be defined as a column constraint or a table constraint. As a simple key column constraint, PRIMARY KEY is placed in the column definition.

> A composite key (multiple columns) can only be defines as a table constraint. As a table constraint, the PRIMARY KEY definition has the column names specified.

> The primary key columns can have NOT NULL specified on the column. If NOT NULL is not specified on primary key columns, most RDBMS systems set the columns to NOT NULL by default.
DB2 requires that NOT NULL be specified for primary key columns.

> Primary key values can not be changed. If you want to change the value of a primary key, you must DELETE the row and then INSERT the same row with new key values.

Primary keys were discussed in Relational Database Basics. Let us review:Tables and columns have unique names, but rows are unnamed. Rows can not be identified by their position within the table, and the relational model looks at a table as an unordered set. Therefore, each table is required to have one or more columns as a unique primary key. It is traditional to place the primary key columns in the leftmost positions.

> A table must have one and only one primary key.

> A primary key with only one column is called a simple key.

> A primary key with two or more columns is called a composite key.

> No two rows in a table can have the same primary key.

> A primary key only includes the columns necessary for uniqueness.

> A primary key can not have an empty value, called a null, in any column.

> If referential integrity is enforced, a primary key can not be deleted unless all foreign keys are also deleted.

> DEFAULT is a column constraint in a CREATE TABLE statement and can not be a table constraint.

> When inserting a row, if no value is specified and the DEFAULT column constraint is specified, a default value will be inserted.

> The default value must be the same data type and fit in the length of the column definition.

> If no value is specified after the DEFAULT and NOT NULL in the table definition, numeric columns will default to zeroes and character columns will default to spaces.

> If DEFAULT is not defined and NOT NULL is defined and you insert a row with no data in this column, some RDBMS systems will refuse to insert the row, while other RDBMS systems will give a default value that matches the data type.

          CREATE TABLE SAMPLE_TBL
                (ID       CHAR(12)     NOT NULL PRIMARY KEY,
                 TYPE     CHAR(4)      NOT NULL DEFAULT,
                 NUMB     INTEGER          NULL,
                 NAME     VARCHAR(200)          DEFAULT 'unknown',
                 SIZE     SMALLINT     NOT NULL DEFAULT 7,
                 PRICE    DECIMAL(5,2) NOT NULL DEFAULT 0.00,
                 DATE     DATE                  DEFAULT CURRENT_DATE,
                 TIME     TIME                  DEFAULT CURRENT_TIME,
                 QTY      DECIMAL(4,0)          DEFAULT (5+3)*2);

With an INSERT ROW and no value given:

> ID must have a valid value.

> TYPE will default to spaces.

> NUMB will default to null. The NULL definition is optional by default.

> All other columns will default to the specified default value.

> Access, DB2, and MySQL do not allow arithmetic expressions.

> MySQL only allows a literal as a default. You can not use CURRENT_DATE or CURRENT_TIME.

> Oracle does not have CURRENT_DATE. It has SYSDATE and DEFAULT, which must be before constraints.

> SQL Server does not have DATE or TIME. It only has DATETIME.