Relational databases. What is the primary key in the database? Description of primary key external
Last update: 27.04.2019
Foreign keys allow you to establish relationships between tables. The foreign key is set for columns from the dependent, subordinate table, and points to one of the columns from the main table. Typically, a foreign key points to a primary key from a related master table.
The general syntax for setting a foreign key at the table level is:
FOREIGN KEY (column1, column2, ... columnN) REFERENCES master_table (column_main_table1, column_main_table2, ... column_main_tableN)
To create a foreign key constraint, after FOREIGN KEY, you specify the table column that will represent the foreign key. The REFERENCES keyword is followed by the name of the related table, followed by the name of the related column in parentheses that the foreign key will point to. The REFERENCES expression is followed by the ON DELETE and ON UPDATE statements, which specify the action to be taken when a row is deleted and updated from the main table, respectively.
For example, let's define two tables and link them using a foreign key:
CREATE TABLE Customers (Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR (20) NOT NULL, LastName VARCHAR (20) NOT NULL, Phone VARCHAR (20) NOT NULL UNIQUE); CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
In this case, the Customers and Orders tables are defined. Customers is in charge and represents the customer. Orders is dependent and represents an order placed by a customer. The Orders table is linked through the CustomerId column to the Customers table and its Id column. That is, the CustomerId column is a foreign key that points to the Id column from the Customers table.
Using the CONSTRAINT statement, you can specify a name for the foreign key constraint:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
ON DELETE and ON UPDATE
The ON DELETE and ON UPDATE statements can be used to set the actions to be taken, respectively, when a related row is deleted and modified from the main table. The following options can be used as an action:
CASCADE: Automatically deletes or modifies rows from a dependent table when deleting or modifying related rows in the main table.
SET NULL: Sets the foreign key column to NULL when deleting or updating a related row from the master table. (In this case, the foreign key column must support setting to NULL)
RESTRICT: Rejects deleting or modifying rows in the master table if there are related rows in the dependent table.
NO ACTION: same as RESTRICT.
SET DEFAULT: When deleting a related row from the master table, sets the foreign key column to the default value, which is set using the DEFAULT attributes. Although this option is available in principle, the InnoDB engine does not support this expression.
Cascade deletion
Cascading delete allows you to automatically delete all related rows from the dependent table when you delete a row from the master table. For this, the CASCADE option is used:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);
The ON UPDATE CASCADE statement works in a similar way. Changing the value of the primary key will automatically change the value of its associated foreign key. However, since primary keys are changed very rarely, and, in principle, it is not recommended to use columns with mutable values as primary keys, in practice the ON UPDATE statement is rarely used.
Setting to NULL
Setting the SET NULL option for a foreign key requires the foreign key column to be nullable:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
F oreign Key (foreign key) is the key used to join two tables. It is sometimes also called a referencing key.
A foreign key is a column or combination of columns whose values correspond to a primary key in another table.
The relationship between the 2 table matches the primary key in one of the foreign key tables in the second table.
If a table has a primary key defined on any field (s), then you cannot have two records that have the same value for that field (s).
Example
Consider the structure of the following two tables.
CUSTOMERS table
CREATE TABLE CUSTOMERS (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID));
ORDERS table
CREATE TABLE ORDERS (ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS (ID), AMOUNT double, PRIMARY KEY (ID));
If the ORDERS table has already been created and the foreign key has not yet been set, then the syntax is used to set the foreign key by modifying the table.
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);
Removing a foreign key constraint
To drop a foreign key constraint use the following SQL syntax.
ALTER TABLE ORDERS DROP FOREIGN KEY;
They are used in any activity: in the banking and financial industries, tourism business, warehouses, production and training. They are a collection of tables, have clear properties and are subject to strict requirements. In relational databases, tables are called relationships.
What is a primary key in a database
In a database, the primary key of a table is one of its columns (Primary key). Let's see with an example how it looks. Imagine a simple relationship of university students (let's call it "Students").
We need to uniquely identify the student by one column. To do this, the information in this column for each record must be unique. But the available data in this regard do not allow us to unambiguously identify the record, since namesakes, namesakes and students with the same surnames and first names can study in the same course and in the same faculty. The primary key in the database is used to accurately identify the required row in the relation. Most often, a numeric field is used in this capacity, which automatically increases with the entry of a record (auto-incrementing identifier column).
Simple and Composite Primary Key
Primary key can be simple or compound. If the uniqueness of a record is determined by the value in only one field, as described above, we are dealing with a simple key. A composite key is a database primary key that consists of two or more fields. Consider the following attitude of bank customers.
| FULL NAME. | Date of Birth | Passport Series | Passport ID |
| Ivanov P.A. | 12.05.1996 | 75 | 0553009 |
| Sergeev V.T. | 14.07.1958 | 71 | 4100654 |
| L.V. Krasnov | 22.01.2001 | 73 | 1265165 |
People's passports may contain the same series or numbers, but there are no passports with the same series and number combination. Thus, the fields "Passport series" and "Passport number" will become a composite key of the specified relationship, uniquely identifying a person.
Links between relationships
So, the primary key in a database is one or more columns of a table that allows you to uniquely identify the row of this relationship. What is it for?
Let's go back to the first example with the "Students" relationship. In addition to this relationship, the database stores other information, for example, the progress of each student. In order not to repeat all the information that is already contained in the database, they use the key referring to the required record. It looks like this.
In the two relationships of the example, we see the ID field. These are the primary keys in the database for these tables. As you can see, the progress only contains links to these fields from other tables without the need to indicate all the information from them.
Natural and surrogate key
How is the primary key of a database table determined? The two examples we have considered - "Students" and "Bank Clients" - illustrate the concepts of natural and surrogate keys. In the table of bank clients, we have defined a key consisting of the fields "Number" and "Passport series", using the existing columns. Such a key is called natural; to determine it, we did not make any changes or additions. In the case of the "Students" relationship, no field or combination of fields gave us uniqueness. This forced us to enter an additional field with the student code. Such a key is called a surrogate key, for which we have added one more service column to the table. This column does not carry any useful information and only serves to identify records.
Foreign key and data integrity in the database
All of the above leads us to Foreign key and database integrity. Foreign key is a field that refers to the Primary key of the foreign relationship. In the table of grades, these are the "Student" and "Discipline" columns. Their data refers us to external tables. That is, the field "Student" in the relation "Performance" is a Foreign key, and in the relation "Student" it is the primary key in the database.
An important principle of building databases is their integrity. And one of its rules is link integrity. This means that the foreign key of the table cannot refer to the nonexistent Primary key of another relationship. You cannot delete a record with the code 1000 - Ivanov Ivan from the "Student" relationship if a record from the grades table refers to it. In a well-formed database, when you try to delete, you will receive an error that this field is in use.
There are other groups of integrity rules, as well as other database constraints, which also deserve attention and should be taken into account by developers.
FOREIGN KEY used to restrict links.
When all the values in one field of a table are represented in a field in another table, the first field is said to refer to the second. This indicates a direct relationship between the values of the two fields.
When one gender in a table refers to another, it is called foreign key; and the field it refers to is called parent key... The foreign key and parent key names do not have to be the same. A foreign key can have any number of fields, all of which are treated as a single module. The foreign key and the parent key that it refers to must have the same field number and type, and be in the same order. When a field is a foreign key, it is associated in a specific way with the table it refers to. Each value (each row) of a foreign key must unambiguously refer to one and only this value (row) of the parent key. If this condition is met, then the database is in the state referential integrity.
SQL maintains constrained referential integrity FOREIGN KEY... This function should constrain the values that can be entered into the database in order to force the foreign key and parent key to comply with the principle of referential integrity. One of the restriction actions FOREIGN KEY is discarding values for fields that are constrained as a foreign key that is not yet represented in the parent key. This restriction also affects the ability to change or delete parent key values.
Limitation FOREIGN KEY used in a CREATE TABLE (or ALTER TABLE (intended to modify the structure of a table) command containing a field that is declared a foreign key. The parent key is given a name that is referenced within a constraint FOREIGN KEY.
Like most constraints, it can be a table or column constraint, in the form of a table, allowing multiple fields to be used as a single foreign key.
Table Constraint Syntax FOREIGN KEY:
FOREIGN KEY
The first column list is a comma-separated list of one or more columns in the table that will be created or modified by this command.
Pktable is a table containing the parent key. It can be a table that is being created or modified by the current command.
The second column list is the list of columns that will make up the parent key. The lists of the two columns must be compatible, i.e .:
- have the same number of columns
- in a given sequence, the first, second, third, etc., columns of the foreign key column list must have the same data types and sizes as the first, second, third, etc., columns of the parent key column list.
- the columns in the lists of both columns must not have the same name.
FOREIGN KEY Example 1
CREATE TABLE Student
(Kod_stud integer NOT NULL PRIMARY KEY,
Kod_spec integer NOT NULL,
Adres char (50),
Ball decimal),
FOREIGN KEY(Kod_spec) REFERENCES Spec (Kod_spec)
);
When using ALTER TABLE instead of CREATE TABLE, to apply the constraint FOREIGN KEY, the values specified in the foreign key and parent key must be in a referential integrity state. Otherwise, the command will be rejected.
Using constraint FOREIGN KEY table or column, you can omit the list of columns of the parent key if the parent key has a PRIMARY constraint KEY... Naturally, in the case of keys with many fields, the order of the columns in the foreign and primary keys must be the same, and in any case, the principle of compatibility between the two keys still applies.
FOREIGN KEY Example 2
CREATE TABLE Student (
Kod_stud integer NOT NULL PRIMARY KEY,
Fam char (30) NOT NULL UNIQUE,
Adres char (50),
Ball decimal),
Kod_spec integer REFERENCES Spec
);
Maintaining referential integrity requires some constraints on the values that can be represented in fields declared as foreign key and parent key. The parent key must be structured to ensure that each foreign key value matches one specified string. This means that it (the key) must be unique and not contain any NULL values.
This is not sufficient for the parent key if a requirement such as a foreign key declaration is met. SQL must be sure that no double values or null values have been introduced into the parent key. Therefore, you need to make sure that all fields that are used as parent keys have either a PRIMARY constraint KEY or a UNIQUE constraint like the NOT NULL constraint.
Referencing foreign keys only to primary keys is a good strategy. When foreign keys are used, they are associated with more than just the parent keys to which they refer; they are associated with a specific row in the table where that parent key will be found. By itself, the parent key does not provide any information that is not already present in the foreign key.
Since the purpose of the primary key is to identify the uniqueness of the row, this is a more logical and less ambiguous choice for a foreign key. For any foreign key that uses a unique key as the parent key, you must create a foreign key that uses the primary key of the same table for the same action. A foreign key, which has no other purpose other than concatenating strings, resembles a primary key used solely to identify strings, and is a good way to keep your database structure clear and simple. A foreign key can only contain values that are actually represented in the parent key or are empty (NULL). Attempts to enter other values in this key will be rejected.
FOREIGN KEY Example 3
CREATE TABLE payment (
sh_payout integer,
sh_eml integer,
date_payout date,
summ_payout real,
FOREIGN KEY(sh_eml) REFERENCES k_sotr2 (eid)
);
In this example FOREIGN KEY the sh_eml column is linked to the eid column from the k_sotr2 table.
Last update: 09.07.2017
Foreign keys are used to establish relationships between tables. The foreign key is set for columns from the dependent, subordinate table, and points to one of the columns from the main table. While it is common for a foreign key to point to a primary key from a related master table, this does not have to be a requirement. A foreign key can also point to some other column that has a unique value.
The general syntax for setting a foreign key at the column level is:
REFERENCES master_table (column_master_table)
To create a column-level foreign key constraint, the REFERENCES keyword is followed by the name of the related table and, in parentheses, the name of the related column that the foreign key will point to. Also, the FOREIGN KEY keywords are usually added, but in principle they do not need to be specified. The REFERENCES statement is followed by an ON DELETE statement and an ON UPDATE statement.
The general syntax for setting a foreign key at the table level is:
FOREIGN KEY (column1, column2, ... columnN) REFERENCES master_table (column_main_table1, column_main_table2, ... column_main_tableN)
For example, let's define two tables and link them using a foreign key:
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR (20) NOT NULL, LastName NVARCHAR (20) NOT NULL, Email VARCHAR (30) UNIQUE, Phone VARCHAR (20) UNIQUE); CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date);
The Customers and Orders tables are defined here. Customers is in charge and represents the customer. Orders is dependent and represents an order placed by a customer. This table is linked through the CustomerId column to the Customers table and its Id column. That is, the CustomerId column is a foreign key that points to the Id column from the Customers table.
Defining a foreign key at the table level would look like this:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
You can use the CONSTRAINT statement to specify a name for the foreign key constraint. Usually this name starts with the "FK_" prefix:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
In this case, the CustomerId foreign key constraint is named "FK_Orders_To_Customers".
ON DELETE and ON UPDATE
The ON DELETE and ON UPDATE statements can be used to set the actions to be taken, respectively, when a related row is deleted and modified from the main table. And to define the action, we can use the following options:
CASCADE: Automatically deletes or modifies rows from a dependent table when deleting or modifying related rows in the main table.
NO ACTION: Prevents any action on the dependent table when deleting or modifying related rows in the main table. That is, in fact, there are no actions.
SET NULL: Sets the foreign key column to NULL when deleting a related row from the master table.
SET DEFAULT: When deleting a related row from the master table, sets the foreign key column to the default value, which is set using the DEFAULT attributes. If no default value is specified for the column, NULL is used as the default.
Cascade deletion
By default, if a row from the main table by a foreign key is referenced by any row from the dependent table, then we will not be able to delete this row from the main table. First, we will need to remove all related rows from the dependent table. And if, when deleting a row from the main table, it is necessary that all related rows from the dependent table be deleted, then cascading deletion is applied, that is, the CASCADE option:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE)
The ON UPDATE CASCADE statement works in a similar way. Changing the value of the primary key will automatically change the value of its associated foreign key. But since primary keys, as a rule, change very rarely, and in principle it is not recommended to use columns with mutable values as primary keys, in practice the ON UPDATE expression is rarely used.
Setting to NULL
Setting the SET NULL option for a foreign key requires the foreign key column to be nullable:
CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
