Understanding Primary Keys, Foreign Keys, and Constraints in SQL
Tags: webdev, backend, sql, 100daysofcode
Published: 2023-05-01 21:22:09

**SQL** stands for Structured Query Language, which is a standard programming language used for managing relational databases. It is used to create, modify, and retrieve data from a database, as well as to manage database objects such as tables, views, indexes, and stored procedures.

Important concepts in SQL databases that are used to establish relationships between tables and ensure data integrity are _primary keys, foreign keys, and foreign key constraints_.

**A primary key** is a unique identifier for a row in a table. It is a column or a combination of columns that uniquely identify each row in the table. Primary keys are used to enforce entity integrity, which means that each row in the table must be uniquely identifiable.

**A foreign key** is a column or a combination of columns in one table that refers to the primary key of another table. It can be used to ensure referential integrity, which means that the values in the column or columns that refer to the primary key of another table must match one of the values in that primary key.

**A foreign key constraint** is a rule that enforces referential integrity between two tables. It specifies that the values in a foreign key column or columns must match one of the values in the primary key of another table and can be used to ensure that data is consistent and accurate across multiple related tables.


Here's a simple example to illustrate the concepts of primary keys, foreign keys, and foreign key constraints in SQL databases:

Suppose we have two tables: "customers" and "orders". The "customers" table has columns for "customer_id", "name", and "email", and the "orders" table has columns for "order_id", "customer_id", "date", and "total".

To establish a relationship between the two tables, we can define the "customer_id" column in the "customers" table as the primary key, and the "customer_id" column in the "orders" table as the foreign key.

Here's the SQL code to create these tables and define the primary key and foreign key constraints:

```sql
CREATE TABLE customers (
  customer_id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

CREATE TABLE orders (
  order_id INT NOT NULL,
  customer_id INT NOT NULL,
  date DATE,
  total DECIMAL(10,2),
  PRIMARY KEY (order_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
```

In this example, the "customer_id" column in the "customers" table is the _primary key_, which means that it uniquely identifies each customer in the table. The "customer_id" column in the "orders" table is the _foreign key_, which refers to the primary key in the "customers" table.

The _foreign key constraint_ is defined using the "REFERENCES" keyword, which specifies that the "customer_id" column in the "orders" table references the "customer_id" column in the "customers" table. This constraint ensures that each order in the "orders" table is associated with a valid customer in the "customers" table.

So, when we insert data into the "orders" table, the _foreign key constraint_ ensures that the value in the "customer_id" column matches one of the values in the "customer_id" column in the "customers" table. If the value in the "customer_id" column does not match a valid value in the "customers" table, the foreign key constraint will prevent the data from being inserted, ensuring that the data is consistent and accurate across both tables.

_This article was created with the help of AI._