Database Concepts About Keys And Constraints

KEYS

A key is a set of attributes (usually just one) that will always uniquely identify

a record.

• A key consisting of more than one attribute is called a composite key.

• The primary key for a table is the key chosen to uniquely identify records

within the table. Primary key is defined on a single column It does not have null values.

• A foreign key is a field from a table that refers to (or targets) a specific key, usually the primary key, in another table.

A unique key is defined as having no two of its values the same. The columns of a unique key cannot contain null values. A table can have multiple unique keys or no unique keys at all.

The fields or combination of fields that are not used as primary key are known as candidate key or alternate key.

CONSTRAINTS

A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s). Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database.

Primary Key constraint

This constraint is used to guarantee that a column or set of columns on a table contain unique values for every record in the given table. This lets you ensure data integrity by always being able to uniquely identify the record in the table. A table can have only one primary key constraint defined on it, and the rows in the primary key columns cannot contain null values. A primary key constraint can be defined when a table is created, or it can be added later.

Unique Constraints

Unique constraints may be placed on multiple columns. They constrain the UPDATE/INSERTS on the table so that the values being updated or inserted do not match any other row in the table for the corresponding values.

Foreign Key (FK) Constraints

A foreign key constraint allows certain fields in one table to refer to fields in another table. This type of constraint is useful if you have two tables, one of which has partial information, details on which can be sought from another table with a matching entry. A foreign key constraint in this case will prevent the deletion of an entry from the table with detailed information if there is an entry in the table with partial information that matches it.

Check Constraints

A check constraint prevents updates/inserts on the table by placing a check condition on the selected column. The UPDATE/INSERT is allowed only if the check condition qualifies.

Not Null Constraint

A Not Null constraint enforces that the column will not accept null values. It enforces valid entries for a given column by restricting the type, the format, or the range of possible values.