What data can be a database key. The concept of a key in a database, primary and foreign keys. One to one communication

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!

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 the 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 column list 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)

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 sales people, 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 customer 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

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

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

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

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

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

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.

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, offer 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. 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, the values can be duplicated within the same column, but each combination of the values of 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.

Indexes in Foreign Key Constraints

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.

Referential integrity

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 associates the foreign key with data in another primary key.

Cascading referential integrity

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

Triggers and cascading reference actions

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.

A Foreign Key is a key used to join two tables. It is sometimes also called a referencing key.

A foreign key is a column or combination of columns whose values correspond to a primary key in another table.

The relationship between 2 tables corresponds to a primary key in one of the tables with a foreign key in the second table.

If a table has a primary key defined on any field(s), then you cannot have two records that have the same value for that field(s).

Example

Let's look at the structure of the following two tables.

CUSTOMERS table

CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));

ORDERS table

CREATE TABLE ORDERS (ID INT NOT NULL, DATE DATETIME, CUSTOMER_ID INT references CUSTOMERS(ID), AMOUNT double, PRIMARY KEY (ID));

If the ORDERS table has already been created and the foreign key has not yet been set, then the syntax is used to set the foreign key by modifying the table.

ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (ID);

Removing a Foreign Key Constraint

To remove a foreign key constraint, use the following SQL syntax.

ALTER TABLE ORDERS DROP FOREIGN KEY;

The Figure shows a table (a ratio of degree 5) containing some information about the employees of a hypothetical enterprise. Table rows correspond to tuples. Each row is actually a description of one real-world object (in this case, an employee), the characteristics of which are contained in the columns. Relational relationships correspond to sets of entities, and tuples correspond to entities. The columns in a table representing a relational relationship are called attributes.

Each attribute is defined on a domain, so a domain can be thought of as the set of valid values for a given attribute. Multiple attributes of the same relationship, and even attributes of different relationships, can be defined on the same domain.

An attribute whose value uniquely identifies tuples is called key (or simply key). The key is the Personnel Number attribute, since its value is unique for each employee of the enterprise. If tuples are identified only by concatenating the values of several attributes, then the relation is said to have a composite key.

Primary key- in a relational data model, one of the potential keys of a relationship, selected as the primary key (or default key).

A relation can contain multiple keys. One of the keys is always declared primary, its values cannot be updated. All other relation keys are called possible keys.

From a theoretical point of view, all potential (possible) relation keys are equivalent, that is, they have the same uniqueness and minimality properties. However, the primary key is usually selected from the potential keys that is most convenient for certain practical purposes, for example, for creating external keys in other respects or to create a clustered index. Therefore, as a rule, the one that has the smallest size (physical storage) and/or includes the smallest number of attributes is chosen as the primary key.

If primary key consists of a single attribute, it is called with a simple key.

If primary key consists of two or more attributes, it is called compound key. So, first name, last name, patronymic, passport number, passport series cannot be primary keys individually, since they may be the same for two or more people. But there are no two personal documents of the same type with the same series and number. Therefore, in a relation containing data about people, the primary key can be a subset of attributes consisting of the personal document type, its series and number.

Unlike hierarchical and network data models, the relational one does not have the concept of a group relationship. To reflect associations between tuples of different relations, duplication of their keys is used.

Attributes that are copies of keys of other relationships are called foreign keys.

For example, the relationship between the DEPARTMENT and EMPLOYEE relationships is created by copying the primary key "Department_number" from the first relationship to the second. Thus, in order to obtain a list of employees of a given department, it is necessary: 1) From the DEPARTMENT table, set the attribute value "Department_number" , corresponding to this “Department_Name”. 2) select all records from the EMPLOYEE table, attribute value "Department_number" which is equal to that obtained at the previous step. In order to find out in which department an employee works, you need to perform the reverse operation: 1) Determine "Department_number" from the EMPLOYEE table. 2) Using the obtained value, we find the entry in the DEPARTMENT table.

18. Normalization in relational databases, the concept of normal form in database design.

Normal form - a property of a relationship in a relational data model, characterizing it from the point of view of redundancy, which can potentially lead to logically erroneous results of sampling or changing data. Normal form is defined as a set of requirements that a relation must satisfy.

The process of converting a database to normal form is called normalization . Normalization is intended to bring the database structure to a form that provides minimal redundancy, that is, normalization is not intended to reduce or increase work productivity or reduce or increase the volume of the database. The ultimate goal of normalization is to reduce the potential inconsistency of information stored in the database.

Elimination of redundancy is carried out, as a rule, by decomposing relations in such a way that only primary facts are stored in each relation (that is, facts that are not inferred from other stored facts).

Functional dependencies.

A relational database contains both structural and semantic information. The structure of a database is determined by the number and type of relationships it contains, and the one-to-many relationships that exist between the tuples of these relationships. The semantic part describes the set of functional dependencies that exist between the attributes of these relationships. Let us define functional dependence.

19. 1NF: Basic definitions and transformation rules.

To discuss first normal form, two definitions are necessary:

Simple attribute - an attribute whose values are atomic (indivisible).

Complex attribute - is obtained by connecting several atomic attributes that can be defined on the same or different domains (it is also called a vector or data aggregate).

Definition of first normal form:

a relation is in 1NF if the values of all its attributes are atomic. . Otherwise, it is not a table at all and such attributes must be decomposed.

Let's look at an example:

In the database of the enterprise's HR department, it is necessary to store information about employees that can be attempted to be presented in relation to

EMPLOYEE(EMPLOYEE_NUMBER, NAME, DATE OF BIRTH, WORK_HISTORY, CHILDREN).

