Tags:concept database relationalmodel relationship keys Status:🟩

Database Keys and Relationships

Summary

Keys, such as primary and foreign keys, uniquely identify records and establish relationships between tables. Primary keys ensure that each record is distinct, while foreign keys link tables, enforcing referential integrity. These relationships help maintain data consistency, enabling efficient data retrieval and ensuring that the database structure is both organized and reliable.

Keys

Keys define unique records within a table/relation, and play an important role in setting relationships between different relations. It ensures that a relation complies with its definition, which is a set of unique records. Check out the Keys Example at the bottom.

Superkeys

A superkey is a set of attributes that uniquely identifies a record in a relation. Any combination of attributes that guarantees unique identification of records is considered a superkey. The entire set of attributes in a relation is always a superkey, but the minimal superkey (candidate key), is more commonly used in practice.

Candidate Keys

A candidate key is a minimal superkey, meaning that it uniquely identifies records in a relation and contains no unnecessary attributes. It satisfies both the uniqueness, not null and minimality properties. A relation can have more than one candidate key. Superkeys contain at least one candidate key, and out of all candidate keys, one will eventually be chosen to be the primary key.

Primary Keys

The primary key (PK) is the main key chosen to uniquely identify records in a relation. Primary keys cannot be null and are also used to create relationships with other relations. From the set of candidate keys, one is selected to serve as the primary key.’

When defining a column as a primary key implicitly adds a NOT NULL constraint

Alternate Keys

All the other candidate keys that did not get chosen as the primary key will instead be alternate keys.

Foreign Keys

A foreign key (FK) defines relationships between two relations. A foreign key in one relation references the primary key in another relation, establishing a link between the two. For a foreign key to be valid, the attributes in the FK must match the attributes of the primary key in the referenced relation, both in domain (data type), and values. See also:

Relationships

Relationships between tables are defined by primary and foreign keys. These keys create links between entities (represented by tables), in the database, forming associations that ensures referential integrity. See also: Example here.

Integrity Constraints

Integrity constraints (ICs) are rules that limit the allowed content or structure of the database to ensure data is correct, consistent and accurate. RDBMSs take care of ensuring the ICs is in a database. Types of Integrity Constraints:

Domain Constraint: Ensures that an attribute’s values conform to its defined data type.
Key Constraint: Ensures the uniqueness and minimality of candidate keys.
Entity Constraint: Enforces the primary key cannot be null.
Referential Constraint: Ensures that foreign key values correspond to existing values in the referenced primary key, or are null.

Examples

Relational database between 2 tables

Primary key in SQL

/* When creating a table */
CREATE TABLE table_name (
    column_name data_type PRIMARY KEY,
    other_column data_type,
    ...);
 
/* To existing table */
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
 
/* Multiple columns */
ALTER TABLE table_name 
ADD PRIMARY KEY (column1, column2);

Foreign key in SQL

/* When creating a table */
CREATE TABLE child_table (
    column_name data_type,
    foreign_key_column data_type,
    PRIMARY KEY (column_name),
    FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_column));
 
/* When creating a table (in-line) */
CREATE TABLE child_table (
    column_name data_type,
    foreign_key_column data_type REFERENCES parent_table(parent_column),
    PRIMARY KEY (column_name),
    FOREIGN KEY (foreign_key_column));
 
 
/* To existing table */
ALTER TABLE child_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_column);

Keys Example

employee_id	name	email	phone_number	department_id
1	Alice Johnson	alice.j@example.com	1234567890	101
2	Bob Smith	bob.s@example.com	2345678901	102
3	Charlie Lee	charlie.l@example.com	3456789012	101

Key Type	Examples
Superkey	`{employee_id}`, `{employee_id, email}`
Candidate Key	`{employee_id}`, `{email}`
Primary Key	`{employee_id}`
Alternate Key	`{email}`
Foreign Key	`department_id` (referencing `departments`)
Unique Key	`{email}`

Superkeys: A superkey is any set of attributes (columns) that can uniquely identify each row in the table.

{employee_id}
{employee_id, email}
{employee_id, name, phone_number}
In general, adding any combination of attributes to employee_id still forms a superkey.

Candidate key A candidate key is a minimal superkey — a superkey with no unnecessary attributes.

{employee_id}
{email} Both employee_id and email can uniquely identify employees and are minimal.

Primary Key The primary key is a candidate key chosen by the database designer to uniquely identify rows in the table.

employee_id This is often chosen because it is short, unique, and cannot change.

Alternate Key Any candidate key not chosen as the primary key is an alternate key.

email Since employee_id is the primary key, email becomes the alternate key.

Foreign Key A foreign key is an attribute in one table that refers to the primary key in another table to establish a relationship.

department_id in the employees table could be a foreign key referencing a departments table with department_id as its primary key.

department_id	department_name
101	HR
102	IT
Unique Key
A unique key ensures that a column or combination of columns has unique values across rows, except for `NULL`.

The email column could have a unique constraint to prevent duplicate emails, ensuring each email is unique.

INDBS Noter

Explorer