Foreign keys and joints¶

Foreign keys¶

In order to create relationships between tables, in addition to defining primary keys, you must specify correlated foreign keys that will indicate the relationship between objects (i.e., they will point to the primary key of another table). A foreign key can be entered into the table using a structure:

CONSTRAINT name FOREIGN KEY (column1, column2, column3, ..., columnN) REFERENCES table_name (column1, column2, ..., columnN)

• name is the name of the restriction by which the foreign key will be identified
• kcolumns defined after FOREIGN KEY will be foreign keys of the table
• table_name specifies the name of the table from which the foreign key originated
• columns after the REFERENCES clause, specify specific columns derived from the created foreign keys.

Creating a foreign key.¶

You can create a foreign key when you create the table:

CREATE TABLE Product
(
    Id INTEGER PRIMARY KEY,
    name VARCHAR(20),
    ProducerId INTEGER, 
    CONSTRAINT ProducerId_fk FOREIGN KEY (ProducerId) REFRENCES Producer(Id)
);

Foreign keys can also be added to existing tables. To do this, the ALTER TABLE statement is executed. Its general form is as follows:

ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column1, column2, ..., columnN) REFERENCES table_name(column1, column2, ..., columnN);

A real example looks like this:

ALTER TABLE Product ADD CONSTRAINT producer_fk FOREIGN KEY (ProducerId) REFERENCES Producer(Id);

Deleting a foreign key¶

Deleting a foreign key that exists within the table is done using the ALTER TABLE statement:

ALTER TABLE Product DROP FOREIGN KEY producer_fk;

Advanced constructions of the SELECT clause¶

Selecting data from several tables¶

Using the SELECT statement, you can retrieve data from several tables. It's enough to enter next names after the decimal point.

SELECT column1, column2, column3, ...,columnN FROM table1, tablel2, ..., tableN 
[WHERE condition]
...;

We often make such queries by assigning aliases to individual tables, e.g.

SELECT k.Publisher, p.name FROM Book AS k, Product AS p;

ATTENTION: The keyword AS, when assigning aliases, is optional. In addition, in the absence of an alias for a given table, when selecting a column, we can use the table name.

Joins¶

There are various types of table joins in SQL, including:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN.

For the following examples, we will use the following tables along with the data:

INNER JOIN¶

Joining this type enables the table data to be joined like the usual SELECT command, including tables. The query structure looks as follows:

SELECT column1, column2, ..., columnN FROM table1 [INNER] JOIN table2 [ON condition];

The ON clause defines a join condition.

For the presented example, the command will take the following form:

SELECT ProductId, Product.name, Product.ProductentId, Producer.ProducerId, Producer.name, Producer.Address FROM Product INNER JOIN Producer ON Product.ProducerId=Producer.Producer.Id;

The result of the command will return the common part of both tables:

In case both tables have the same column names, specify the specific column using the table name as a prefix.

LEFT JOIN¶

Joining this type allows you to include resulting data that is not related to the joined table. In short, if there are records in table1 that are not correlated withtable2 records, they will still be included in the join, and the missing values will be supplemented with NULL values. The diagram of the instructions is as follows:

SELECT column1, column2, ..., columnN FROM table1 [LEFT] JOIN table2 [ON condition];

SELECT ProductId, Product.name, Product.ProductentId, Producer.ProducerId, Producer.name, Producer.Address FROM Product LEFT JOIN Producer ON Product.ProducerId=Producer.Producer.Id;

The result will be the following data set:

Thanks to the LEFT JOIN query, you can easily get data on Products with an undefined Producer.

RIGHT JOIN¶

Joining this type works similarly to LEFT JOIN with the difference that in the result table the rows of the second table are included, which have no references to their counterparts in the first. The diagram of the instructions is as follows:

SELECT column1, column2, ..., columnN FROM table1 [RIGHT] JOIN table2 [ON condition];

For the following command:

SELECT ProductId, Product.name, Product.ProductentId, Producer.ProducerId, Producer.name, Producer.Address FROM Product RIGHT JOIN Producer ON Product.ProducerId=Producer.Producer.Id;

The result will be the following data set:

FULL OUTER JOIN¶

This join allows you to retrieve all records from two tables. The table1 records that have no equivalent intable2, and the vice versa.

SELECT column1, column2, ..., columnN FROM table1 [FULL OUTER] JOIN table2 [ON condition];

For the following command:

SELECT ProductId, Product.name, Product.ProductentId, Producer.ProducerId, Producer.name, Producer.Address FROM Product FULL OUTER JOIN Producer ON Product.ProducerId=Producer.Producer.Id;

The result will be the following data set: