Last update: 27.04.2019
Foreign keys allow you to establish relationships between tables. The foreign key is set for columns from the dependent, subordinate table, and points to one of the columns from the main table. Typically, a foreign key points to a primary key from a related master table.
The general syntax for setting a foreign key at the table level is:
FOREIGN KEY (column1, column2, ... columnN) REFERENCES master_table (column_main_table1, column_main_table2, ... column_main_tableN)
To create a foreign key constraint, after FOREIGN KEY, you specify the table column that will represent the foreign key. And then keyword REFERENCES specifies the name of the related table, followed by the name of the related column in parentheses that the foreign key will point to. The REFERENCES expression is followed by the ON DELETE and ON UPDATE statements, which specify the action to be taken when a row is deleted and updated from the main table, respectively.
For example, let's define two tables and link them using a foreign key:
CREATE TABLE Customers (Id INT PRIMARY KEY AUTO_INCREMENT, Age INT, FirstName VARCHAR (20) NOT NULL, LastName VARCHAR (20) NOT NULL, Phone VARCHAR (20) NOT NULL UNIQUE); CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
In this case, the Customers and Orders tables are defined. Customers is in charge and represents the customer. Orders is dependent and represents an order placed by a customer. The Orders table is linked through the CustomerId column to the Customers table and its Id column. That is, the CustomerId column is a foreign key that points to the Id column from the Customers table.
Using the CONSTRAINT statement, you can specify a name for the foreign key constraint:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, CONSTRAINT orders_custonmers_fk FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
ON DELETE and ON UPDATE
The ON DELETE and ON UPDATE statements can be used to set the actions to be taken, respectively, when a related row is deleted and modified from the main table. The following options can be used as an action:
CASCADE: Automatically deletes or modifies rows from a dependent table when deleting or modifying related rows in the main table.
SET NULL: Sets the foreign key column to NULL when deleting or updating a related row from the master table. (In this case, the foreign key column must support setting to NULL)
RESTRICT: Rejects deleting or modifying rows in the master table if there are related rows in the dependent table.
NO ACTION: same as RESTRICT.
SET DEFAULT: When deleting a related row from the master table, sets the foreign key column to the default value, which is set using the DEFAULT attributes. Although this option is available in principle, the InnoDB engine does not support this expression.
Cascade deletion
Cascading delete allows you to automatically delete all related rows from the dependent table when you delete a row from the master table. For this, the CASCADE option is used:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);
The ON UPDATE CASCADE statement works in a similar way. Changing the value of the primary key will automatically change the value of its associated foreign key. However, since primary keys are changed very rarely, and, in principle, it is not recommended to use columns with mutable values as primary keys, in practice the ON UPDATE statement is rarely used.
Setting to NULL
Setting the SET NULL option for a foreign key requires the foreign key column to be nullable:
CREATE TABLE Orders (Id INT PRIMARY KEY AUTO_INCREMENT, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
PRIMARY KEY constraint
A PRIMARY KEY is a column-level integrity constraint — a set of supported rules — that formally marks a column or group of columns as a unique identifier for each row in a table.
If you came to Firebird from a DBMS that supports the concept of a "primary index" for defining a key (usually file-based systems such as Paradox, Access, and MySQL), then Firebird and the world SQL standards you understand. The primary key is not an index, but a constraint. One of the rules for such a constraint is that the constraint must have a specific unique index of one or more non-empty elements associated with it.
Simply creating such an index does not create a primary key. However, creating a primary key constraint creates the required index, consisting of the columns listed in the constraint declaration.
ATTENTION! You do not need to import an existing "primary index" from a file-based legacy system, or create such an index pending the declaration of a primary key constraint. Firebird cannot impose a primary key constraint on top of an existing index - at least in existing versions, including 1.5 - and the query optimizer will not work correctly when there are duplicate indexes.
A table can only have one primary key. When you define a constraint, Firebird automatically creates the required index using a variety of named rules. Primary key index names are discussed below.
ATTENTION! If you are converting a database to Firebird from any other source except InterBase or Oracle, then you should pay special attention to the schema regarding primary key naming and constraints.
Although the PRIMARY KEY constraint itself is not a referential constraint, it is usually a required part of any referential constraint, being a potential object of the REFERENCES clause of a FOREIGN KEY constraint. See chapter 17 for details.
Selecting a primary key
Identifying columns as candidates for a primary key is outside the scope of this edition. Many excellent books have been written about normalization, the process of reducing redundancy and duplicate groups in datasets, and correctly identifying an item that uniquely represents a single row in a table. If you're new to relational databases, then the cost of learning a good data modeling book isn't too big.
The primary key candidate, which can be a single column or a group of columns, has two prerequisites.
* The NOT NULL attribute must be declared for all columns in a group of one or more columns. Key integrity can only be enforced by comparing values, and NULL is not a value.
* A column or group of columns must be unique - that is, more than one row with the same values cannot appear in the table. For example, a driver's license or social security number can be considered candidates because they are generated by systems that do not allow duplicate numbers.
To these theoretical "attitudes" a third must be added.
* The total size of candidate keys must be 252 bytes or less. It's not just a matter of counting characters. This limit must be reduced — in some cases drastically — if multiple columns, a non-binary collation, or a multibyte character set are present.
How real data can lead you to failure
Using the EMPLOYEE table from the employee.fdb database from the / examples directory of the Firebird root directory (employee.gdb in version 1.0.x), let's see how real data can lead to your theoretical assumptions about uniqueness falling apart. Here is a declaration that shows the meaningful data stored in this table:
CREATE TABLE EMPLOYEE (
FIRST_NAME VARCHAR (15) NOT NULL,
/ * guess: employee must have a name * /
LAST_NAME VARCHAR (20) NOT NULL,
/ * assumption: the employee must have a last name * /
PHONE_EXT VARCHAR (4),
HIRE_DATE DATE DEFAULT CURRENT_DATE NOT NULL,
DEPT_NO CHAR (3) NOT NULL,
JOB_CODE VARCHAR (5) NOT NULL,
JOB_GRADE SMALLINT NOT NULL,
JOB_COUNTRY VARCHAR (15) NOT NULL,
SALARY NUMERIC (15, 2) DEFAULT 0 NOT NULL,
FULL_NAME COMPUTED BY FIRST_NAME || || LAST_NAME);
In fact, this structure has no candidate for keys. It is not possible to identify a single employee row using (FIRST_NAME, LAST_NAME) as the key because the combination of the two items with a medium to high probability of duplication in the organization. We cannot save the records of two employees named John Smith.
To get the keys, you need to invent something. This "something" is a mechanism known as a surrogate key.
Surrogate keys
We already covered a surrogate key in the introductory topic about keys in Chapter 14. A surrogate primary key is a value that is guaranteed to be unique and has no meaning, which is used as a substitute for a key in a table structure that cannot provide a candidate for a key in its own structure. For this reason, EMP_NO is added to the EMPLOYEE table (declared through the domain) to act as a surrogate key:
CREATE DOMAIN EMPNO SMALLINT;
ALTER TABLE EMPLOYEE
ADD EMP_NO EMPNO NOT NULL,
ADD CONSTRAINT PK_EMPLOYEE
PRIMARY KEY (EMP_NO);
This database also contains a generator named EMP_NO_GEN and a Before insert trigger (before adding) named SET_EMP_NO for the EMPLOYEE table to get the value of the given key when a new row is added. In sect. "Implementing Auto-Increment Keys" in Chapter 31 describes this technique in detail. This is the recommended way to implement surrogate keys in Firebird.
You may want to consider the benefits of using a surrogate primary key, not only in cases where the table cannot offer a candidate, but also in cases where your candidate key is composite.
Composite Primary Keys
When analyzing data, sometimes a single unique column can be found in a data structure. The theory advises to find two or more columns, grouped together as a key, that will guarantee the uniqueness of the row. When multiple columns are combined to form a key, the key is called a composite key or sometimes a composite key.
If you have experience with databases such as Paradox, where you have used composite keys to implement hierarchical relationships, you may find it difficult to get over the idea of living without them. For the time being, in practice, composite keys should be considered very limited in DBMSs such as Firebird, which do not traverse physical index structures on disk to enforce relationships.
In Firebird, there is no need for composite indexes and, moreover, composite indexes pose some problems for both development and performance in case of large tables.
* Composite keys are usually composed of non-atomic key elements - i.e. the columns selected have semantic meaning (they are "meaningful data") and are clearly vulnerable to external changes and manual errors.
* Foreign keys from other tables that refer to this table will duplicate every element of the composite key. Referential integrity is at risk when using non-atomic keys. The combination of non-atomic elements increases the risk.
* Keys - external, as well as primary - have constant indexes. Composite indexes have stricter size limits than single-column indexes.
* Composite indexes tend to be large. Large indexes use more database pages, which makes index operations (sort, join, and compare) slower than necessary.
Atomicity of primary key columns
It is a good practice not to include any columns that make sense as data in primary and foreign keys. This violates one of the fundamental principles of relational database design - atomicity. The principle of atomicity requires that each data element fully exist on its own with a single internal rule for managing its existence.
For the primary key to be atomic, one has to be outside of human decisions. If people compose it or classify it, it is not atomic. If it is subject to any rule other than NOT NULL and uniqueness, it is not atomic. In the previous example, even the driver's license or social security number does not meet the atomicity requirements for the primary key because they are subjects of external systems.
Primary Key Declaration Syntax
Several syntaxes can be used to assign a PRIMARY KEY constraint to a column or group of columns. All columns that are members of the primary key must be predefined with the NOT NULL attribute. Since you cannot add a NOT NULL constraint to a column after it has been created, you must take care of this constraint before using other constraints.
The PRIMARY KEY constraint can be applied in any of the following phases of metadata creation:
* in a column definition in a CREATE TABLE or ALTER TABLE statement as part of a column definition;
* in a table definition in a CREATE TABLE or ALTER TABLE statement as a separately defined table constraint.
Defining a Primary Key as Part of a Column Definition
The following sequence creates and commits a non-nullable domain, then defines a primary key column based on that domain and simultaneously enforces a PRIMARY KEY constraint on that column:
CREATE DOMAIN D_IDENTITY AS BIGINT NOT NULL;
CREATE TABLE PERSON (
PERSON_ID D_IDENTITY PRIMARY KEY,
Firebird creates a table constraint named INTEG_M and an index named RDB $ PRIMARYnn. (pl in each case is the number received from the generator. These two numbers are not related to each other.) You cannot influence what these names will be, and you cannot change them.
The result is similar if you use the same approach when adding a column using the ALTER TABLE statement and creating the primary key in one clause:
ALTER TABLE BOOK
ADD BOOK_ID D_IDENTITY PRIMARY KEY;
Defining a Primary Key as a Named Constraint
Another way to define a primary key in a table definition is to add a constraint declaration at the end of the column definitions. Constraint declarations are placed last because they depend on the existence of the columns they refer to. This method gives you the ability to name constraints. The following declaration names the primary key constraint PK_ATABLE:
CREATE TABLE ATABLE (
ID BIGINT NOT NULL,
ANOTHER_COLUMN VARCHAR (20),
CONSTRAINT PK_ATABLE PRIMARY KEY (ID));
Now, instead of using the system-generated RDB name $ PRIMARYnnn, Firebird uses PK_ATABLE as the name of this constraint. In Firebird 1.5 and up, it also enforces the user-defined constraint name for the backing unique index. In this example, the index will be named PK_ATABLE, while in other versions it will be named RDB $ PRIMARYnnn.
Firebird 1.5 also allows user-defined names for the constraint and the index that supports it.
Using a custom index
Prior to Firebird 1.5, it was not possible to use a descending index to support the primary key. Since version 1.5, it is possible to support the primary key with a descending index. To do this, Firebird 1.5 adds a syntax extension in the form of a USING clause that allows you to create an ASC (ascending) or DESC (descending) index and assign it a different name than the constraint name.
AS c and DESC determine the direction of the search. This concept is discussed in more detail in Chapter 18.
The following statement will create a primary key constraint named PK ATEST and its supporting descending index named IDX_PK_ATEST:
CREATE TABLE ATEST (
ID BIGINT NOT NULL,
DATA VARCHAR (10));
ALTER TABLE ATEST
ADD CONSTRAINT PK_ATEST PRIMARY KEY (ID)
USING DESC INDEX IDX_PK_ATEST;
An alternative syntax will work as well:
CREATE TABLE ATEST (
ID BIGINT NOT NULL,
DATA VARCHAR (10),
CONSTRAINT PK_ATEST PRIMARY KEY (ID)
USING DESC INDEX IDX PK ATEST;
ATTENTION! If you create a DESCENDING index for a primary or unique key constraint, you must specify USING DESC INDEX for all foreign keys referencing it.
Adding a primary key to an existing table
Adding constraints to the table can be delayed. It is common practice for developers to define all their tables without table constraints and then add them using a separate script. The main reason for this practice is that large scripts often fail because the authors forget about some dependencies. There will simply be less headaches if you create the database in sequence, which reduces the time and frustration of fixing dependency errors and rerunning scripts.
Usually in the first script we declare tables and confirm their creation:
CREATE TABLE ATABLE (
ID BIGINT NOT NULL,
ANOTHER_COLUMN VARCHAR (20),
< другие столбцы >) ;
CREATE TABLE ANOTHERTABLE (
ALTER TABLE ATABLE
ADD CONSTRAINT PK_ATABLE
PRIMARY KEY (ID);
ALTER TABLE ANOTHERTABLE ...
In the next chapter, as we look at FOREIGN KEY definitions, the benefits of creating a database in a sequence of reliable dependencies will become apparent.
From the book Databases: lecture notes the author author unknown3. Constraining integrity by state Constraining the integrity of a relational data object by state is the so-called data invariant. In this case, integrity should be confidently distinguished from security, which, in turn, implies protection of data from
From the book The C # 2005 Programming Language and the .NET 2.0 Platform. author Troelsen AndrewRestricting Attribute Usage By default, custom attributes can be applied to any part of your code (methods, classes, properties, etc.). Therefore, if only it makes sense, VehicleDescription can be used to define (among other things) methods,
From the book DIY Linux Server the author11.2.2. Restricting Access I find it necessary to take a closer look at the Limit block directive. This directive determines the type and parameters of access to a particular directory. Consider Listing 11.9 Listing 11.9. An example of using the Limit directive Restricting access to clients To restrict client access to some component h, the ability to include two or more feature sections in the class declaration will be used. The announcement will look like this: class S2 featuref ... g ... feature (A, B) h ...... end Components f and g 5.1. Enumeration constraint In the process of achieving the goal, the prologue system automatically enumerates the options, making a return if any of them fails. Such brute force is a useful programming mechanism as it relieves the user of the need to Constraint The second element of the ODP formula is the deadline, or constraint. This can be a time limit (for example, a 50% discount for only two days) or quantity (for example, a valuable gift for the first 50 customers). Moreover, short deadlines work Reference constraint The reference constraint is implemented as FOREIGN KEY. A foreign key constraint exists only in the context of another table and the unique key of that table, either explicitly or implicitly in the REFERENCES clause when the constraint is declared. NOT NULL Constraint Firebird does not support the nullable attribute as some non-standard DBMS do. According to the standards, all columns in Firebird can contain a null value, unless the NOT NULL constraint is explicitly specified. 4.11.5. Limiting the network In large networks, it is very difficult to describe each computer. To facilitate this task, you can use group recordings. For example, you need to allow Internet access only for the 192.168.1.x network (with a mask 255.255.255.0). This means that the first 24 bits (the first three 9.5.8. Channel limitation When organizing Internet access, it is very often required for individual users to provide a higher connection speed. How to do this, when by default everyone is equal and can work at the maximum currently available speed? CHECK Constraint One of the most useful constraints in a database is the CHECK constraint. Its idea is very simple - to check the value inserted into the table for any condition and, depending on whether the condition is met, insert or not insert data. A2.4. Access restriction P2.4.1. Denying access to a site (or a list of sites) Suppose you want to deny access to a specific site (or a list of sites). To do this, go to the section Billing | Clients | Filters | Up to the group (Fig. A2.10). Run the menu command Action | A2.4.2. Rate limiting To limit the speed of a particular user, select it in the list of users, right-click and select Properties. Go to the Restrictions tab (Fig. A2.14), uncheck the Default box in the I present to your attention a free translation of the article SQL for Beginners Part 2 It is important for every web developer to be able to interact with databases. In the second part, we continue to study the language SQL and apply our skills to the DBMS MySQL... We will take a look at indexes, data types, and more complex queries. Please refer to the "What you need" section of the first part that is located. If you want to run the given examples on your server, do the following: Indexes (or keys) are commonly used to speed up the execution of statements that fetch data (such as SELECT) from tables. They are an important part of good database architecture and are difficult to classify as "optimization". Indexes are usually added initially, but can be added later using a query ALTER TABLE. The main reasons for indexing database columns are: Almost all tables have a primary key, usually an integer with the auto-increment option ( AUTO_INCREMET). Very often, subqueries lead to significant performance degradation, so use them with caution. Using query UNION, you can combine the results of multiple SELECT queries. This example combines states starting with the letter "N" with states with large populations: (SELECT * FROM states WHERE name LIKE "n%") UNION (SELECT * FROM states WHERE population> 10000000); Note that New York is a major state and starts with the letter "N". However, it appears only once in the list, since duplicates are removed automatically. Just the beauty of requests UNION is that they can be used to combine queries against different tables. For example, we have the employees, managers, and customers tables. Each table has a field with an email address. If we want to receive all E-mail addresses in one request, we can proceed as follows: (SELECT email FROM employees) UNION (SELECT email FROM managers) UNION (SELECT email FROM customers WHERE subscribed = 1); After completing this request, we will receive the mailing addresses of all employees and managers, and only those customers who are subscribed to the newsletter. We already talked about the request INSERT in the previous article. After we have covered indexes, we can talk about additional features requests INSERT. This is the most commonly used condition. First, the request tries to execute INSERT, and if the request fails due to duplicate primary ( PRIMARY KEY) or unique ( UNIQUE KEY) key, then the request is executed UPDATE. Let's create a test table first. This is a food storage table. The "stock" field stores the number of products available in the warehouse. Now let's try to insert an already existing value into the table and see what happens. We got an error. Let's say we received a new bakery and want to update the database, but we don't know if there is already a record in the database. We can first check for the existence of a record and then execute another insert query. Or you can do everything in one simple query: Works the same as INSERT but with one important feature. If the entry already exists, then it is deleted, and then the request is executed INSERT and we will not receive any error messages. Please note, because a completely new row is inserted, the auto-increment field is increased by one. This is a way to prevent a duplicate error from occurring, primarily in order not to stop the execution of the application. You may need to insert a newline without printing any errors, even if there is a duplication. There are no errors and no updated lines. Each column in the table must be of a certain type... We have already used the types INT, VARCHAR and DATE, but did not dwell on them in detail. We will also look at a few more data types. Let's start with numeric data types. I divide them into two groups: Wholes and Fractions. An integer column can only store natural numbers (no decimal point). By default, they can be positive or negative. If option is selected UNSIGNED, then only positive numbers can be stored. MySQL supports 5 types of integers different sizes and ranges: These types can store fractional numbers: FLOAT, DOUBLE and DECIMAL. FLOAT is 4 bytes, DOUBLE is 8 bytes and is similar to the previous one. DOUBLE is more accurate. DECIMAL (M, N) has variable precision. M is the maximum number of digits, N is the number of digits after the decimal point. For example, DECIMAL (13,4) has 9 decimal places and 4 after. As the name suggests, they can store strings. CHAR (N) can store N characters and is fixed. For example, CHAR (50) must always contain 50 characters per line across the entire column. Maximum possible value 255 characters VARCHAR (N) works the same, but the range can vary. N - indicates the maximum value. If the stored string is shorter than N characters, then it will take up less space on the hard disk. The maximum possible value is 65535 characters. The TEXT flavors are more suitable for long strings. TEXT has a limit of 65535 characters, MEDIUMTEXT at 16.7 million, and LONGTEXT at 4.3 billion characters. MySQL usually stores them in separate repositories on the server so that the main repository is as small and faster as possible. Thanks for reading the article. SQL is an important language and tool in a web developer's arsenal. This is how we quietly approached a very important topic - primary and foreign keys. While the former are used by almost everyone, the latter are for some reason ignored. But in vain. Foreign keys are not a problem; they are a real aid to data integrity. We have already talked a lot about key fields, but we never used them. The most interesting thing is that everything worked. This is an advantage, or maybe a disadvantage of the database. Microsoft SQL Server and MS Access. In Paradox tables, this trick will not work, and without the presence of a key field, the table will be read-only. To some extent, keys are constraints, and they could be considered in conjunction with the CHECK operator, because the declaration is similar and even the CONSTRAINT operator is used. Let's take a look at this process with an example. To do this, create a table with two fields "guid" and "vcName". In this case, the "guid" field is set as the primary key: CREATE TABLE Globally_Unique_Data (guid uniqueidentifier DEFAULT NEWID (), vcName varchar (50), CONSTRAINT PK_guid PRIMARY KEY (Guid)) The most delicious thing here is the CONSTRAINT line. As we know, this keyword is followed by the name of the constraint, and key declarations are no exception. For naming the primary key, I recommend using naming like PK_name, where name is the name of the field that should become the primary key. The abbreviation PK comes from Primary Key. After that, instead of the CHECK keyword, which we used in the restrictions, there is a PRIMARY KEY operator.This indicates that we do not need a check, but a primary key. One or more fields that will make up the key are indicated in brackets. Remember that two rows cannot have the same value in a key field, in this case the primary key constraint is identical to the uniqueness constraint. This means that if we make the field for storing the surname as the primary key, then it will not be possible to write two Ivanovs with different names to such a table. This violates the primary key constraint. This is why keys are constraints and are declared the same as CHECK constraints. But this is not only true for primary keys and uniqueness secondary keys. V this example, the primary key is a field of type uniqueidentifier (GUID). The default value for this field is the result of the NEWID server procedure. Attention Only one primary key can be created for a table For simplicity of examples, it is desirable to use a numeric type as a key, and if the database allows it, it would be better if it is of the "autoincrement" type (automatically increasing / decreasing number). In MS SQL Server such field is IDENTITY, and in MS Access it is a field of type "counter". The following example shows how to create a product table with an auto-incrementing integer field as the primary key: CREATE TABLE Products (id int IDENTITY (1, 1), product varchar (50), Money price, Number numeric (10, 2), CONSTRAINT PK_id PRIMARY KEY (id)) We will use this type of key most often, because easy-to-read numbers will be stored in the key field and it is easier and more intuitive to work with them. A primary key can have more than one column. The following example creates a table in which the fields "id" and "Product" form the primary key, which means that a unique index will be created for both fields: CREATE TABLE Products1 (id int IDENTITY (1, 1), Product varchar (50), Money price, Number numeric (10, 2), CONSTRAINT PK_id PRIMARY KEY (id, [Product name])) Very often programmers create a database with a key field in the form of an integer, but at the same time the task clearly states that certain fields must be unique. Why not immediately create a primary key from those fields that should be unique and you will not need to create separate solutions for this problem. The only drawback of a multi-column primary key is the problem of creating relationships. Here you have to get out by various methods, but the problem is still solvable. You just need to enter a field of type uniqueidentifier and make a link on it. Yes, in this case, we get a unique primary key and a field of type uniqueidentifier, but this redundancy as a result will not be greater than the same table where the primary key is uniqueidentifier, but a unique constraint is set on the fields that must be unique. What to choose? Depends on the specific task and on what is more convenient for you to work with. A foreign key is also a CONSTRAINT constraint and represents the relationship between two tables. Let's say you have two tables: One person can have several phones, so we divided the data storage into different tables. Figure 1.4 visually shows the relationship between the two tables. If you've worked with linked tables before, this should be enough for you. If you are hearing about connections for the first time, then let's try to look at the problem more closely. Let's take a table of three people as an example. Table 1.3 shows the contents of the "Names" table. There are only three lines, and each has its own unique master key. For uniqueness, when we create a table, we will make the key an automatically incremented field. Table 1.3 Contents of the Names table Table 1.4. Phones table content Table 1.4 contains five phone numbers. The master key field also contains a unique master key, which can also be made auto-incrementing. The secondary key is a relationship to the primary key of the Names table. How does this link work? Petrov has the number 1 in the Names table as the main key. In the Phones table, in the secondary key, we look for the number 1 and get Petrov's phone numbers. It's the same with the rest of the recordings. Visually, the connection can be seen in Figure 1.5. Such data storage is very convenient. If it were not for the ability to create linked tables, then in the Names table, you would have to fill in all the phone numbers in one field. This is inconvenient in terms of use, maintenance, and data retrieval. You can create several Names fields in the table, but the question arises - how many. One person can only have 1 phone, and I, for example, have 3 of them, not counting the workers. A large number of fields leads to data redundancy. It is possible for each phone in the Names table to create a separate line with the last name, but this is easy only for such a simple example, when you need to enter only the last name and you can easily make several entries for Petrov with several phone numbers. And if there are 10 or 20 fields? So, the creation of two tables linked by a foreign key can be seen in Listing 1.6. Listing 1.6. Creating tables linked by a foreign key CREATE TABLE Names (idName int IDENTITY (1,1), vcName varchar (50), CONSTRAINT PK_guid PRIMARY KEY (idName),) CREATE TABLE Phones (idPhone int IDENTITY (1,1), idName int, vcPhone varchar (10), CONSTRAINT PK_idPhone PRIMARY KEY (idPhone), CONSTRAINT FK_idName FOREIGN KEY (idName) REFERENCES Names (idName)) Study the contents of the listing carefully. It is interesting enough because it uses some of the operators that we have already covered, and an additional example will not hurt. For both tables, a key field is created, which is the first, is of type int, and is automatically incremented starting from 1 in increments of one. The key field is made the master key using the CONSTRAINT constraint. In the description of the Phones table, the last line contains a declaration that is new for us, namely, the declaration of a foreign key using the FOREIGN KEY statement. As you can see, this is also a limitation and you will see why a little later. The field in the table, which should be associated with another table, is indicated in brackets. After that comes the keyword REFERENCES (link), the name of the table with which the link should be (Names) and the field name ("idName") in brackets. Thus, we made the connection, which is displayed in Figure 1.4. Attention! A foreign key can only refer to the primary key of another table or to a unique constraint. This means that the REFERENCES keyword must be followed by the table name and only the primary key or field with UNIQUE constraint can be specified in parentheses. Other fields cannot be specified. Now, if you can fill tables with data. The following three commands add the three last names that we saw in Table 1.3: INSERT INTO Names (vcName) VALUES ("Petrov") INSERT INTO Names (vcName) VALUES ("Ivanov") INSERT INTO Names (vcName) VALUES ("Sidorov") If you have already worked with SQL, you can add records for the phone table as well. I'll omit these commands, but you can see them in the foreign_keys.sql file in the Chapter1 directory on the CD. Our task now is to see what the restrictive actions of a foreign key are, let's figure it out. We have indicated an explicit relationship between two fields in different tables. If you try to add to the telephone table a record with an identifier in the "idName" field that does not exist in the field of the same name (the name could have been made differently) in the table with surnames, an error will occur. This will break the relationship between the two tables, and the foreign key constraint will prevent unrelated records from existing. The limitation also applies to changes or deletions of records. For example, if you try to delete a line with the last name Petrov, then a foreign key constraint error will occur. You cannot delete records for which externally related rows exist. To begin with, you need to delete all the phones for this entry, and only after that it will be possible to delete the line with the last name Petrov. When creating a foreign key, you can specify ON DELETE CASCADE or ON UPDATE CASCADE. In this case, if you delete the Petrov record from the Names table or change the identifier, then all the records in the Phones table associated with the Petrov row will be automatically updated. Never. No, you need to write in big letters: NEVER do this. Everything has to be deleted or changed manually. If a user accidentally deletes an entry from the Names table, then the corresponding phones are also deleted. The point then is to create a foreign key if half of its restrictive capabilities disappear! Everything needs to be done only manually, and it is never recommended to change identifiers at all. The deletion of the tables themselves must also start from the subordinate table, that is, from Phones, and only then can the main table Names be deleted. Finally, I'll show you how to beautifully get the correspondence of names and phone numbers from two tables: SELECT vcName, vcPhone FROM Names, Phones WHERE Names.idName = Phones.idName We'll talk more about these kinds of queries in Chapter 2. Now I just gave you an example to show you the power of linked tables. A table can contain up to 253 foreign keys, which is quite enough even for building the most complex databases. Personally, I had to work with databases where the number of foreign keys did not exceed 7 per table. If more, then most likely the database is designed incorrectly, although there are exceptions. The table itself can also have a maximum of 253 foreign keys. Foreign keys in a table are less common, generally no more than 3. Most often, a table can have many references to other tables. A foreign key can refer to the same table in which it is created. For example, you have a table of positions in an organization, as shown in Table 1.5. The table consists of three fields: primary key, foreign key, and job title. In any organization there can be many positions, but it would be quite logical to display their titles and reporting structure in one table. To do this, the foreign key must be linked to the primary key of the job table. Table 1.5. Interconnected table As a result, we get that the CEO has a zero foreign key, i.e. this position is at the head of all the others. For a sales manager and a general director, the foreign key points to the CEO string. This means that these two positions are directly subordinate to to CEO... Etc. Let's see how you can create all this in the form of a SQL query: CREATE TABLE Positions (idPosition int IDENTITY (1,1), idParentPosition int, vcName varchar (30), CONSTRAINT PK_idPosition PRIMARY KEY (idPosition), CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition) REFERENCES Positions (idPosition)) As you can see, the foreign key is simply referring to the same table that we are creating. On the CD, in the Chapter1 directory, you can see in the foreign_keys_to_self.sql file an example of creating this table, filling it with data and displaying positions taking into account their subordination. In the next chapter, we will look at the possibility of working with such tables in more detail. So far, we have considered the classic relationship, when one row of the main data table corresponds to one row from the related table. This relationship is called one-to-many. But there are other relationships, and now we will consider another one - one to one, when one record in the main table is linked to one record in another. To do this, it is enough to link the primary keys of both tables. Since primary keys cannot be repeated, only one row can be related in both tables. The following example creates two tables that have a primary key relationship: CREATE TABLE Names (idName uniqueidentifier DEFAULT NEWID (), vcName varchar (50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID (), vcPhone varchar (10), CONSTRAINT PK_idPEYPEY CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES Names (idName)) Only one of the tables needs a foreign key. Since the relationship goes one to one, it does not matter in which table to create it. The most difficult relationship is many-to-many, where many records from one table match many records from another table. To do this, two tables are not enough, you need three tables. First you need to understand when can a many-to-many relationship be used? Let's say you have two tables: a list of residents in a house and a list of phone numbers. One apartment can have more than one number, which means that two telephones can belong to the same surname. It turns out that the relationship is one to many. On the other hand, in one apartment there can be two families (a communal apartment or just a tenant who uses the owner's phone), which means that the connection between the phone and the resident is also one-to-many. And the most difficult option is to have two telephones in a communal apartment. In this case, both numbers are used by several residents of the apartment. So it turns out that "many" families can use "many" phones (many-to-many communication). How to implement a many-to-many relationship? At first glance, this is not possible in the relational model. About 10 years ago, I was looking for different options for a long time and as a result I simply created one table that was overflowing with data redundancy. But one day, I got one task, thanks to which already from the condition it came to the surface perfect solution- you need to create two tables of residents of apartments and telephones and implement only the primary key in them. Foreign keys are not needed in this table. But the connection between the tables should be through the third, linking table. At first glance, this is difficult and not clear, but once you understand this method, you will see the full power of this solution. Tables 1.6 and 1.7 show examples of tables of names and telephone numbers, respectively. And table 1.8 shows the linking table. Table 1.6. Surname table Table 1.7. Telephone table Table 1.8. Telephone table Let's now see what the data retrieval logic will be in a many-to-many relationship. Let's say that we need to find all the phones that belong to Ivanov. Ivanov has a primary key of 1. Find in the junction table all records for which the "Link to name" field is 1. These will be records 1 and 2. In these records, the "Link to phone" field contains identifiers 1 and 2, respectively, and hence, Ivanov owns the numbers from the telephone table, which are located in lines 1 and 2. Now we will solve the inverse problem - we will determine who has access to the phone number 567575677. This number in the phone table has the key 3. We are looking for all records in the junction table, where in the field "Contact phone" is equal to 3. These are records with numbers 4 and 5, which in the "Link with name" field contain values 2 and 3, respectively. If you now look at the table of surnames, you will see Petrov and Sidorov under numbers 2 and 3. This means that these two residents are using the phone with the number 567575677. Go through all three tables and make sure you understand which phone numbers belong to which residents and vice versa. If you see this connection, you will understand that it is as simple as three kopecks and you can quickly implement it in your projects. CREATE TABLE Names (idName uniqueidentifier DEFAULT NEWID (), vcName varchar (50), CONSTRAINT PK_guid PRIMARY KEY (idName)) CREATE TABLE Phones (idPhone uniqueidentifier DEFAULT NEWID (), vcPhone varchar (10), CONSTRAINT PK_idPhone) CREATE TABLE LinkTable (idLinkTable uniqueidentifier DEFAULT NEWID (), idName uniqueidentifier, idPhone uniqueidentifier, CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable), CONSTRAINT FK_idPhone FOREIGN KEY (idPhone) REFERENCES) Phones CONSTRAINT ) The junction table has two foreign keys, which are associated with the name and telephone tables, and one primary key, which ensures the uniqueness of the records. I chose the GUID field as the primary key, because it is more convenient for solving this particular task. The point is that we need to insert records into two tables and in both cases we need to specify the same key. The GUID value can be generated and then used when inserting data into both tables. You can also use an automatically increasing field as a key, but in this case the problem is a little more difficult to solve, more precisely, it is inconvenient to solve the problem. For example, when adding a phone number, you first need to insert the corresponding row into the table, then find it, determine the key that was assigned to the row, and then make the connection. At this stage, we are limited to creating tables only, and in section 2.8 we will return to this topic and learn and learn how to work with linked tables. Working with a one-to-one and one-to-many relationship is not very different because there are only two tables involved in this schema. The many-to-many relationship is a little more complicated because of the junction table, so we'll look at it separately in Section 2.27. IT APPLIES TO: SQL Server (since 2016) Base SQL data AzureParallel Data Warehouse You can determine the primary key in SQL Server 2016 using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique clustered or nonclustered index. In this section Before you start, follow these steps. Restrictions Safety Creating a primary key with: SQL Server Management Studio There can be only one primary key constraint in a table. All columns with a PRIMARY KEY constraint must be NOT NULL. If the validity of the NULL value is not specified, then the NOT NULL flag is set for all columns with the PRIMARY KEY constraint. Creating a new table with a primary key requires CREATE TABLE permission on the database and ALTER permission on the schema in which the table is created. Creating a primary key on an existing table requires ALTER permission on the table. In Object Explorer, right-click the table to which you want to add a unique constraint and select Constructor. V Table constructor click the row selector for the database column that you want to define as the primary key. To select multiple columns, hold down the CTRL key and click the row selectors for the remaining columns. Right-click the Column Row Selector and choose Set primary key. The source key column is identified by the primary key character in the corresponding row selector. If the primary key consists of more than one column, then there may be duplicate values in one column, but all combinations of values from all columns of the primary key must be unique. When you define a composite key, the order of the columns in the primary key is the same as the order of the columns shown in the table. However, after the primary key has been created, the order of the columns can be changed. For more information see. V object explorer Create request. Execute... This example creates a primary key in the TransactionID column. USE AdventureWorks2012;
What you need
Indexes
PRIMARY KEY
UNION: Combining data
INSERT Continued
INSERT ... ON DUPLICATE KEY UPDATE
Data types
Whole
Fractional numeric data types
String data types
Conclusion
1.2.5. Primary key
1.2.6. External key
One to one relationship
Many to many
Restrictions
Safety
Permissions
Creating a primary key
Create a primary key on an existing table
Go ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID); Go
Creating a primary key in a new table
V object explorer connect to an instance of the Database Engine.
On the standard panel, select Create request.
Copy the following example to the query window and click the button Execute... This example creates a table and defines the primary key for the TransactionID column.
USE AdventureWorks2012;
For more information, see sections, and.
