Tuesday, July 7, 2009

Candidate, Artificial (Derived) Primary Key, Unique Primary Key

Integrity Constraint
An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true and that follows these rules:
· If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint cannot be enforced.
· After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned.
Integrity constraints are defined with a table and are stored as part of the table's definition in the data dictionary, so that all database applications adhere to the same set of rules. When a rule changes, it only needs be changed once at the database level and not many times for each application.
Candidate key:
A relation (table) may contain more than one key. In this case, one of the keys is selected as the primary key and the remaining keys are called candidate keys. The candidate key constraint indicates that the table cannot contain two different rows with the same values under candidate key columns.

Artificial (Derived) Primary Key
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.

UNIQUE Constraints
You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.

FOREIGN KEY Constraints
A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.

PRIMARY KEY Constraints
A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values.

No comments:

Post a Comment