Keys Flashcards
Primary Key
A column (or collection of columns) or a set of columns that uniquely identifies each row in the table.
Uniquely identifies a single row in the table
Null values not allowed
Foreign Key
Foreign key maintains referential integrity by enforcing a link between the data in two tables.
The foreign key in the child table references the primary key in the parent table.
The foreign key constraint prevents actions that would destroy links between the child and parent tables.
Candidate Key
A set of attributes that uniquely identify tuples in a table.
Candidate Key is a super key with no repeated attributes.
The Primary key should be selected from the candidate keys.
Every table must have at least a single candidate key.
A table can have multiple candidate keys but only a single primary key.
Unique Key
Uniquely identifies a single row in the table.
Multiple values allowed per table.
Null values allowed.
Properties of Candidate Keys
It must contain unique values
Candidate key in SQL may have multiple attributes
Must not contain null values
It should contain minimum fields to ensure uniqueness
Uniquely identify each record in a table
Compound Key
COMPOUND KEY has two or more attributes that allow you to uniquely recognize a specific record.
It is possible that each column may not be unique by itself within the database.
However, when combined with the other column or columns the combination of composite keys become unique.
The purpose of the compound key in database is to uniquely identify each record in the table.
Composite Key
COMPOSITE KEY is a combination of two or more columns that uniquely identify rows in a table.
The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed. Hence, they are combined to uniquely identify records in a table.
The difference between compound and the composite key is that any part of the compound key can be a foreign key, but the composite key may or maybe not a part of the foreign key.
Surrogate Key
SURROGATE KEYS is An artificial key which aims to uniquely identify each record is called a surrogate key.
This kind of partial key in dbms is unique because it is created when you don’t have any natural primary key.
They do not lend any meaning to the data in the table.
Surrogate key in DBMS is usually an integer. A surrogate key is a value generated right before the record is inserted into a table.
Super Key
A superkey is a group of single or multiple keys which identifies rows in a table. A Super key may have additional attributes that are not needed for unique identification.
Rules for Primary Key
Two rows can’t have the same primary key value
It must for every row to have a primary key value.
The primary key field cannot be null.
The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.
Alternate Key
ALTERNATE KEYS is a column or group of columns in a table that uniquely identify every row in that table.
A table can have multiple choices for a primary key but only one can be set as the primary key.
All the keys which are not primary key are called an Alternate Key.
