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 entry, 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 can 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 the 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 relationship "Students". 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; we did not make any changes or additions to determine it. 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 grading table, 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.
Integrity is an important principle of database design. And one of its rules is link integrity. This means that a table's foreign key cannot refer to a 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 properly built 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 are also noteworthy and should be considered by developers.
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 \u200b\u200bcorrespond to a primary key in another table.
The relationship between the 2 table corresponds to 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;
FOREIGN KEY used to restrict links.
When all the values \u200b\u200bin one field of a table are represented in a field of another table, the first field is said to refer to the second. This indicates a direct relationship between the values \u200b\u200bof 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... 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 \u200b\u200bthat can be entered into the database to force the foreign key and parent key to conform to the principle of referential integrity. One of the restriction actions FOREIGN KEY is dropping values \u200b\u200bfor 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 as 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 list of one or more table columns, separated by commas, and 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. Lists of 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 \u200b\u200bspecified in the foreign key and parent key must be in referential integrity. Otherwise, the command will be rejected.
Using constraint FOREIGN KEY table or column, you can omit the parent key column list 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 restrictions on the values \u200b\u200bthat 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 (key) must be unique and not contain any NULL values.
This is not enough for a parent key if a requirement such as a foreign key is met. SQL must be sure that no double values \u200b\u200bor null values \u200b\u200bhave 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 table row where this 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 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 \u200b\u200bthat are actually represented in the parent key or are empty (NULL). Attempts to enter other values \u200b\u200bin 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: 02.07.2017
Databases can contain tables that are linked by various relationships. A relationship represents an association between entities of different types.
When allocating a relationship, the primary key table / master table and the dependent foreign key table / child table are selected. The child table depends on the parent.
Foreign keys are used to organize communication. A foreign key represents one or more columns from one table that is also a potential key from another table. The foreign key does not have to match the primary key from the main table. Generally though, the foreign key from the dependent table points to the primary key from the main table.
Relationships between tables are of the following types:
One to one
One to many
Many to many (Many to many)
One to one relationship
This type of relationship is not common. In this case, only one object of another entity can be associated with an object of one entity. For example, on some sites, a user may have only one blog. That is, there is a relationship one user - one blog.
Often this type of relationship involves splitting one large table into several small ones. The main parent table in this case continues to contain frequently used data, while the child dependent table usually stores data that is less frequently used.
In this respect, the primary key of the dependent table is at the same time a foreign key that refers to the primary key from the main table.
For example, the Users table represents users and has the following columns:
UserId (id, primary key)
Name (username)
And the Blogs table represents user blogs and has the following columns:
BlogId (id, primary and foreign key)
Name (blog name)
In this case, the BlogId column will store the value from the UserId column from the users table. That is, the BlogId column will act as both a primary and a foreign key.
One to many relationship
This is the most common type of relationship. In this type of relationship, multiple rows from the child table are dependent on one row in the parent table. For example, one blog can have multiple articles. In this case, the blogs table is the parent and the articles table is the child. That is, one blog - many articles. Or, another example, a football team may have several players. And at the same time, one footballer can only play in one team at a time. That is, one team - many players.
For example, let's say there is an Articles table that represents blog posts and has the following columns:
ArticleId (id, primary key)
BlogId (foreign key)
Title (article title)
Text (article text)
In this case, the BlogId column from the articles table will store the value from the BlogId column from the blogs table.
Many-to-many relationship
With this type of relationship, one row from table A can be linked to many rows from table B. In turn, one row from table B can be linked to multiple rows from table A. A typical example is students and courses: one student can attend several courses , and accordingly several students can enroll in one course.
Another example is articles and tags: multiple tags can be defined for one article, and one tag can be defined for several articles.
But in SQL Server, at the database level, we cannot establish a direct many-to-many relationship between two tables. This is done through an auxiliary staging table. Sometimes the data from this staging table is a separate entity.
For example, in the case of articles and tags, let's have a Tags table, which has two columns:
TagId (id, primary key)
Text (tag text)
Also, let's have a staging table ArticleTags with the following fields:
TagId (id, primary and foreign key)
ArticleIdId (id, primary and foreign key)
Technically, we get two one-to-many relationships. The TagId column from the ArticleTags table will reference the TagId column from the Tags table. And the ArticleId column from the ArticleTags table will refer to the ArticleId column from the Articles table. That is, the TagId and ArticleId columns in the ArticleTags table represent a composite primary key and are also foreign keys for linking to the Articles and Tags tables.
Referential data integrity
When changing primary and foreign keys, you should observe such an aspect as referential data integrity (referential integrity). Its main idea is for two tables in the database that store the same data to maintain consistency. Data integrity represents well-formed relationships between tables with the correct establishment of links between them. In what cases data integrity can be violated:
Removal anomaly (deletion anomaly). Occurs when a row is deleted from the main table. In this case, the foreign key from the dependent table continues to refer to the deleted row from the main table
Insertion anomaly (insertion anomaly). Occurs when a row is inserted into a dependent table. In this case, the foreign key from the dependent table does not match the primary key of any of the rows from the main table.
Update anomalies (update anomaly). With such an anomaly, several rows of the same table may contain data that belongs to the same object. Changing data in one row may cause it to conflict with data in another row.
Removal anomaly
To resolve the delete anomaly for a foreign key, one of two constraints should be imposed:
If a row from a dependent table necessarily requires a row from the main table, then a cascading delete is set for the foreign key. That is, when a row is deleted from the main table, the associated row (s) are deleted from the dependent table.
If a row from a dependent table allows no relationship with a row from the main table (that is, such a relationship is optional), then the foreign key is set to NULL when the related row is deleted from the main table. However, the foreign key column must be nullable.
Insertion anomaly
To resolve an insert anomaly when data is added to a dependent table, the column that represents the foreign key must be nullable. And thus, if the object being added has no connection with the main table, then the foreign key column will have a NULL value.
Update anomalies
Normalization is applied to resolve the update anomaly problem, which will be discussed later.
In this topic, using the example of two tables, the basic concepts of relational databases are defined, namely:
- primary key;
- external key;
- simple and compound key;
- attitude, types of relationships;
- artificial and natural keys;
- master and detail tables.
Input data
Let the database of employees of the enterprise be given, which consists of two tables. The first table contains data about the employee. The second table contains information about the employee's wages.
The tables are structured as follows.
"Worker"... Contains data about the employee "Salary"... Contains information about the wages of employees.
Question answer
1. What is the primary key in a database table? What are primary keys used for?
When working with tables in relational databases, it is desirable (necessary) that each table has a so-called primary key.
Primary key Is a field that is used to ensure the uniqueness of the data in the table. This means that the value (information) in the primary key field in each row (record) of the table can be unique.
Uniqueness is necessary to avoid ambiguity, when it is not known which table record can be accessed if there are duplicate records in the table (two records have the same values \u200b\u200bin all fields of the table).
Example. For the "Employee" table, you can enter an additional field that will be the primary key. However, the field (attribute) "Personnel number" also provides uniqueness. Since, theoretically, there can be no two identical personnel numbers. In practice, there may be cases that the same personnel number will be entered by mistake and the values \u200b\u200bof all fields in the table will match. As a result, there will be two identical records in the table. To avoid such an error, it is better to create an additional counter field in the table, which will ensure uniqueness.
Also for the table "Salary" you can enter an additional field, which will be the primary key.
2. What is a relationship (relationship) between tables (relationship)? Example
Tables in a relational data model can have relationships with each other. These connections are called relationships. For the tables "Employee" and "Salary", you can establish a link by the "Personnel number" field.
Example. Let's analyze the tables "Employee" and "Salary". In these tables, you can establish a relationship between tables based on the Personnel Number field. That is, the relationship between tables is based on the "Personnel number" field (attribute).
This means the following. If you need to find the accrued wages in the "Salary" table for the employee Ivanov I.I., then you need to do the following:
- find the personnel number of the employee Ivanov I.I. in the table "Employee". The personnel number is 7585;
- in the table "Salary" find all values \u200b\u200bthat are equal to 7585 (personnel number);
- select from the "Salary" table all the values \u200b\u200bof the "Accrued" field that correspond to the personnel number 7585.
Figure: 1. Illustration of the relationship between tables. Personnel number 2145 of the "Employee" table is displayed in the "Salary" table
Figure: 2. Relationship (relationship) between table fields
3. What is a foreign key? Example
The concept of "foreign key" is important when considering related tables.
External key Is one or more fields (attributes) that are primary in another table and whose value is replaced by the values \u200b\u200bof the primary key of another table.
Example. Let there be a relationship between the tables "Employee" and "Salary" in the "Personnel number" field. In this case, the “Personnel number” field of the “Employee” table can be the primary key, and the “Personnel number” field of the “Salary” table can be a foreign key. This means that the values \u200b\u200bof the "Personnel number" field of the "Salary" table are replaced by the values \u200b\u200bof the "Personnel number" field of the "Employee" table.
4. What is a recursive foreign key?
Recursive foreign key Is a foreign key that refers to the same table to which it belongs. In this case, the field (attribute) that corresponds to the foreign key is the key of the same relationship (relationship).
5. Can primary and foreign keys be simple or composite (complex)?
Primary, secondary and foreign keys can be simple or composite (complex). Simple Keys Are keys that contain only one field (one attribute). Composite (complex) keys are keys that contain multiple fields (attributes).
6. What is the difference between artificial and natural key? Example
Natural key provides uniqueness from the very essence of the domain. There are cases when the values \u200b\u200bof records of some field (s) of the table are unique. This field can be a natural key.
Artificial key additionally introduced to provide unique values. Most often, the artificial key is a field of type counter (counter). In such a field, when a new record (row) is added to the table, the counter value is increased by 1 (or another value). If a record is deleted from the table, then the maximum value of the row counter is no longer decremented, but remains as it is. As a rule, everything is monitored by the database management system.
Example. In the table "Employee" the natural key is the field (attribute) "Personnel number". The "Personnel number" field is unique in itself, since there cannot be two employees with the same personnel number.
In the "Salary" table, the value in all four fields can be accidentally repeated. Therefore, here it is advisable to add an additional counter field, which will be an artificial key. In this case, the table "Salary" with an additional field may look like the following:
where the "Number" field is an artificial key that ensures uniqueness.
7. What are the ways to choose a primary key?
There are 3 ways to choose a primary key:
- use the increment field (counter field) as an artificial key;
- select one field from the data that can provide uniqueness;
- select several fields from the data that can provide uniqueness. In this case, the key will also be called complex (composite).
8. What do the terms “master table” and “detail” mean?
If there is a relationship between the tables, then one of them can be the master (master), and the other subordinate (detail). The main table displays all the records that fit in it. The subordinate table displays only those records that correspond to the value of the key of the main table, which is currently active (current). If the current record of the master table changes, then the set of available records of the subordinate table changes.
Example. If we consider the tables "Employee" and "Salary", then the table "Employee" is the main one, and the table "Salary" is subordinate.
9. What types of relationships (links) exist between tables?
There are 4 main types of relationships between tables:
- "one to one"... In this case, each record of one table corresponds only to one record of another table;
- One-to-many... This is when one record of the master table corresponds to several records of the subordinate table (detail). That is, each record, which is the primary key of one table, corresponds to several records of the related table;
- "Many to one"... This is when several records of the main table correspond to one record of the subordinate table;
- "Many to many"... This is when there are several related records in both tables.
Example. If we consider the relationship between the tables "Employee" and "Salary", then this relationship is of the "one to many" type. The "Employee" table is the main one. The table "Salary" is subordinate.
