Page 88 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Enforcing Primary key and foreign key

What is the Key?
In the realm of database management systems, a key refers to a particular attribute or a group of attributes
employed to distinguish each record, often called a tuple, within a table uniquely. Keys hold significant importance
in the structure of a relational database, as they serve to organize data and create connections between tables.
Within a database, various types of keys exist, each serving a distinct function. Keys are pivotal components
in relational databases, as they ensure record uniqueness, facilitate table relationships, and enhance overall
database performance.
1 Enforcing a Primary Key
A primary key is a unique identifier for each record in a table. To enforce a primary key constraint, follow these
steps:
• Define a primary key when creating a table using the ‘PRIMARY KEY’ constraint.
CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name
VARCHAR(50) );
• If you’re altering an existing table to add a primary key, use the ALTER TABLE statement.
ALTER TABLE employees ADD PRIMARY KEY (employee_id);
The primary key constraint enforces uniqueness, meaning that no two rows in the table can have the same
value for the primary key column(s).
2 Enforcing a Foreign Key
A foreign key is a field in one table that refers to the primary key in another table, establishing a relationship
between the tables. To enforce a foreign key constraint, follow these steps:
• Define a foreign key when creating a table using the ‘FOREIGN KEY’ constraint.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT, order_date DATE,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
• The ‘REFERENCES’ clause specifies the table and column that the foreign key references. In this example,
it references the ‘customer_id’ column in the ‘customers’ table.
• Ensure that the referenced column in the referenced table (in this case, ‘customers’ in the ‘customers’
table) has a primary key constraint.
Foreign key constraints enforce referential integrity, ensuring that data in the referencing table (in this case, the
‘orders’ table) is consistent with data in the referenced table (in this case, the ‘customers’ table).
Enforcing these constraints helps maintain data quality and consistency in your database, preventing the insertion
of invalid or inconsistent data and facilitating data relationships between tables.
Difference between Primary Key and Foreign Key
Primary keys and foreign keys are both fundamental components of relational database design, but they serve
different purposes and have distinct characteristics. Here are the key differences between primary keys and
foreign keys:
Primary Key

1 Uniqueness: A primary key is a column or a set of columns in a table that uniquely identifies each row. Every
value in the primary key column(s) must be unique within the table.
2 Required: A primary key is required for every table. It ensures that each row in the table can be uniquely
identified.

75

CITS : IT&ITES - Computer software application - Lesson 18 - 36