• Databases. Relational databases Key field foreign key data integrity

    Last update: 04/27/2019

    Foreign keys allow you to establish relationships between tables. A foreign key is set on columns from a 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 main_table (main_table_column1, main_table_column2, ... main_table_columnN)

    To create a foreign key constraint, after FOREIGN KEY you specify the table column that will represent the foreign key. And after the REFERENCES keyword, the name of the related table is indicated, and then in parentheses the name of the related column to which the foreign key will point. After the REFERENCES expression there are ON DELETE and ON UPDATE expressions, which specify the action when deleting and updating a row 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 the main one and represents the client. Orders is dependent and represents the order placed by the 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.

    You can use the CONSTRAINT operator to specify a name for a 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

    Using the ON DELETE and ON UPDATE statements, you can set the actions that are performed when a related row is deleted or modified, respectively, from the main table. The following options can be used as an action:

      CASCADE: Automatically deletes or modifies rows from a dependent table when related rows in the master table are deleted or modified.

      SET NULL: When deleting or updating a related row from the main table, sets the foreign key column to NULL. (In this case, the foreign key column must support NULL setting)

      RESTRICT: Rejects the deletion or modification of rows in the main table if there are related rows in the dependent table.

      NO ACTION: same as RESTRICT.

      SET DEFAULT: When deleting a related row from the main table, sets the foreign key column to the default value that is specified using the DEFAULT attribute. Despite the fact that this option is available in principle, the InnoDB engine does not support this expression.

    Cascading deletion

    Cascading delete allows you to automatically delete all related rows from the dependent table when you delete a row from the main table. To do this, use the CASCADE option:

    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 expression works in a similar way. When you change the value of a primary key, the value of its associated foreign key automatically changes. However, since primary keys change very rarely, and it is generally not recommended to use columns with mutable values ​​as primary keys, the ON UPDATE expression is rarely used in practice.

    Setting NULL

    When you set the SET NULL option for a foreign key, the foreign key column must be NULL-allowable:

    CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);

    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).

    FOREIGN KEY AND PARENT KEY

    When all the values ​​in one table field are represented in a field in another table, we say that the first field refers to the second. This indicates a direct relationship between the values ​​of the two fields. For example, each of the customers in the Customers table has a snum field that points to the seller assigned in the Sellers table. For each order in the Orders table, there is one and only this seller and one and only this customer. This is displayed using the snum and cnum fields in the Orders table.

    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.

    MULTI-COLUMN FOREIGN KEYS

    In reality, a foreign key does not necessarily consist of only one gender. Like a primary key, a foreign key can have any number of fields, which are all treated as a single unit. The foreign key and the parent key it refers to must, of course, have the same number and gender type, and be in the same order. Foreign keys consisting of one gender - those that we used exclusively in our standard tables, are the most common. To keep our discussion simple, we will often refer to a foreign key as a single column. This is no coincidence. If this is not noted, anyone will say about a field that is a foreign key that it also belongs to a group of fields that is a foreign key.

    THE MEANING OF FOREIGN AND PARENT KEYS

    When a field is a foreign key, it is related in some way to the table it refers to. What you are essentially saying is "every value in this field (the foreign key) is directly tied to a value in another field (the parent key)." Each value (each row) of a foreign key must unambiguously refer to one and only that value (row) of the parent key. If this is the case, then in fact your system will, as they say, be in a state of reference integrity. 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!

    It is clear that each value in a foreign key must be represented once, and only once, in the parent key.

    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.

    FOREIGN KEY LIMITATION

    SQL maintains referential integrity with the FOREIGN KEY constraint. Although the FOREIGN KEY constraint is a new feature in SQL, it does not yet make it universal. In addition, some of its implementations are more complex than others. This function should limit the values ​​that you can enter into your database to force the foreign key and parent key to comply with referential integrity. One of the actions of a Foreign Key constraint is to discard values ​​for fields constrained as a foreign key that are not already represented in the parent key. This restriction also affects your ability to change or delete parent key values ​​(we'll discuss this later in this chapter).

    HOW CAN FIELDS BE REPRESENTED AS FOREIGN KEYS

    You use a FOREIGN KEY constraint in a CREATE TABLE (or ALTER TABLE) command that contains the field that you want to declare as a foreign key. You give them a parent key that you will reference inside the FOREIGN KEY constraint. Placing this constraint in the command is the same as for the other constraints discussed in the previous chapter. Figure 19.1: Foreign Key of the Customer table with parent key

    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.

    FOREIGN KEY AS TABLE CONSTRAINT

    FOREIGN KEY table constraint syntax: FOREIGN KEY REFERENCES [ ] The first column list is a comma-separated list of one or more table columns that will be created or modified by this command. Pktable is the table containing the parent key. It can be a table that is created or modified by the current command. The second list of columns is the list of columns that will make up the parent key. The two column lists must be compatible, i.e.:

    * 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.

    Let's create a Customers table with the snum field defined as a foreign key referencing the Sellers table: CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY cname char(10), city char(10), snum integer, FOREIGN KEY (snum) REFERENCES Salespeople (snum ); Keep in mind that when using ALTER TABLE instead of CREATE TABLE, in order to apply the FOREIGN KEY constraint, the values ​​you specify in the foreign key and parent key must be in reference integrity state, otherwise the ALTER TABLE command will be rejected. - for its convenience, you will have to first formulate structural principles, such as reference integrity, in your system, whenever possible, every time.

    FOREIGN KEY AS COLUMN CONSTRAINT

    The option of limiting a column with a FOREIGN KEY constraint is also called a REFERENCES constraint, since it does not actually contain the words FOREIGN KEY, but simply uses the word REFERENCES, followed by the parent key, like this: CREATE TABLE Customers ( cnum integer NOT NULL PRIMARY KEY, cname char(10), city char(10), snum integer REFERENCES Salespeople (snum)); The above defines Customers.snum as a foreign key whose parent key is Salespeople.snum. This is equivalent to a table constraint like this: FOREIGN KEY (snum) REGERENCES Salespeople (snum)

    DO NOT SPECIFY LIST OF PRIMARY KEY COLUMNS

    By using a FOREIGN KEY constraint on a table or column, you can omit the parent key's column list if the parent key has a PRIMARY KEY constraint. Naturally, in the case of keys with many fields, the order of the columns in the foreign and primary keys must match, and, in any case, the principle of compatibility between the two keys still applies. For example, if we placed a PRIMARY KEY constraint in the snum field of the Sales table, we could use it as a foreign key in the Customers table (similar to the previous example) with this command: CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) , city char(10), snum integer REFERENCES Salespeople); This feature was built into the language to encourage you to use primary keys as parent keys.

    HOW REFERENCE INTEGRITY CONSTRAINTS THE VALUES OF A PARENT KEY

    Maintaining referential integrity requires some restrictions on the values ​​that can be represented in fields declared as a foreign key and a parent key. The parent key must be structured to ensure that each foreign key value corresponds to one specified row. This means that it (the key) must be unique and not contain any empty values ​​(NULL). This is not sufficient for the parent key if the same requirement is met as when declaring a foreign key. SQL must ensure that double values ​​or null values ​​are not entered into the parent key. Therefore, you must ensure that all fields that are used as parent keys have either a PRIMARY KEY constraint or a UNIQUE constraint, such as the NOT NULL constraint.

    PRIMARY KEY AS A UNIQUE FOREIGN KEY

    Linking your foreign keys to primary keys only, as we did in standard tables, is a good strategy. When you use foreign keys, you don't just associate them with the parent keys they refer to; you associate them with a specific table row where that parent key will be found. The parent key itself does not provide any information that is not already present in the foreign key. The meaning of, for example, sex snum as a foreign key in the Customers table is the relationship it provides, not to the value of sex snum to which it refers, but to other information in the Sales table, such as the names of the sellers, their locations, and so on . A foreign key is not simply a relationship between two identical values; this is a relationship, using these two values, between two rows of the table specified in the query. This snum field can be used to associate any information in a row from the Customers table with a reference row from the Sellers table - for example, to find out whether they live in the same city, who has a longer name, whether the seller has any other customers besides this one customers, and so on. Since the purpose of a primary key is to identify the uniqueness of a row, it is a more logical and less ambiguous choice for a foreign key. For any foreign key that uses a unique key as its parent key, you must create a foreign key that uses the same table's primary key for the same effect. A foreign key, which has no other purpose other than linking rows, is similar to a primary key used solely to identify rows, and is a good way to keep the structure of your database clear and simple, and therefore less complex.

    FOREIGN KEY CONSTRAINTS

    A foreign key, in particular, can only contain values ​​that are actually present in the parent key or are empty (NULL). Any attempt to enter other values ​​into this key will be rejected. You can declare a foreign key as NOT NULL, but this is not necessary, and in most cases, undesirable. For example, suppose you enter a customer without knowing in advance which salesperson he will be assigned to. The best way out in this situation is to use a NOT NULL value, which must be changed later to a specific value.

    WHAT HAPPENS IF YOU EXECUTE A MODIFICATION COMMAND

    Let's stipulate that all foreign keys created in our example tables are declared and enforced with foreign key constraints, as follows: CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10) , comm decimal); CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer, FOREIGN KEY (snum) REFERENCES Salespeople, UNIQUE (cnum, snum) ; CREATE TABLE Orders ( cnum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL, cnum integer NOT NULL snum integer NOT NULL FOREIGN KEY (cnum, snum) REFERENCES CUSTOMERS (cnum, snum);

    INCLUDING TABLE DESCRIPTIONS

    There are several attributes of such definitions that need to be discussed. The reason we decided to make the cnum and snum floors in the Orders table a single foreign key is to guarantee that for each customer contained in the orders, the seller crediting this order is the same as that indicated in the Customers table. To create such a foreign key, we would have to place a UNIQUE table constraint on two floors of the Customer table, even though it is not required by that table itself. As long as the cnum field in this table has a PRIMARY KEY constraint, it will be unique in any case, and therefore it is impossible to obtain another combination of the cnum field with some other field. Creating a foreign key in this way maintains the integrity of the database, even if it prevents you from interrupting internally by mistake and crediting any vendor other than the one assigned to that particular customer.

    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.

    EFFECT OF RESTRICTIONS

    How do such restrictions affect your ability and inability to use DML modification commands? For fields defined as foreign keys, the answer is quite simple: any values ​​you put into those fields with an INSERT or UPDATE command must already be present in their parent keys. You can place NULL values ​​in these fields, although NULL values ​​are not allowed in parent keys if they have a NOT NULL constraint. You can DELETE any rows with foreign keys without using the parent keys at all.

    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 Order table, using the Customer table and the Seller 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). There are some other possible parent key changing actions that are not part of ANSI but may be found in some commercial programs. If you want to change or delete the current reference value of a parent key, there are essentially three possibilities:

  • You can restrict, or prohibit, changes (in ANSI fashion) by specifying that changes to the parent key are restricted.
  • You can make a change in the parent key and thereby make changes in the foreign key automatic, which is called a cascading change.
  • You can make a change to the parent key, and set the foreign key to NULL, automatically (assuming NULLS is allowed in the foreign key), which is called a null foreign key change.

    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. The actual capabilities of your system should be within the strict ANSI standard - modification and deletion effects are both automatically limited - for the more ideal situation described above. To illustrate, we'll show a few examples of what you can do with a full range of modification and removal effects. Of course, modification and deletion effects, which are non-standard means, lack standard state syntax. The syntax we use here is easy to write and will serve to illustrate the functions of these effects later on.

    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 third effect is Empty (NULL) changes. It happens that when sellers leave a company, their current orders are not transferred to another seller. On the other hand, you want to cancel all orders automatically for customers whose accounts you delete. By changing the numbers of the seller or customer, you can simply transfer them to him. The example below shows how you can create an Order table using these effects. CREATE TABLE Orders (onum integer NOT NULL PRIMARY KEY, amt decimal, odate date NOT NULL cnum integer NOT NULL REFERENCES Customers snum integer REFERENCES Salespeople, UPDATE OF Customers CASCADES, DELETE OF Customers CASCADES, UPDATE OF Salespeople CASCADES, DELETE OF Salespeople NULLS); Of course, in a DELETE command with the effect of an Empty change on the Vendors table, the NOT NULL constraint must be removed from the snum field.

    FOREIGN KEYS THAT REFERENCE BACK TO THEIR SUBJECT TABLES

    As mentioned earlier, a FOREIGN KEY constraint can represent this private table as a parent key table. Far from being simple, this feature can come in handy. Let's assume that we have an Employees table with a manager field. This field contains the numbers of each employee, some of whom are also administrators. But since every administrator remains an employee at the same time, he will naturally also be represented in this table. Let's create a table where the employee number (a column named empno) is declared as the primary key, and the administrator, as a foreign key, will reference it: CREATE TABLE Employees (empno integer NOT NULL PRIMARY KEY, name char(10) NOT NULL UNIOUE , manager integer REFERENCES Employees); (Since the foreign key is the referenced primary key of the table, the column list can be excluded.) There is the content of this table: EMPNO NAME MANAGER _____ ________ _______ 1003 Terrence 2007 2007 Atali NULL 1688 McKenna 1003 2002 Collier 2007 As you can see, each of these( but not Atali), refers to another employee in the table as his administrator. Atali, which has the highest number in the table, must have its value set to NULL. This gives another principle of referential integrity. A foreign key that references back to a private table must allow values ​​= NULL. If this is not the case, how would you insert the first row? Even if this first row refers to itself, the value of the parent key must already be set when the foreign key value is entered. This principle will be true even if the foreign key refers back to the private table not directly but through a reference to another table, which then refers back to the foreign key table. For example, suppose our Sales table has an additional field that references the Customers table, so that each table references the other, as shown in the following CREATE TABLE statement: CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL, city char(10), comm declmal, cnum integer REFERENCES Customers); CREATE TABLE Customers (cnum integer NOT NULL PRIMARY KEY, cname char(10) NOT NULL, city char(10), rating integer, snum integer REFERENCES Salespeople); This is called a cross reference. SQL supports this in theory, but in practice it can be a problem. Whichever table of these two is created first is a reference table that does not yet exist for the other. In the interest of cross-referencing, SQL actually allows this, but neither table will be usable while both are in the process of being created. On the other hand, if the two tables are created by different users, the problem becomes even more difficult. Cross-referencing can be a useful tool, but it is not without ambiguity and dangers. The previous example, for example, is not entirely usable because it limits the seller to a single customer, and it is not necessary to use a cross-reference to achieve this. We recommend that you be careful in its use and analyze how your programs manage the effects of modification and deletion, as well as the processes of privileges and interactive query processing, before you create a cross-reference integrity system. (Privileges and interactive request processing will be discussed, respectively, in Chapters 22 and 1.)

    RESUME

    You now have fairly good control of reference integrity. The basic idea is that all foreign key values ​​refer to the specified parent key row. This means that each foreign key value must be represented once, and only once, in the parent key. Whenever a value is placed in a foreign key, the parent key is checked to ensure that its value is represented; otherwise, the command will be rejected. The parent key must have a PRIMARY KEY or UNIQUE constraint to ensure that the value is not represented more than once. An attempt to change a parent key value that is currently represented in a foreign key will be rejected altogether. Your system may, however, give you the choice to get the value of the foreign key set to NULL or to get the new value of the parent key, and specify which one can be obtained independently for the UPDATE and DELETE commands. This concludes our discussion of the CREATE TABLE command. Next we'll introduce you to another type of command - CREATE. In Chapter 20, you'll learn how to represent data objects that look and act like a table but are actually the results of queries. Some constraint functions can also be performed by views, so you will be able to better assess your need for constraints after you read the next three chapters.

    WORKING WITH SQL

    1. Create a table named Cityorders. It should contain the same onum, amt, and snum fields as the Orders table, and the same cnum and city fields as the Customers table, so that each customer's order will be entered into this table along with its city. 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. Do this using a foreign key referencing the Order table itself. The foreign key must also reference the customer's cnum field, providing a specific prescribed relationship between the current order and the referenced one.

    (See Appendix A for answers.)

    • InterBase can use the following types of restrictions:
    • PRIMARY KEY - the primary key of the table.
    • UNIQUE - unique table key.
    • FOREIGN KEY- foreign key, provides a link to another table and guarantees referential integrity between the parent and child tables.

    A note on terminology

    If you are like the author of this course in that you like to look for answers to a question that interests you comprehensively, in different works by different authors, then you could not help but notice some confusion in the definitions main (master) -> subordinate (detail) tables. Recall that the main table is often called the parent table, and the subordinate table is often called the child table.

    This is probably due to how these definitions are interpreted in local and SQL server DBMSs.

    In local DBMSs, the main table is the one that contains the main data, and the subordinate table contains additional data. Let's take, for example, three related tables. The first contains data on sales, the second - on products and the third - on customers:


    Rice. 18.1.

    Here the main information is stored in the sales table, hence it is the main (parent) table. Additional information is stored in the product and customer tables, which means they are children. This is understandable: one daughter cannot have two biological mothers, but one mother is quite capable of giving birth to two daughters.

    But in SQL database servers there is a different definition of relationships: when one field in a table refers to a field in another table, it is called foreign key. And the field it refers to is called parent or primary key. A table that has a foreign key (a link to a record in another table) is often called a child, and a table with parent key- parental. Also in the definition of relationships they say that a parent can have only one unique record, which can be referenced by several records child table.

    So in the example above, the sales table has two foreign keys: the product ID, and the customer ID. And both tables on the right side of the figure have parent key"Identifier". Since the same customer or product can appear multiple times in the sales table, it turns out that both tables on the right side of the figure are parents, and the table on the left is a child. Because we are now studying InterBase - SQL database server, we will be guided by these definitions in subsequent lectures. In order not to rack our brains further over this confusion, let’s agree right away: child table has a foreign key (FOREIGN KEY) to another table.

    PRIMARY KEY

    PRIMARY KEY- primary key is one of the main types of restrictions in a database. The primary key is designed to uniquely identify a record in a table and must be unique. Primary keys PRIMARY KEY are located in tables, which are usually called parent (Parent). The primary key should not be confused with the primary indexes of local databases; the primary key is not an index, but a constraint. When creating a primary key InterBase automatically creates for him unique index. However, if we create unique index, this will not create primary key constraints. A table can only have one primary key, PRIMARY KEY.

    Let's say you have a table with a list of employees. The Last Name field can contain duplicate values ​​(namesakes), so it cannot be used as a primary key. It is rare, but there are namesakes who, in addition, have the same names. Even more rarely, there are full namesakes, so even all three fields “Last Name” + “First Name” + “Patronymic” cannot guarantee the uniqueness of the record, and cannot be the primary key. In this case, the solution, as before, is to add an identifier field that contains the serial number of this person. Such fields are usually made auto-incrementing (we’ll talk about organizing auto-incrementing fields in the next lectures). So,

    Primary key is one or more fields in a table, the combination of which is unique for each record.

    If the primary key contains a single column (as is most often the case), the PRIMARY KEY specifier is used when column definition:

    CREATE TABLE Prim_1(Stolbec1 INT NOT NULL PRIMARY KEY, Stolbec2 VARCHAR(50))

    If the primary key is built on several columns, then the specifier is placed after defining all the fields:

    CREATE TABLE Prim_2(Stolbec1 INT NOT NULL, Stolbec2 VARCHAR(50) NOT NULL, PRIMARY KEY (Stolbec1, Stolbec2))

    As can be seen from the examples, the primary key must have a NOT NULL column(s) constraint.

    UNIQUE

    UNIQUE- unique key. The UNIQUE specifier indicates that all values ​​of this field must be unique; therefore, such fields also cannot contain values NULL. You could say that a UNIQUE key is an alternative to a primary key, but there are differences. The main difference is that there must be only one primary key, whereas there can be several unique keys. Additionally, a UNIQUE constraint cannot be built on the same set of columns that was used for a PRIMARY KEY or other UNIQUE constraint. Unique keys, like primary keys, are found in tables that are parents of other tables.

    A column declared with a UNIQUE constraint, like a primary key, can be used to enforce referential integrity between its parent and child tables. In this case, the foreign key child table will refer to this field(s). As with a primary key, when a unique key is created, a unique index. But not the other way around. An example of creating a table with one primary and two unique keys:

    CREATE TABLE Prim_3(Stolbec1 INT NOT NULL PRIMARY KEY, Stolbec2 VARCHAR(50) NOT NULL UNIQUE, Stolbec3 FLOAT NOT NULL UNIQUE)

    FOREIGN KEY

    FOREIGN KEY- foreign key. This is a very powerful tool for ensuring referential integrity between tables, which allows you to not only monitor the presence of the correct links, but also automatically manage them. Foreign keys are contained in tables that are children (Child) of other tables. Referential integrity is provided precisely by a foreign key that refers to the primary or

    Last update: 07/09/2017

    Foreign keys are used to establish relationships between tables. A foreign key is set on columns from a dependent, subordinate table, and points to one of the columns from the main table. Although it is common practice for a foreign key to point to a primary key from a related master table, this does not have to be the case. 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 main_table (main_table_column)

    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. The FOREIGN KEY keywords are also usually added, but in principle they do not have to be specified. After the REFERENCES expression comes the ON DELETE and ON UPDATE expression.

    The general syntax for setting a foreign key at the table level is:

    FOREIGN KEY (column1, column2, ... columnN) REFERENCES main_table (main_table_column1, main_table_column2, ... main_table_columnN)

    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 the main one and represents the client. Orders is dependent and represents the order placed by the 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 operator to specify a name for a foreign key constraint. Typically this name begins with the prefix "FK_":

    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 called "FK_Orders_To_Customers".

    ON DELETE and ON UPDATE

    Using the ON DELETE and ON UPDATE statements, you can set the actions to be performed when a related row is deleted or modified, respectively, 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 related rows in the master table are deleted or modified.

      NO ACTION: Prevents any action on the dependent table when related rows in the master table are deleted or modified. That is, in fact, there are no actions.

      SET NULL: When deleting a related row from the main table, sets the foreign key column to NULL.

      SET DEFAULT: When deleting a related row from the main table, sets the foreign key column to the default value that is specified using the DEFAULT attribute. If a column does not have a default value, NULL is used as the default value.

    Cascading deletion

    By default, if a row from the main table is referenced by a foreign key by any row from a dependent table, then we will not be able to delete this row from the main table. First, we will need to delete 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 a cascade delete is used, 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 expression works similarly. When you change the value of a primary key, the value of its associated foreign key automatically changes. 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 NULL

    When you set the SET NULL option for a foreign key, the foreign key column must be NULL-allowable:

    CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);

    Setting the default value

    CREATE TABLE Orders (Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT)

    FOREIGN KEY used for link restrictions.
    When all the values ​​in one table field 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 names of the foreign key and the parent key do not have to be the same. A foreign key can have any number of fields, which are all processed as a single unit. A foreign key and the parent key it refers to must have the same field number and field type, and be in the same order. When a field is a foreign key, it is related in some way to the table it references. Each value (each row) of a foreign key must unambiguously refer to one and only that value (row) of the parent key. If this condition is met, then the database is in state referential integrity.

    SQL maintains referential integrity with constraint FOREIGN KEY. This function must limit the values ​​that can be entered into the database to force the foreign key and parent key to comply with referential integrity. One of the restriction actions FOREIGN KEY is the dropping of values ​​for fields that are constrained as a foreign key that are 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 table structure) command) containing a field that is declared a foreign key. The parent key is given a name that is referenced within the constraint FOREIGN KEY.

    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.

    Table Constraint Syntax FOREIGN KEY:

    FOREIGN KEY REFERENCES

    [ ]

    The first column list is a comma-separated list of one or more table columns that will be created or modified by this command.

    Pktable- this is the table containing the parent key. It can be a table that is created or modified by the current command.

    The second column list is the list of columns that will make up the parent key. The two column lists 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 names.

    FOREIGN KEY Example 1

    CREATE TABLE Student
    (Kod_stud integer NOT NULL PRIMARY KEY,
    Kod_spec integer NOT NULL,

    Addresses char(50),
    Ball decimal),
    FOREIGN KEY(Kod_spec) REFERENCES Spec (Kod_spec)
    );

    When using ALTER TABLE instead of CREATE TABLE to apply a constraint FOREIGN KEY, the values ​​specified in the foreign key and parent key must be in referential integrity state. 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 match, 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,
    Addresses char(50),
    Ball decimal),
    Kod_spec integer REFERENCES Spec
    );

    Maintaining referential integrity requires some restrictions on the values ​​that can be represented in fields declared as a foreign key and a parent key. The parent key must be structured to ensure that each foreign key value corresponds to one specified row. This means that it (the key) must be unique and not contain any empty values ​​(NULL).

    This is not sufficient for the parent key to meet the same requirement as when declaring a foreign key. SQL must be sure that double values ​​or null values ​​have not been introduced into the parent key. Therefore, you must ensure 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 not simply associated with the parent keys they refer to; they are associated with a specific table row where that parent key will be found. The parent key itself does not provide any information that is not already present in the foreign key.

    Since the purpose of a primary key is to identify the uniqueness of a row, it is a more logical and less ambiguous choice for a foreign key. For any foreign key that uses a unique key as its parent key, you must create a foreign key that uses the primary key of the same table for the same effect. A foreign key, which has no purpose other than linking rows, is similar to a primary key, used solely to identify rows, and is a good means of keeping the database structure clear and simple. A foreign key can only contain values ​​that are actually present in the parent key or that are empty (NULL). Any attempt to enter other values ​​into this key will be rejected.

    FOREIGN KEY Example 3

    CREATE TABLE payment (
    sh_payout integer,
    sh_eml integer,
    date_payout date,
    sum_payout real,
    FOREIGN KEY(sh_eml) REFERENCES k_sotr2 (eid)
    );

    In this example FOREIGN KEY the sh_eml column is associated with the eid column from the k_sotr2 table.

    Read more: