curriculum/challenges/english/blocks/lecture-working-with-relational-databases/687ea8bb2b781b19c790d00e.md
In SQL, primary keys and foreign keys are used to establish relationships between tables.
Let's start with primary keys.
A primary key is a column or set of columns that uniquely identifies each record (row) in a table. This constraint ensures that no records in the table will have the same value for the primary key.
This is why a table can only have one primary key.
Values in the primary key column can't be NULL either, so they will always have a valid value.
In PostgreSQL, to make a column the primary key of your table, you just need to add PRIMARY KEY after the data type:
column_name data_type PRIMARY KEY
This is an example of a students table:
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
Each student will have its own student_id, and this ID will be the primary key of the table. The SERIAL type is useful here because it ensures the student_id will always have a unique value.
A composite primary key is for when a table doesn't have a single unique column to identify the row. In this case, you can use a combination of two or more columns as the primary key that, together, are unique.
To do this, denote which columns are the composite primary key when you create the table like this:
CREATE TABLE table_name (
column1 data_type column_constraint,
column2 data_type column_constraint,
column3 data_type column_constraint,
...
PRIMARY KEY (column1, column2)
);
Imagine you have a table with student_id and course_id columns that tells you what students are enrolled in various classes.
Each student will be enrolled in several classes, and each class will have several students enrolled in it. So neither column is unique - but you will never have the same student enrolled in the same class more than once. So you can use the combination of the two columns as the primary key:
CREATE TABLE course_enrollments (
student_id INT,
course_id INT,
...
PRIMARY KEY (student_id, course_id),
);
Primary keys are essential for ensuring data uniqueness and integrity, for optimizing data retrieval, and for establishing relationships with other tables in a relational database.
That takes us to the next type of key that we will cover in this lesson: foreign keys.
A foreign key is a column (or set of columns) in a table that references the primary key of another table. A table can have multiple foreign keys.
In this example, we have two tables, customers, and orders:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(100) NOT NULL,
...
);
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
...
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
The primary key of the customers table is customer_id and the primary key of the orders table is order_id.
But notice that there is also a customer_id column in the orders table.
Why are we storing customer information in the orders table?
We do this to create a relationship between the customers and orders tables. Each order will store the ID of the customer who submitted it.
To create the relationship between the tables, we will make the customer_id column in the orders table a foreign key that references the customer_id column in the customers table:
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
Values in a foreign key column must either match the primary key values in the referenced table or be NULL (if the foreign key allows NULLs).
Why is this helpful?
By making sure that these columns in both tables match exactly, foreign and primary keys prevent the creation of "orphaned" records, records that refer to a non-existent record in another table.
Primary and foreign keys are fundamental for relational databases. They power the "relational" aspect of these databases, allowing you to model real-world data in an accurate way.
What is the primary purpose of a primary key in a relational database table?
To link the table to other tables.
Think about what makes each row in a table distinct.
To uniquely identify each record in the table.
To define the data type of a column.
Think about what makes each row in a table distinct.
To enforce relationships with foreign keys in the same table.
Think about what makes each row in a table distinct.
2
A foreign key in one table typically references which key in another table?
Another foreign key
Think about which key in the referenced table ensures the integrity and validity of the relationship created by the foreign key.
A unique index
Think about which key in the referenced table ensures the integrity and validity of the relationship created by the foreign key.
A primary key
Any column with the same data type
Think about which key in the referenced table ensures the integrity and validity of the relationship created by the foreign key.
3
Which of the following is a characteristic of a primary key?
It can contain NULL values.
Remember the fundamental requirement for identifying each row distinctly.
It can be repeated in the same table.
Remember the fundamental requirement for identifying each row distinctly.
A table can have multiple primary keys.
Remember the fundamental requirement for identifying each row distinctly.
It must contain unique values.
4