Unique column, unique index and priamry key use in DB2.

[Aanchal Sahu]

If I define a unique column in a DB2 columns why do we need to define a unique INDEX also in order to insert the values in the table? And when we have a unique INDEX why do we need a primary key, are they just not similar things?

[Aanchal Sahu]

I did some study on this but I still hail the same confusion. Does anyone has good explantion about it?

[Akatsukami]

1. By defining a column as unique, you are asserting that it will be used in a unique index. Note that the converse is not true; a unique index can be created without defining any of the columns in it as unique.
2. Remember that a primary key will be used to constrain rows in another table, and that therefore a strictly one-to-one relationship is required; columns in a primary key must be non-nullable.

[Anuj Dhawan]

This is directly from the manual and can be of intrest for you, [mention]Aanchal Sahu[/mention]:

DB2 keys

A key is a column or an ordered collection of columns that is identified in the description of a table, an index, or a referential constraint. Keys are crucial to the table structure in a relational database.

Keys are important in a relational database because they ensure that each record in a table is uniquely identified, they help establish and enforce referential integrity, and they establish relationships between tables. The same column can be part of more than one key.

A composite key is an ordered set of two or more columns of the same table. The ordering of the columns is not constrained by their actual order within the table. The term value, when used with respect to a composite key, denotes a composite value. For example, consider this rule: "The value of the foreign key must be equal to the value of the primary key." This rule means that each component of the value of the foreign key must be equal to the corresponding component of the value of the primary key.

DB2® supports several types of keys.

Unique keys

A unique constraint is a rule that the values of a key are valid only if they are unique. A key that is constrained to have unique values is a unique key. DB2 uses a unique index to enforce the constraint during the execution of the LOAD utility and whenever you use an INSERT, UPDATE, or MERGE statement to add or modify data. Every unique key is a key of a unique index. You can define a unique key by using the UNIQUE clause of either the CREATE TABLE or the ALTER TABLE statement. A table can have any number of unique keys.

The columns of a unique key cannot contain null values.

Primary keys
A primary key is a special type of unique key and cannot contain null values. For example, the DEPTNO column in the DEPT table is a primary key.

A table can have no more than one primary key. Primary keys are optional and can be defined in CREATE TABLE or ALTER TABLE statements.

The unique index on a primary key is called a primary index. Start of changeWhen a primary key is defined in a CREATE TABLE statement or ALTER TABLE statement, DB2 automatically creates the primary index.End of change

If a unique index already exists on the columns of the primary key when it is defined in the ALTER TABLE statement, this unique index is designated as the primary index when DB2 is operating in new-function mode and implicitly created the table space.

Parent keys
A parent key is either a primary key or a unique key in the parent table of a referential constraint. The values of a parent key determine the valid values of the foreign key in the constraint.

Foreign keys
A foreign key is a key that is specified in the definition of a referential constraint in a CREATE or ALTER TABLE statement. A foreign key refers to or is related to a specific parent key.

Unlike other types of keys, a foreign key does not require an index on its underlying column or columns. A table can have zero or more foreign keys. The value of a composite foreign key is null if any component of the value is null.

The following figure shows the relationship between some columns in the DEPT table and the EMP table.

Figure 1. Relationship between DEPT and EMP tables.gif

Begin figure description. The DEPT table and EMP table are pictured with arrows indicating the primary and foreign keys for each table. End figure description.

Figure notes: Each table has a primary key:

1. DEPTNO in the DEPT table
2. EMPNO in the EMP table

Each table has a foreign key that establishes a relationship between the tables:

1. The values of the foreign key on the DEPT column of the EMP table match values in the DEPTNO column of the DEPT table.
2. The values of the foreign key on the MGRNO column of the DEPT table match values in the EMPNO column of the EMP table when an employee is a manager.

To see a specific relationship between rows, notice how the shaded rows for department C01 and employee number 000030 share common values.

Reference: https://www-01.ibm.com/support/knowledg ... _keys.dita