From a careful consideration of this relationship it follows that the attributes "work_history" And "children" are complex, moreover, the attribute "work_history" includes another complex attribute "salary_history".
These units look like this:

 JOB_HISTORY (RECEPTION_DATE, NAME, SALARY_HISTORY),

 SALARY_HISTORY (APPOINTMENT_DATE, SALARY),

 CHILDREN (CHILD_NAME, BIRTH_YEAR).

Their connection is shown in Fig. 3.3.

Fig.3.3. Initial attitude.

To bring the original relation SERVANT to the first normal form, it is necessary to decompose it into four relations, as this is shown in the following figure:

Fig.3.4. Normalized set of relations.

Here, the primary key of each relationship is highlighted with a blue frame, the names of the foreign keys are in blue font. Recall that foreign keys are used to represent functional dependencies that exist in the source relation. These functional dependencies are indicated by lines with arrows.

The normalization algorithm is described by E.F. Codd as follows:

Starting with the relation at the top of the tree (Figure 3.3.), its primary key is taken, and each immediately subordinate relation is expanded by inserting a domain or combination of domains of that primary key.
The Primary Key of each relation expanded in this way consists of the Primary Key that the relation had before the extension and the added Primary Key of the parent relation.
After this, all non-simple domains are deleted from the parent relation, the top node of the tree is removed, and the same procedure is repeated for each of the remaining subtrees.

20. 2NF: Basic definitions and transformation rules.

Very often the primary key of a relationship includes several attributes (in which case it is called composite) - see, for example, the relation CHILDREN shown in Fig. 3.4 question 19. At the same time, the concept is introduced full functional dependence.

Definition:

a non-key attribute is functionally fully dependent on a composite key if it is functionally dependent on the entire key as a whole, but is not functionally dependent on any of its constituent attributes.

Example:

Let there be a relation SUPPLY (N_SUPPLIER, PRODUCT, PRICE).
A supplier may supply different products, and the same product may be supplied by different suppliers. Then the relation key is "N_supplier + product". Let all suppliers supply goods at the same price. Then we have the following functional dependencies:

N_supplier, product -> price
product -> price

The incomplete functional dependence of the price attribute on the key leads to the following anomaly: when the price of an item changes, a full view of the relationship is required in order to change all records about its suppliers. This anomaly is a consequence of the fact that two semantic facts are combined in one data structure. The following expansion gives the relations in 2NF:

DELIVERY (N_SUPPLIER, PRODUCT)
PRODUCT_PRICE (PRODUCT, PRICE)

So you can give

Definition of second normal form: A relation is in 2NF if it is in 1NF and each non-key attribute is fully functionally dependent on the key.

21. 3NF: Basic definitions and transformation rules.

Before discussing third normal form, it is necessary to introduce the concept: transitive functional dependence.

Definition:

Let X, Y, Z be three attributes of some relation. In this case, X --> Y and Y --> Z, but there is no reverse correspondence, i.e. Z -/-> Y and Y -/-> X. Then Z depends transitively on X.
Let there be a relation STORAGE ( FIRM, WAREHOUSE, VOLUME), which contains information about companies receiving goods from warehouses and the volumes of these warehouses. Key attribute - "firm". If each company can receive goods from only one warehouse, then in this regard there are the following functional dependencies:

firm -> stock
stock -> volume

In this case, anomalies arise:

if at the moment no company receives goods from the warehouse, then data on its volume cannot be entered into the database (since the key attribute is not defined)
if the warehouse volume changes, it is necessary to view the entire relationship and change the cards for all companies associated with this warehouse.

To eliminate these anomalies, it is necessary to decompose the original relation into two:

STORAGE ( FIRM, STOCK)
STORAGE_VOLUME ( STOCK, VOLUME)

Definition of third normal form:

A relation is in 3NF if it is in 2NF and each non-key attribute does not depend transitively on the primary key.

In this topic, using two tables as an example, the basic concepts of relational databases are defined, namely:

primary key;
external key;
simple and compound key;
attitude, types of relationships;
artificial and natural keys;
main (master) and subordinate (detail) tables.

Input data

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.

Question answer

1. What is a primary key in a database table? What are primary keys used for?

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.

2. What is a relationship between tables? Example

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:

find the personnel number of the employee Ivanov I.I. in the "Employee" table. The value of the personnel number is 7585;
in the “Salary” table, find all values that are equal to 7585 (personnel number);
select from the “Salary” table all values of the “Accrued” field that correspond to personnel number 7585.

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

3. What is a foreign key? Example

The concept of "foreign key" is important when considering related tables.

External key– these are 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.

4. What is a recursive foreign key?

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

5. Can primary and foreign keys be simple or compound (complex)?

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

6. What is the difference between an artificial and a natural key? Example

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.

7. What are the different ways to select a primary key?

There are 3 ways to select a primary key:

use the increment field (counter field) as an artificial key;
select one field from the data that can provide uniqueness;
select several fields from the data that can provide uniqueness. In this case, the key will also be called complex (composite).

8. What do the terms “main table” (master) and “subordinate table” (detail) mean?

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.

9. What types of relationships (links) exist between tables?

There are 4 main types of relationships between tables:

"one to one". In this case, each record of one table corresponds to only one record of another table;
"one to many". This is when one record of the main table (master) corresponds to several records of the subordinate table (detail). That is, each record that is the primary key of one table corresponds to several records of the related table;
"many to one". This is when several records of the main table correspond to one record of the subordinate table;
"many to many". This is when there are multiple related records in both 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.