ALTER TABLE PRIMARY
KEY
Adds a primary
key to a table.
SYNTAX
| table_name |
Name
of the table you are adding the primary key to. |
| column_name |
Name
of the column the primary key is created on. |
DESCRIPTION
The ALTER
TABLE PRIMARY KEY command modifies the definition of an existing table
and adds a primary key. To execute the ALTER TABLE PRIMARY KEY command
on a table, you must be the table owner, have DBA security privilege,
or have both the ALTER and INDEX privileges for that table.
A key is
a column or combination of columns that help identify specific rows in
a table. The columns that make up a key are known as key columns. A unique
key is a key in which no two records have the same value or the key field.
A primary
key is a key that uniquely identifies each row in a table. Without a primary
key, it is impossible to distinguish between specific rows in a table
because rows may contain duplicate values. The DBMS will not allow you
to define a primary key on columns that contain duplicate values, and
will not allow you to enter a duplicate value in a primary key that already
exists.
A foreign
key is a key that corresponds to the primary key (or a unique index) of
another table. This establishes a parent-child relationship between two
tables that is represented by common data values stored in the tables.
The parent table contains the primary key or unique index, and the child
table contains the foreign key whose columns correspond to columns in
the parent table.
Referential
integrity ensures that every value in a child key (the foreign key of
the child table) has a corresponding value in the parent key (the primary
key or unique index of the parent table). Referential integrity is enforced
between tables using the parent-child relationship established with foreign
keys. DBMaker has automatic support for referential integrity constraints
between tables through the definition of foreign keys. When adding a record
to a child table, the value in the child key must also exist in the parent
key. Similarly, when deleting a record from the parent table, all records
in the child key with the same value must be deleted first.
Primary
keys ensure data integrity in a table by requiring unique key values in
each record of the primary key. Since this means columns in a primary
key may not contain duplicate or null values, you must define the key
columns with the NOT NULL constraint.
Each table
may only have one primary key. You cannot name a primary key for this
reason. Instead, DBMaker will automatically create and maintain a unique,
internally managed index named PrimaryKey for the primary key in each
table. Since DBMaker builds an index on the primary key, it is not necessary
to build another index on the columns in the primary key to increase the
performance of query operations.
Primary
keys may be built on up to 16 columns, providing the size of the columns
does not exceed 1024 bytes. You cannot create a primary key on a view,
but you may create one on a synonym. When you create a primary key on
a synonym, the primary key is actually created on the base table, as if
you specified the base table directly in the ALTER TABLE PRIMARY KEY command.
EXAMPLE
The following
example creates a primary key on column CustNo of the Customers table.
The CustNo column must be defined with the NOT NULL constraint, and all
values in the CustNo column must be unique, or the table must be empty.
ALTER TABLE Customers PRIMARY KEY (CustNo)
|
RELATED COMMANDS
<
ALTER TABLE MODIFY COLUMN |
Contents | ALTER
TABLE SET OPTIONS >
|
