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.
In a database, the primary key of a table is one of its columns (Primary key). Let's look at an example of what this looks like. Let's imagine a simple attitude of university students (let's call it "Students").
We need to uniquely identify a student using one column. To do this, the information in this column must be unique for each entry. But the available data in this regard does 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 the same faculty. The primary key in the database is used to precisely identify the required row in a relationship. Most often, a numeric field is used in this capacity, automatically increasing as a record is entered (auto-incrementing identifier column).
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 consisting 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 |
| Krasnov L.V. | 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 the person.
So, a primary key in a database is one or more columns of a table that allows one to uniquely identify a row in that relationship. What is it for?
Let's return to the first example with the relation "Students". In addition to this relationship, the database also stores other information, for example, the performance of each student. In order not to repeat all the information that is already contained in the database, they use a key, referring to the desired record. It looks like this.
In the two example relationships we see an ID field. These are the primary keys in the database for these tables. As you can see, the academic record contains only links to these fields from other tables without the need to indicate all the information from them.
How is the primary key of a database table determined? The two examples we looked at - "Students" and "Bank Clients" - illustrate the concepts of natural and surrogate keys. In the table of bank clients, we defined a key consisting of the fields “Number” and “Passport Series”, using already existing columns. This key is called natural; we did not make any changes or additions to determine it. In the case of the "Students" relation, no single field or combination of fields gave us uniqueness. This forced us to enter an additional student code field. This key is called a surrogate key, for which we added another service column to the table. This column does not contain any useful information and serves only to identify records.
All of the above brings us to the foreign key and database integrity. Foreign key is a field that refers to the Primary key of a foreign relationship. In the progress table, these are the “Student” and “Discipline” columns. Their data refers us to external tables. That is, the “Student” field in the “Performance” relation is a Foreign key, and in the “Student” relation it is the primary key in the database.
An important principle for constructing databases is their integrity. And one of its rules is referential integrity. This means that a foreign key of a table cannot refer to a non-existent Primary key of another relation. You cannot delete a record with code 1000 - Ivanov Ivan from the Student relationship if it is referenced by a record from the academic performance table. In a properly constructed database, when you try to delete it, you will receive an error that this field is in use.
There are other groups of integrity rules, as well as other database restrictions, that also deserve attention and should be taken into account by developers.
Earlier in this book, we pointed out certain relationships that exist between certain fields of typical tables. The snum field of the Customers table, for example, corresponds to the snum field in the Sellers table and the Orders table. The cnum field of the Customers table also corresponds to the cnum field of the Orders table. We called this type of relationship reference integrity; and during the discussion, you saw how it can be used.
In this chapter, you'll explore reference integrity in more detail and learn all about the constraints you can use to maintain it. You will also see how this limitation applies when you use DML modification commands. Because reference integrity involves relating fields or groups of fields, often across different tables, this action can be somewhat more complex than other constraints. For this reason, it's good to be fully familiar with it, even if you don't plan to create tables. Your modification commands can be made more efficient by using a reference integrity constraint (as with other constraints, but a reference integrity constraint can affect tables other than those on which it is defined), and certain query functions, such as joins, are iteratively structured in terms reference integrity relationships (as emphasized in Chapter 8).
When one field in a table refers to another, it is called a foreign key; and the field to which it refers is called the parent key. So the snum field of the Customers table is a foreign key, and the snum field it refers to in the Vendors table is the parent key.
Likewise, the cnum and snum fields of the Orders table are foreign keys that refer to their parent keys named in the Customers table and the Vendors table. The names of the foreign key and the parent key do not have to be the same, it is just a convention that we follow to make the join clearer.
You can see this with an example. The foreign key snum in the Customers table has the value 1001 for the rows Hoffman and Clemens. Let's assume that we had two rows in the Vendors table with a field value of snum = 1001. How do we know which of the two vendors the customers Hoffman and Clemens were assigned to? Likewise, if there are no such rows in the Vendors table, we'll end up with Hoffman and Clemens assigned to a vendor that doesn't exist!
In fact, a given foreign key value can only refer to one parent key value without the reverse being possible: i.e. any number of foreign keys can refer to a single parent key value. You can see this in the typical tables of our examples. Both Hoffman and Clemens are assigned to Peel, so both of their foreign key values are the same as the same parent key, which is a good thing. A foreign key value must reference only one parent key value, but a parent key value can be referenced by any number of foreign key values. As an illustration, foreign key values from the Customers table that match their parent key in the Sellers table are shown in Figure 19.1. For convenience, we have not taken into account gender not relevant to this example.
Like most constraints, it can be a table or column constraint, in table form allowing multiple fields to be used as a single foreign key.
* They must have the same number of columns.
* In this 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 should not have the same names, although we used this method in our examples to make the relationship clearer.
From the point of view of maintaining database integrity, internal interrupts (or exceptions) are of course undesirable. If you allow them and at the same time want to maintain the integrity of your database, you can declare the snum and cnum fields in the Orders table as independent foreign keys of these fields in the Vendors table and the Customers table, respectively. In fact, using sex snum in the Order table as we did is not necessary, although it is useful to do so for variety. The cnum field linking each customer order in the Customer table, in the Order table, and in the Customer table must always be shared in order to find the correct snum field for that order (without allowing any exceptions). This means that we are recording a piece of information - which customer is assigned to which vendor - twice, and additional work will need to be done to make sure that both versions are consistent. If we don't have a foreign key constraint as stated above, this situation will be especially problematic because each order will need to be checked manually (along with the query) to ensure that the corresponding seller credited each corresponding sale. Having this type of information redundancy in your database is called denormalization, which is undesirable in an ideal relational database, although in practice it can be resolved. Demoralization can cause some queries to run faster, since a query on one table is always much faster than a query on a join.
Since the question of changing parent key values is raised, the answer, as defined by ANSI, is even simpler, but perhaps somewhat more limited: any parent key value referenced by a foreign key value cannot be deleted or changed. This means, for example, that you cannot remove a customer from the Customers table while it still has orders in the Orders table. Depending on how you use these tables, this can be either desirable or a hassle. However, this is certainly better than having a system that allows you to delete a customer with current orders and leave the Orders table referencing non-existent customers. The point of this restriction system is that the creator of the Orders table, using the Customers table and the Sellers table as parent keys, can impose significant restrictions on actions in these tables. For this reason, you will not be able to use a table that you do not control (that is, you did not create it and you are not its owner) until the owner (creator) of that table specifically grants you the right to do so (as explained in Chapter 22).
Even within these three categories, you may not want to handle all modification commands in this way. INSERT, of course, is irrelevant. It puts the new values of the parent key into the table so that none of those values can be called at the moment. However, you may want to allow modifications to be cascaded without deletions, and vice versa. A better situation might be one that allows you to define any of the three categories, independent of the UPDATE and DELETE commands. We will therefore refer to the update effects and delete effects, which determine what happens if you issue an UPDATE or DELETE command on a parent key. These effects we talked about are called: RESTRICTED changes, CASCADES changes, and NULL changes.
For the sake of completeness of the experiment, let's assume that you have a reason to change the snum field of the Vendors table in the case where our Vendors table changes partitions. (Usually changing primary keys is not something we recommend doing in practice. It's just another reason for existing primary keys that don't know how to do anything other than act as primary keys: they shouldn't change.) When you change the merchant number, you want all of its customers to be saved. However, if this salesperson leaves his firm or company, you may not want to remove his customers while removing him from the database. Instead, you'll want to make sure that the customers are assigned to someone else. To do this you must specify UPDATE with a Cascading effect, and DELETE with a Limited effect.
CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople, UPDATE OF Salespeople CASCADES, DELETE OF Salespeople RESTRICTED); If you now try to remove Peel from the Vendors table, the command will not be valid until you change the sex snum value of the Hoffman and Clemens customers for another assigned vendor. On the other hand, you can change the sex snum value for Peel to 1009, and Hoffman and Clemens will be automatically changed as well.
The onum field will be the primary key of Cityorders. All floors in Cityorders must have restrictions when compared with the Customers and Orders tables. It is possible that the parent keys in these tables already have appropriate restrictions.
2. Let's complicate the problem. Redefine the Orders table as follows: add a new column called prev that will be identified for each order, the onum field of the previous order for that current customer.
In this topic, using two tables as an example, the basic concepts of relational databases are defined, namely:
Let a database of enterprise employees be given, which consists of two tables. The first table contains data about the employee. The second table contains information about the employee's salary.
The tables have the following structure.
"Worker". Contains employee data "Salary". Contains information about employee salaries.
When working with tables in relational databases, it is desirable (necessary) for each table to have a so-called primary key.
Primary key is a field that is used to ensure the uniqueness of 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 in all fields of the table).
Example. For the “Employee” table, you can enter an additional field, which will be the primary key. However, the “Personnel number” field (attribute) also ensures uniqueness. Since, theoretically, there cannot be two identical personnel numbers. In practice, there may be cases where the same personnel number is entered by mistake and the values of all table fields are the same. As a result, two identical records will appear in the table. To avoid such an error, it is better to create an additional counter field in the table, which will ensure uniqueness.
You can also enter an additional field for the “Salary” table, which will be the primary key.
Tables in the relational data model can have relationships with each other. Such connections are called relationships. For the “Employee” and “Salary” tables, you can establish a connection using the “Personnel Number” field.
Example. Let’s analyze the “Employee” and “Salary” tables. In these tables, you can establish a relationship between tables based on the Personnel Number field. That is, the connection between tables occurs on the basis of the field (attribute) “Personnel number”.
This means the following. If you need to find accrued wages in the “Salary” table for the employee Ivanov I.I., then you need to perform the following steps:
Rice. 1. Illustration of the relationship between tables. Personnel number 2145 of the “Employee” table is displayed in the “Salary” table
Rice. 2. Connection (relationship) between table fields
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 of the primary key of another table.
Example. Let there be a relationship between the “Employee” and “Salary” tables 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 of the “Personnel number” field of the “Salary” table are replaced by the values of the “Personnel number” field of the “Employee” table.
Recursive foreign key is a foreign key that references 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 (link).
Primary, secondary and foreign keys can be either simple or compound (complex). Simple keys– these are keys that contain only one field (one attribute). Composite(complex) keys are keys that contain several fields (attributes).
Natural key provides uniqueness from the very essence of the subject area. There are cases when the values of records of some field (fields) of the table are unique. This field can be a natural key.
Artificial key is added additionally to ensure unique values. Most often, the artificial key is a field of the counter type. In such a field, when a new record (row) is added to the table, the counter value increases by 1 (or another value). If a record is deleted from the table, the maximum value of the row counter is no longer reduced, but remains as is. Typically, this is all monitored by a database management system.
Example. In the “Employee” table, 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 may be accidentally repeated. Therefore, here it is advisable to add an additional counter field, which will be an artificial key. In this case, the “Salary” table with an additional field may look something like this:
where the “Number” field is an artificial key that ensures uniqueness.
There are 3 ways to select a primary key:
If there is a relationship between tables, then one of them can be the main one (master), and the other subordinate (detail). The main table displays all the records that fit into it. The slave table displays only those records that match the value of the main table key, which is currently active (current). If the current record of the main table changes, then the set of available records of the slave table changes.
Example. If we consider the “Employee” and “Salary” tables, then the “Employee” table is the main one, and the “Salary” table is a subordinate one.
There are 4 main types of relationships between tables:
Example. If we consider the relationship between the “Employee” and “Salary” tables, then this relationship is of the “one-to-many” type. The “Employee” table is the main one. The “Salary” table is a subordinate table.
IT APPLIES TO: SQL Server (since 2016)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse
Primary keys and foreign keys are two types of constraints that can be used to ensure data integrity in SQL Server tables. These are important database objects.
This topic is covered in the following sections.
Primary Key Constraints
Foreign Key Constraints
Related tasks
Typically a table has a column or combination of columns that contains values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and provides integrity to the entity of the table. Primary key constraints are often defined on an identity column because they ensure that the data is unique.
When you set a primary key constraint on a table in a component, the Database Engine ensures that the data is unique by automatically creating a unique index on the primary key columns. This index also provides fast access to data when using the primary key in queries. If a primary key constraint is defined on more than one column, values can be duplicated within a single column, but each combination of values from all columns in the primary key constraint definition must be unique.
As shown in the following figure, the columns ProductID And VendorID in the table Purchasing.ProductVendor form a compound primary key constraint for a given table. This ensures that each row in the table Product Vendor has a unique combination of meanings ProductID And VendorID. This prevents duplicate rows from being inserted.
A table can only have one primary key constraint.
The primary key cannot have more than 16 columns, and the total key length cannot exceed 900 bytes.
An index formed by a primary key constraint cannot cause the number of indexes in the table to exceed the limit of 999 nonclustered indexes and 1 clustered index.
If a primary key constraint does not specify whether the index is clustered or nonclustered, a clustered index is created if one does not exist on the table.
All columns with a primary key constraint must be defined as non-nullable. If nullability is not specified, then all columns with a primary key constraint are set to non-nullable.
If the primary key is defined on a column of a CLR user-defined data type, the implementation of that type must support binary sorting.
A foreign key (FK) is a column or combination of columns that is used to force a relationship between data in two tables to control the data that can be stored in the foreign key table. If one or more columns that contain the primary key for one table are referenced in one or more columns of another table, a foreign key link creates a relationship between the two tables. This column becomes a foreign key in the second table.
For example, table Sales.SalesOrderHeader linked to table Sales.SalesPerson using a foreign key because there is a logical relationship between sales orders and sales managers. Column SalesPersonID in the table Sales.SalesOrderHeader matches the primary key column in the table SalesPerson. Column SalesPersonID in the table Sales.SalesOrderHeader is a foreign key to the table SalesPerson. By establishing this relationship using a foreign key, the value for SalesPersonID cannot be inserted into table SalesOrderHeader, if it is not currently contained in the table SalesPerson.
The maximum number of tables and columns that a table can reference as foreign keys (outbound references) is 253. SQL Server 2016 increases the limit on the number of other tables and columns that can reference columns in the same table (inbound references), from 253 up to 10,000. (Requires compatibility level of at least 130.) Magnification is subject to the following limitations:
Exceeding 253 foreign key references is only supported for DML DELETE operations. UPDATE and MERGE operations are not supported.
Exceeding 253 foreign key references is currently not available for columnstore indexes, memory-optimized tables, Stretch database, or foreign key partitioned tables.
Unlike primary key constraints, when you create a foreign key constraint, a corresponding index is not automatically created. However, it is often necessary to create an index on a foreign key manually for the following reasons:
Foreign key columns are often used in join criteria when used together to query data from related tables. This is implemented by mapping a column or columns in a foreign key constraint in one table to one or more primary or unique key columns in another table. An index allows the Database Engine to quickly find related data in a foreign key table. However, creating an index is not mandatory. Data from two related tables can be combined even if there are no primary key or foreign key constraints defined between the tables, but a foreign key relationship between two tables indicates that the two tables are optimized to be used together in a query that uses the keys as criteria.
Foreign key constraints check for changes to primary key constraints on related tables.
The main purpose of a foreign key constraint is to control the data that can be stored in the foreign key table, but the constraint also controls changes to the data in the primary key table. For example, if you delete the row for sales manager from the table Sales.SalesPerson, whose ID is used in sales orders in the table Sales.SalesOrderHeader, the referential integrity of the two tables will be violated. Remote manager sales orders in table SalesOrderHeader will become invalid without a connection to the data in the table SalesPerson.
A foreign key constraint prevents this situation from occurring. A constraint enforces referential integrity in the following way: It prevents changes to data in the primary key table if such changes would invalidate a reference in the foreign key table. If you try to delete a row in a primary key table or change the value of that key, if you find that the deleted or changed primary key value has a corresponding value in a foreign key constraint in another table, the action will fail. To successfully modify or delete a row with a foreign key constraint, you must first delete the foreign key data in the foreign key table or change the data in the foreign key table that relates the foreign key to the data in another primary key.
By using cascading referential integrity constraints, you can define the actions that the Database Engine will take when a user attempts to delete or update a key that is pointed to by foreign keys that still exist. The following cascading actions can be defined.
NO ACTION
The Database Engine generates an error and then rolls back the delete or update operation on the row in the parent table.
CASCADE
The corresponding rows are updated or deleted from the referencing table if that row is updated or deleted from the parent table. The CASCADE value cannot be specified if the column is of type timestamp is part of a foreign or reference key. The ON DELETE CASCADE action cannot be specified on a table that has an INSTEAD OF DELETE trigger defined. The ON UPDATE CASCADE clause cannot be specified on tables that have INSTEAD OF UPDATE triggers defined.
SET NULL
All values that make up a foreign key are set to NULL when the corresponding row in the parent table is updated or deleted. To satisfy this restriction, foreign key columns must be nullable. Cannot be set on tables that have INSTEAD OF UPDATE triggers defined.
SET DEFAULT
All values that make up a foreign key are set to their default value when the corresponding row in the parent table is deleted or updated. To satisfy this constraint, all foreign key columns must have default definitions. If a column is nullable and the default value is not explicitly defined, the default value of the column becomes NULL. Cannot be set on tables that have INSTEAD OF UPDATE triggers defined.
The keywords CASCADE, SET NULL, SET DEFAULT, and NO ACTION can be combined in tables that have mutual reference relationships. If the Database Engine encounters the NO ACTION keyword, it will stop and rollback the associated CASCADE, SET NULL, and SET DEFAULT operations. If a DELETE statement contains a combination of the CASCADE, SET NULL, SET DEFAULT, and NO ACTION keywords, all CASCADE, SET NULL, and SET DEFAULT operations are performed before the Database Engine searches for the NO ACTION operation.
Cascading reference actions fire AFTER UPDATE or AFTER DELETE triggers as follows:
All cascading reference actions directly caused by the original DELETE or UPDATE statements are executed first.
If there are any AFTER triggers defined on tables that have changed, those triggers are fired after all cascading actions have completed. These triggers fire in the reverse order of cascading actions. If multiple triggers are defined for a single table, they are fired in random order unless the table's first and last triggers are selected. This order is determined by the procedure.
If cascading action sequences originate from a table that was the immediate target of a DELETE or UPDATE action, the order in which the action sequences fire triggers is not determined. However, one action sequence always fires all its triggers before the next one does.
An AFTER trigger on a table that was the immediate target of a DELETE or UPDATE action fires regardless of whether any rows have changed. In this case, no other tables are affected by cascading.
If one of the previous triggers performs DELETE or UPDATE operations on other tables, those operations can trigger their own sequence of cascading actions. These secondary action sequences are processed for each DELETE or UPDATE operation after all primary action sequence triggers have completed. This process can be repeated recursively for subsequent DELETE or UPDATE operations.
Performing CREATE, ALTER, DELETE, or other DDL operations inside triggers can cause DDL triggers to fire. This can lead to further DELETE or UPDATE operations, which will begin additional sequences of cascading actions and fire their triggers.
If an error occurs in any particular sequence of cascading reference actions, no AFTER triggers will be fired on that sequence, and DELETE or UPDATE operations generated by that sequence will be rolled back.
A table that has an INSTEAD OF trigger defined can also have a REFERENCES clause that specifies a specific cascading action. However, an AFTER trigger on a cascading action's target table can issue an INSERT, UPDATE, or DELETE statement on another table or view, which will fire an INSTEAD OF trigger on that object.
The following table lists common tasks associated with primary key and foreign key constraints.
And so, quietly, we approached a very important topic - primary and foreign keys. If the former are used by almost everyone, then the latter are somehow ignored. But in vain. Foreign keys are not a problem, they are a real help in data integrity.
We've already talked a lot about key fields, but we've never used them. The most interesting thing is that everything worked. This is an advantage, or maybe a disadvantage, of Microsoft SQL Server and MS Access databases. This trick will not work in Paradox tables and without a key field the table will be read-only.
To some extent, keys are constraints, and they could be considered in conjunction with the CHECK statement because the declaration occurs in a similar way and even uses the CONSTRAINT statement. Let's look at this process with an example. To do this, we will create a table of two fields “guid” and “vcName”. This sets the "guid" field as the primary key:
CREATE TABLE Globally_Unique_Data (guid uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (Guid))
The best part here is the CONSTRAINT line. As we know, after this keyword comes the name of the constraint, and the key declaration is no exception. To name a primary key, I recommend using a name like PK_name, where name is the name of the field that should become the primary key. The abbreviation PK comes from Primary Key.
After this, instead of the CHECK keyword, which we used in the constraints, there is a PRIMARY KEY operator. This is what indicates that we do not need a check, but a primary key. One or more fields that will make up the key are indicated in parentheses.
Remember that no two rows can have the same value in a key field, in which a primary key constraint is identical to a unique constraint. This means that if you make the field for storing the last name the primary key, then it will not be possible to write two Ivanovs with different names in such a table. This violates the primary key constraint. This is why keys are constraints and are declared in the same way as a CHECK constraint. But this is not true only for primary keys and secondary keys with uniqueness.
In this example, the primary key is a field of type uniqueidentifier (GUID). The default value for this field is the result of the NEWID server procedure.
Attention
Only one primary key can be created for a table
To simplify the examples, it is advisable to use a numeric type as a key, and if the database allows, it will be better if it is of the “autoincrement” type (automatically increasing/decreasing number). In MS SQL Server this field is IDENTITY, and in MS Access it is a “counter” type field.
The following example shows how to create a product table with an auto-incrementing integer field as the primary key:
CREATE TABLE Products (id int IDENTITY(1, 1), product varchar(50), Price money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id))
It is this type of key that we will use most often, because the key field will store numbers that are easy to understand and will be easier and more visual to work with.
A primary key can consist of more than one column. The following example creates a table in which the fields "id" and "Product" form the primary key, which means that a unique index will be created on both fields:
CREATE TABLE Products1 (id int IDENTITY(1, 1), Product varchar(50), Price money, Quantity numeric(10, 2), CONSTRAINT PK_id PRIMARY KEY (id, [Product name]))
Very often programmers create a database with a key field in the form of an integer, but at the same time the task clearly states that certain fields must be unique. Why not immediately create a primary key from those fields that must be unique and there will be no need to create separate solutions for this problem.
The only drawback of a multi-column primary key is the problem of creating relationships. Here you have to get out of it using various methods, but the problem can still be solved. You just need to enter a field of type uniqueidentifier and make a connection using it. Yes, in this case we get a unique primary key and a field of type uniqueidentifier, but this redundancy as a result will not be greater than the same table where the primary key is uniqueidentifier, and a uniqueness constraint is set on the fields that must be unique. What to choose? Depends on the specific task and what you are more comfortable working with.
A foreign key is also a CONSTRAINT constraint and represents the relationship between two tables. Let's say you have two tables:
One person can have several phones, so we divided the data storage into different tables. Figure 1.4 visually shows the relationship between two tables. If you have already worked with linked tables, then this will be enough for you. If you are hearing about connections for the first time, then let’s try to take a closer look at the problem.
For example, let's take a table of three people. Table 1.3 shows the contents of the "Names" table. There are only three lines and each has its own unique master key. For uniqueness, when we create a table, we will make the key an automatically increasing field.
Table 1.3 Contents of the Names table
Table 1.4. Contents of the Phones table
Table 1.4 contains five telephone numbers. The master key field also contains a unique master key, which can also be made automatically incremented. A secondary key is a relationship to the primary key of the Names table. How does this connection work? Petrov has the number 1 as the primary key in the Names table. In the Phones table, in the secondary key, we look for the number 1 and get Petrov’s phone numbers. The same goes for the rest of the entries. Visually the connection can be seen in Figure 1.5.
This type of data storage is very convenient. If it were not possible to create related tables, then in the Names table we would have to enter all phone numbers into one field. This is inconvenient from the point of view of use, maintenance and data retrieval.
You can create several Names fields in a table, but the question arises - how many. One person can only have 1 phone, but I, for example, have 3, not counting work ones. A large number of fields leads to data redundancy.
You can create a separate row with the last name for each phone in the Names table, but this is only easy for such a simple example, when you only need to enter the last name and you can easily make several entries for Petrov with several phone numbers. What if there are 10 or 20 fields? So, the creation of two tables linked by a foreign key can be seen in Listing 1.6.
Listing 1.6. Creating tables linked by a foreign key
CREATE TABLE Names (idName int IDENTITY(1,1), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Phones (idPhone int IDENTITY(1,1), idName int, vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName) REFERENCES Names (idName))
Please review the listing contents carefully. It's quite interesting because it uses some of the operators we've already covered and an additional example would be helpful. For both tables, a key field is created, which comes first, is of type int and is automatically incremented, starting from 1 in increments of one. The key field is made the main key using a CONSTRAINT constraint.
In the description of the Phones table, the last line contains a new declaration for us, namely, the declaration of a foreign key using the FOREIGN KEY operator. As you can see, this is also a limitation and a little later you will see why. The table field that should be linked to another table is indicated in parentheses. After this comes the keyword REFERENCES (link), the name of the table with which the connection should be (Names) and in parentheses the name of the field ("idName"). Thus, we have made a connection, which is shown in Figure 1.4.
Attention!
A foreign key can only reference another table's primary key or a unique constraint. This means that after the REFERENCES keyword there must be a table name and only a primary key or a field with a UNIQUE constraint can be specified in parentheses. Other fields cannot be specified.
Now, if you can fill the tables with data. The next three teams add the three names we saw in Table 1.3:
INSERT INTO Names(vcName) VALUES("Petrov") INSERT INTO Names(vcName) VALUES("Ivanov") INSERT INTO Names(vcName) VALUES("Sidorov")
If you have already worked with SQL, you can add entries for the phone table. I'll omit these commands, but you can see them in the foreign_keys.sql file in the Chapter1 directory on the CD.
Our task now is to see what the restrictive actions of a foreign key are, let's figure it out. We have specified an explicit relationship between two fields in different tables. If you try to add a record to the phone table with an identifier in the "idName" field that does not exist in the field of the same name (the name could be changed to another) in the table with surnames, an error will occur. This will break the relationship between the two tables, and the foreign key constraint will not allow records to exist without the relationship.
The restriction also applies when changing or deleting records. For example, if you try to delete a row with the last name Petrov, a foreign key constraint error will occur. You cannot delete records that have externally related rows. First, you need to delete all phone numbers for this entry, and only after that will it be possible to delete the line with the surname Petrov.
When creating a foreign key, you can specify ON DELETE CASCADE or ON UPDATE CASCADE. In this case, if you delete Petrov's record from the Names table or change the identifier, then all records in the Phones table associated with Petrov's row will be automatically updated. Never. No, it needs to be written in capital letters: NEVER do this. Everything must be deleted or changed manually. If a user accidentally deletes an entry from the Names table, then the corresponding phones are also deleted. There's no point in creating a foreign key if half of its restrictions disappear! Everything must be done manually, and it is never recommended to change identifiers.
Deleting the tables themselves should also begin with the subordinate table, that is, with Phones, and only then can you delete the main Names table.
Finally, I’ll show you how to beautifully get a match between names and phone numbers from two tables:
SELECT vcName, vcPhone FROM Names, Phones WHERE Names.idName=Phones.idName
We will talk about such queries in more detail in Chapter 2. For now, I gave an example just so that you can see the power of related tables.
A table can contain up to 253 foreign keys, which is quite enough for even the most complex databases. Personally, I had to work with databases where the number of foreign keys did not exceed 7 per table. If it is more, then most likely the database is designed incorrectly, although there are exceptions.
The table itself can also have a maximum of 253 foreign keys. Foreign keys in a table are less common, usually no more than 3. Most often, a table can have many links to other tables.
A foreign key can reference the same table in which it is created. For example, you have a table of job titles in an organization, as shown in Table 1.5. The table consists of three fields: primary key, foreign key and job title. Any organization may have many positions, but it would be quite logical to display their names and subordination structure in one table. To do this, the foreign key must be associated with the primary key of the position table.
Table 1.5. Table with internal link
As a result, we get that the general director has a zero foreign key, i.e. this position stands at the head of all others. For the commercial director and general affairs director, the foreign key points to the general director's row. This means that these two positions report directly to the CEO. And so on.
Let's see how we can create all this as an SQL query:
CREATE TABLE Positions (idPosition int IDENTITY(1,1), idParentPosition int, vcName varchar(30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERENCES Positions (idPosition))
As you can see, the foreign key simply references the same table that we are creating. On the CD, in the Chapter1 directory, you can see in the file foreign_keys_to_self.sql an example of creating this table, filling it with data and displaying positions taking into account their subordination. In the next chapter we will look at the possibility of working with such tables in more detail.
So far, we have looked at the classic relationship, when one row of the main data table corresponds to one row from the related table. This relationship is called one-to-many. But there are other connections, and now we will look at another one - one to one, when one record in the main table is connected to one record of another. To implement this, it is enough to link the primary keys of both tables. Since primary keys cannot be repeated, only one row can be related in both tables.
The following example creates two tables that have a primary key relationship:
CREATE TABLE Names (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES Names (idName))
Only one of the tables needs a foreign key. Since the relationship is one to one, it does not matter in which table to create it.
The most complex relationship is many-to-many, where many records from one table match many records from another table. To implement this, two tables are not enough; three tables are needed.
First we need to understand when a many-to-many relationship can be used? Let's say you have two tables: a list of house residents and a list of phone numbers. One apartment may have more than one number, which means that one surname may have two telephone numbers. It turns out that there is a one-to-many relationship. On the other hand, there can be two families in one apartment (a communal apartment or just a tenant who uses the owner’s phone), which means that the connection between the phone and the resident is also one to many. And the most difficult option is to have two telephones in a communal apartment. In this case, both numbers are used by several residents of the apartment. So it turns out that “many” families can use “many” phones (many-to-many communication).
How to implement a many-to-many relationship? At first glance, this is impossible in the relational model. About 10 years ago, I spent a long time looking for different options and as a result I simply created one table that was filled with redundant data. But one day, I was given one task, thanks to which an excellent solution emerged from the conditions - I needed to create two tables of apartment residents and telephone numbers and implement only a primary key in them. Foreign keys are not needed in this table. But the connection between the tables should be through a third, connecting table. At first glance, this is difficult and unclear, but once you understand this method, you will see the full power of this solution.
Tables 1.6 and 1.7 show examples of last name and telephone tables, respectively. And Table 1.8 shows the linking table.
Table 1.6. Surname table
Table 1.7. Phone table
Table 1.8. Phone table
Let's now see what the data search logic will be like in a many-to-many relationship. Let's say that we need to find all the phones that belong to Ivanov. Ivanov’s primary key is equal to 1. We find in the linking table all records for which the “Relationship with name” field is equal to 1. These will be records 1 and 2. In these records in the “Relationship with telephone” field there are identifiers 1 and 2, respectively, and This means that Ivanov owns the numbers from the telephone table, which are located in lines 1 and 2.
Now let’s solve the inverse problem - determine who has access to the phone number 567575677. This number in the phone table has key 3. We are looking for all records in the linking table, where in the “Phone connection” field it is equal to 3. These are records with numbers 4 and 5, which in the "Name Link" field contain values 2 and 3, respectively. If you now look at the table of surnames, you will see Petrov and Sidorov at numbers 2 and 3. This means that these two residents use the phone number 567575677.
Review all three tables and make sure you understand which phone numbers belong to which residents and vice versa. If you see this connection, you will understand that it is as simple as three pennies and you can quickly implement it in your projects.
CREATE TABLE Names (idName uniqueidentifier DEFAULT NEWID(), vcName varchar(50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID(), vcPhone varchar(10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone)) CREATE TABLE LinkTable (idLinkTable uniqueidentifier DEFAULT NEWID(), idName uniqueidentifier, idPhone uniqueidentifier, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES Phones (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName REF) ERENCES Names (idName) )
The linking table has two foreign keys that link to the names and phone tables and one primary key that ensures that records are unique.
I chose the GUID field as the primary key because it is more convenient for solving this particular problem. The fact is that we need to insert records into two tables and in both cases we need to specify the same key. The GUID value can be generated and then used when inserting data into both tables.
You can also use an automatically increasing field as a key, but in this case the problem is a little more difficult to solve, or rather, it is inconvenient to solve the problem. For example, when adding a phone number, you must first insert the corresponding row into the table, then find it, determine the key that was assigned to the row, and then make the connection.
At this stage we are limited to just creating tables, but in section 2.8 we will return to this topic and learn and learn how to work with related tables. Working with a one-to-one and one-to-many relationship is not very different, because only two tables are involved in this scheme. Many-to-many relationships are a little more complicated because of the linking table, so we'll cover that separately in Section 2.27.
