1.Recap
In previous units, we've covered the 4 main SQL keywords that let us manipulate data in a relational database, also known as CRUD:
SELECT INSERT UPDATE DELETE
Also, we covered main SQL data definition statements such as:
CREATE DATABASE CREATE TABLE ALTER TABLE DROP TABLE DROP DATABASE
From the previoust list, the CREATE TABLE statement, needs to be defined with SQL data types and SQL constraints.
2. What we'll learn
Regarding SQL constraints, we've covered the NOT NULL and the PRIMARY KEY constraint. In this unit, we are going to coVer 2 new SQL constraints: the FOREIGN KEY and the UNIQUE KEY. As the name implies, unique keys only allow values that are not repeated in the same table column. On the other hand, foreign keys work as a common field between 2 or more tables allowing us to make more advanced SQL sELECT queries. These new sELECT queries will use a new SQL statement called: JOIN.
Let's begin by examining the data from 2 different tables in the next section.
3. Resetting the unit's databases
If you make a mistake while editing any of the unit's databases or just want to reset the databases back to their original state, click the "Reset Section Database" button that is available in the relevant sections.
4. The foreign key
Another type of SQL constraint, such as the NOT NULL or the PRIMARY KEY constraint, is the FOREIGN KEY constraint.
The FORE IGN KEY constraint, also known as reference key, describes the relationship between data in two or more tables.
The FOREIGN KEY can be specified either with a SQL CREATE TABLE statement or with an ALTER TABLE statement.
Unlike the PRIMARY KEY, foreign keys can be NULL and can be duplicated.
4. 1. Foreign key
We've created a school database with 2 tables: The classrooms table and the courses table.
In the mysql> prompt,execute a SHOW COLUMNS FROM school.classrooms; to display the classroom data definition and a SHOW COLUMNS FROM school.courses; to display the courses data definition.
While planning how the school managament system should work, the classrooms and the courses tables were configured in order to follow these parameters:
The school offers different courses that are taken by a group of students in a classroom The classrooms table has a course_id column which references the course to be held in a specific classroom The course_id is working as the FOREIGN KEY that is associated with the id column of the school.courses table Some classrooms may not have a course assigned yet, so course_id defaults to NULL
The course_id reference will allow us, the SQL developers, to create SELECT queries that will join information about the school . classrooms table and the schools . courses in a single representation.
What does MUL mean? The SHOW COLUMNS FROM school.classroOms; disp|ay the FOREIGN KEY as MUL in the KEY column.
The name comes from "multiple" because multiple occurences of the same value are allowed.
Complete a challenge about SQL constraints in the