PRIMARY KEY constraint. Using multiple keys in one transformation

Considered the issues necessary for the developer to create client-server applications using the Firebird DBMS, which was the development of the Borland Interbase 6 DBMS. It provides an overview of the concepts and models of the client / server architecture, as well as practical advice on working with client libraries Firebird. Features of types are described in detail SQL data, Data Manipulation Language (DML), and Data Definition Language (DDL) syntax and operators. Much attention is paid to the description of transactions and advice on their use when developing applications. It covers client-side and server-side programming, writing triggers and stored procedures, creating and using database events, handling errors in code on the server, and much more. The material is accompanied by numerous examples, tips and practical advice.

For database developers

Book:

PRIMARY KEY constraint

Sections on this page:

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 restriction is that the restriction must have a certain 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 - by at least v existing versions including 1.5 - and the query optimizer will not function correctly when indexes are duplicated.

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 reverse Special attention per schema with respect to primary key names 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.

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 number or social security 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.

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 make your theoretical assumptions about uniqueness fallacious. 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 with a medium to high probability of duplication across 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.

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 at the time of adding new line... 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.

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 selected columns have semantic meaning (they are "meaningful data") and are clearly vulnerable to external changes and manual input errors.

* Foreign keys from other tables that refer to this table will duplicate every element of the composite key. Referential integrity is compromised by the use of 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 large size... Large indexes use more database pages, which makes index operations (sort, join, and compare) slower than necessary.

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 item fully exist on its own with a single internal rule 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.

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.

The following sequence creates and commits a domain that does not allow empty value, then a primary key column based on that domain is defined and a PRIMARY KEY constraint is applied to that column at the same time:

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;

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.

In the process of database design, a decision is made about which tables should be included in the database, what names (identifiers) they will have, what types of data will be required to build the tables, and which users will have access to each of them. Moreover, for effective creation of tables, you need to answer the following questions:

• What type and size of columns will each of the tables compose, what should you choose the names for the columns of the tables?
• What columns can contain null value ?
• Will be used integrity constraints, defaults and rules for columns?
• Is indexing of columns necessary, what types of indexes will be applied to specific columns?
• Which columns will be included in the primary and foreign keys.

To create tables in MS environment SQL Server the command is used:

<определение_таблицы>:: = CREATE TABLE [dbname. [Owner]. | owner. ] table_name (<элемент_таблицы>[, ... n])

<элемент_таблицы> ::= {<определение_столбца>} | <имя_столбца>AS<выражение> | <ограничение_таблицы>

Usually the owner of the table (dbo) is the person who created it.

<Выражение>sets the value for calculated column. Calculated Columns- these are virtual columns, that is, they are not physically stored in the table and are calculated using the values ​​of the columns of the same table. In the expression for calculated column there may be regular column names, constants, and functions linked by one or more operators. Subqueries cannot participate in such an expression. Calculated Columns can be included in SELECT section when specifying a list of columns to be returned from the query. Calculated Columns cannot be part of a foreign key, they do not use default values. Moreover, calculated columns cannot participate in INSERT and DELETE operations.

<определение_столбца>:: = (column_name<тип_данных>) [[DEFAULT<выражение>] | [IDENTITY (start, step)]]] [<ограничение_столбца>] [... n]]

In the column definition, pay attention to the IDENTITY parameter, which indicates that the corresponding column will be column-count... Only one column with this property can be defined for a table. You can additionally specify initial value and the increment step. If these values ​​are not specified, then by default they are both equal to 1. If with keyword IDENTITY is NOT FOR REPLICATION, the server will not automatically generate values ​​for this column, but will allow arbitrary values ​​to be inserted into the column.

As constraints are used column constraints and table constraints... The difference between them is that column constraint applies only to a specific field, while table constraint- to groups of one or more fields.

<ограничение_столбца>:: = [CONSTRAINT constraint_name] ([NULL | NOT NULL] | [(PRIMARY KEY | UNIQUE) [CLUSTERED | NONCLUSTERED] [WITH FILLFACTOR = fill_factor] [ON (filegroup_name | DEFAULT)]]] | [[FOREIGN KEY] REFERENCES table_name [(column_name_table_name)] [ON DELETE (CASCADE | NO ACTION)] [ON UPDATE (CASCADE | NO ACTION)] [NOT FOR REPLICATION]] | CHECK [NOT FOR REPLICATION] (<лог_выражение>) } <ограничение_таблицы>:: = ([(PRIMARY KEY | UNIQUE) [CLUSTERED | NONCLUSTERED] ((column_name [, ... n]))] | FOREIGN KEY [(column_name [, ... n])] REFERENCES table_name [(column_name_table_name [ , ... n])] [ON DELETE (CASCADE | NO ACTION)] [ON UPDATE (CASCADE | NO ACTION)] | NOT FOR REPLICATION] | CHECK [NOT FOR REPLICATION] (log_expression))

Let's consider some parameters of the presented constructions related to restrictions. data integrity. Integrity constraints take precedence over triggers, rules, and defaults. TO integrity constraints relate primary key constraint PRIMARY KEY, foreign key constraint FOREIGN KEY, UNIQUE constraint, NULL value constraint, CHECK check.

Primary Key Constraint

A table usually has a column, or a combination of columns, whose values ​​uniquely identify each row in the table. This column (or columns) is called primary key table and is needed to ensure its integrity. If the primary key contains more than one column, then the values ​​within one column may be duplicated, but any combination of the values ​​of all columns primary key must be unique.

While creating primary key SQL Server automatically creates a unique index on the columns in the primary key. It speeds up access to the data of these columns when using primary key in requests.

A table can have only one PRIMARY KEY constraint, and none of the columns included in the primary key can be null. When trying to use as primary key the column (or group of columns) for which primary key constraints are not executed, the primary key will not be created, and the system will issue an error message.

Since the PRIMARY KEY constraint guarantees data uniqueness, it is often defined for columns-counters... Creation integrity constraints PRIMARY KEY is possible both during creation and changing the table... One of the appointments primary key is to ensure referential integrity data from multiple tables. Naturally, this can only be done by defining corresponding foreign keys in other tables.

FOREIGN KEY constraint

Foreign key constraint is the main mechanism for maintaining referential integrity between tables relational base data. The column of the child table, defined as a foreign key in the FOREIGN KEY parameter, is used to refer to the column of the parent table that is in it primary key... The name of the parent table and its columns primary key are specified in the REFERENCES clause. Data in columns defined as a foreign key can only take on the same values ​​as in its associated columns primary key parent table. It is not necessary to match the column names for the relationship between the child and parent tables. A primary key can be defined for a column with the same name, while a column that has a FOREIGN KEY constraint can have a completely different name. The only requirement is that the columns are matched by the type and size of the data.

The primary key can be referenced not only by columns of other tables, but also by columns located in the same table as the actual primary key; this allows you to create recursive structures.

A foreign key can be associated with more than just primary key another table. It can be defined for UNIQUE constrained columns of the second table or any other columns, but the tables must be in the same database.

Foreign key columns can contain NULL values, but the FOREIGN KEY constraint check is ignored. The foreign key can be indexed so that the server can find the data it needs faster. A foreign key is defined both at creation and changing tables.

Limitation referential integrity specifies the requirement that for every record in the child table there must be a record in the parent table. In this case, changing the value of the link column in the parent table record in the presence of a child record is blocked, as well as the deletion of the parent record (prohibition of cascading changes and deletions), which is guaranteed by the ON DELETE NO ACTION and ON UPDATE NO ACTION parameters accepted by default. The ON DELETE CASCADE and ON UPDATE CASCADE parameters should be used to enable cascading.