What is a primary key in a database? Constraints of primary and foreign keys Description primary key foreign
They are used in any activity: in the banking and financial industries, tourism business, warehouses, production and training. They are a collection of tables, have clear properties and are subject to strict requirements. In relational databases, tables are called relationships.
What is a primary key in a database
In a database, the primary key of a table is one of its columns (Primary key). Let's look at an example of what this looks like. Let's imagine a simple attitude of university students (let's call it "Students").
We need to uniquely identify a student using one column. To do this, the information in this column must be unique for each entry. But the available data in this regard does not allow us to unambiguously identify the record, since namesakes, namesakes and students with the same surnames and first names can study in the same course and the same faculty. The primary key in the database is used to precisely identify the required row in a relationship. Most often, a numeric field is used in this capacity, automatically increasing as a record is entered (auto-incrementing identifier column).
Simple and Composite Primary Key
Primary key can be simple or compound. If the uniqueness of a record is determined by the value in only one field, as described above, we are dealing with a simple key. A composite key is a database primary key consisting of two or more fields. Consider the following attitude of bank clients.
| FULL NAME. | Date of Birth | Passport Series | Passport ID |
| Ivanov P.A. | 12.05.1996 | 75 | 0553009 |
| Sergeev V.T. | 14.07.1958 | 71 | 4100654 |
| Krasnov L.V. | 22.01.2001 | 73 | 1265165 |
People's passports may contain the same series or numbers, but there are no passports with the same series and number combination. Thus, the fields “Passport Series” and “Passport Number” will become a composite key of the specified relationship, uniquely identifying the person.
Connections between relationships
So, a primary key in a database is one or more columns of a table that allows one to uniquely identify a row in that relationship. What is it for?
Let's return to the first example with the relation "Students". In addition to this relationship, the database also stores other information, for example, the performance of each student. In order not to repeat all the information that is already contained in the database, they use a key, referring to the desired record. It looks like this.
In the two example relationships we see an ID field. These are the primary keys in the database for these tables. As you can see, the academic record contains only links to these fields from other tables without the need to indicate all the information from them.
Natural and surrogate key
How is the primary key of a database table determined? The two examples we looked at - "Students" and "Bank Clients" - illustrate the concepts of natural and surrogate keys. In the table of bank clients, we defined a key consisting of the fields “Number” and “Passport Series”, using already existing columns. This key is called natural; we did not make any changes or additions to determine it. In the case of the "Students" relation, no single field or combination of fields gave us uniqueness. This forced us to enter an additional student code field. This key is called a surrogate key, for which we added another service column to the table. This column does not contain any useful information and serves only to identify records.
Foreign key and data integrity in the database
All of the above brings us to the foreign key and database integrity. Foreign key is a field that refers to the Primary key of a foreign relationship. In the progress table, these are the “Student” and “Discipline” columns. Their data refers us to external tables. That is, the “Student” field in the “Performance” relation is a Foreign key, and in the “Student” relation it is the primary key in the database.
An important principle for constructing databases is their integrity. And one of its rules is referential integrity. This means that a foreign key of a table cannot refer to a non-existent Primary key of another relation. You cannot delete a record with code 1000 - Ivanov Ivan from the Student relationship if it is referenced by a record from the academic performance table. In a properly constructed database, when you try to delete it, you will receive an error that this field is in use.
There are other groups of integrity rules, as well as other database restrictions, that also deserve attention and should be taken into account by developers.
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, a worker), 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.
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 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 keyLike 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* 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 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
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 ensure 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 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:
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 cascade 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 further illustrate the functions of these effects.
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.)SUMMARY
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 the order of each customer 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.)
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
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 the 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 same table's primary key 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.
Keys are fundamental elements of a relational database because they establish a relationship between a pair of tables and provide a unique identification for each record in the table. The keys are more important than establishing relationships; they also help with referential integrity, and they are a major component of table-level integrity. Tables store huge chunks of data that typically span thousands of records, all of which are unsorted and disorganized. Retrieving specific data from these multiple records can be difficult or impossible at times. This is where the Keys appear. Here we will look at two very important relational database schema keys and the difference between them: Primary Key and Foreign Key.
What is a primary key?
A primary key is a special key that uniquely identifies each record in a table. In a relational database, it is very important to have a unique identifier in each row of a table, and a primary key is simply what you need to uniquely identify a tuple in a table. A tuple is a collection of value attributes in a relational database. A primary key can refer to a column or set of columns in a relational database table that is used to implicitly identify all records in the table. The primary key must be unique for each record as it acts as a unique identifier and must not contain Null values. Every database must have one and only one primary key.
What is a foreign key?
A foreign key refers to a field or collection of fields in a database record that uniquely identifies the key field of another database record in another table. In simple terms, it establishes a relationship between records in two different tables in a database. It could be a column in a table that points to the primary key columns, which means that the foreign key defined in the table refers to the primary key of some other table. Links are critical in relational databases for establishing relationships between records, which are necessary for sorting databases. Foreign keys play an important role in normalizing relational databases, especially when tables need access to other tables.
Difference between primary key and foreign key
Basics of Primary Key and Foreign Key
A primary key is a special key in a relational database that acts as a unique identifier for each record, meaning that it uniquely identifies each row/record in a table and its value must be unique for each row of the table. On the other hand, a foreign key is a field in one table that links two tables together. It refers to a column or group of columns that uniquely identifies a row of another table or the same table.
Primary key and foreign key relationship
A primary key uniquely identifies a record in a relational database table, whereas a foreign key refers to a field in a table that is the primary key of another table. The primary key must be unique and only one primary key is allowed in a table to be defined, whereas more than one foreign key is allowed in a table.
Duplicate primary key and foreign key values
A primary key is a combination of UNIQUE and Not Null constraints, so a primary key field in a relational database table cannot be allowed to have duplicate values. No two rows can carry duplicate values for a primary key attribute. Unlike a primary key, a foreign key can contain duplicate values, and a table in a relational database can contain more than one foreign key.
NULL primary key and foreign key
One of the main differences between the two is that, unlike primary keys, foreign keys can also contain NULL values. A table in a relational database can only have one primary key, which is not nullable.
Temporary table of primary key and foreign key
A primary key constraint can be defined implicitly on temporary tables and their variables, whereas a foreign key constraint cannot be applied to local or global temporary tables.
Removing a primary key and a foreign key
A primary key value cannot be deleted from a parent table that is referred to as a foreign key in a child table. Before you can drop a parent table, you must drop the child table first. In contrast, a foreign key value can be deleted from a child table even if the value belongs to the parent table's primary key.
Primary key or foreign key: comparison table
Summary of Key Keys
Keys play a crucial role in the existence of a database schema to establish relationships between tables and within a table. Keys establish relationships and enforce various types of integrity, especially table-level and relationship-level integrity. First, they believe that a table contains unique records, and the fields you use to establish relationships between tables must contain corresponding values. Primary key and foreign key are the two most important and common types of keys used in relational databases. A primary key is a special key used to uniquely identify records in a table, while a foreign key is used to establish a relationship between two tables. Both are identical in structure but play different roles in a relational database schema.
