Relational databases. Primary keys

• Translation

I am posting a continuation of the translation of a series of articles for beginners.
These and subsequent ones contain more information on the merits.
Start - .

4. TABLES AND PRIMARY KEYS

There are 6 lessons in the table. All 6 are different, but for each lesson the values ​​of the same fields are stored in the table, namely: tutorial_id (lesson identifier), title (title) and category (category). Tutorial_id – primary key lesson tables. A primary key is a value that is unique for each record in a table.
In the customer table below, customer_id is the primary key. IN in this case The primary key is also a unique value (number) for each record.

Primary keys in everyday life

Some examples

• The order number that you receive when purchasing in an online store can be the primary key of some order table in the database of this store, because it is a unique value.
• A social security number can be a primary key in some table in a government database because... it is also unique, as in the previous example.
• The invoice number can be used as a primary key in a database table that stores invoices issued to customers.
• A numeric customer number is often used as a primary key in a customer table.

What do these examples have in common? The fact that in all of them a unique, non-repeating value for each record is selected as the primary key. Again. The value of the database table field selected as the primary key is always unique.

What characterizes a primary key? Characteristics of the primary key.

The primary key is used for identification records in the table, so that each record becomes unique. Another analogy... When you call customer service technical support, the operator usually asks you to provide a number (contract, telephone, etc.) by which you can be identified in the system.
If you have forgotten your number, the technical support operator will ask you to provide some other information that will help uniquely identify you. For example, a combination of your birthday and last name. They can also be a primary key, or rather a combination of them.

The primary key is unique.

The primary key always has a unique value. Imagine that its meaning is not unique. Then it could not be used to identify the data in the table. This means that any primary key value can appear only once in the column that is selected as the primary key. RDBMSs are designed in such a way that they will not allow you to insert duplicates into a primary key field, you will get an error.
Another example. Imagine that you have a table with fields first_name and last_name and there are two records:

| first_name | last_name |
| vasya |pupkin |
| vasya |pupkin |

Those. there are two Vasyas. You want to select a specific Vasya from the table. How to do this? The entries are no different from each other. This is where the primary key helps. Add an id column (the classic version of a synthetic primary key) and...

Id | first_name | last_name |
1 | vasya |pupkin |
2 | vasya |pupkin |

Now every Vasya is unique.

Types of primary keys.

Typically the primary key is a numeric value. But it can also be any other data type. It is not common practice to use a string as a primary key (a string is a piece of text), but it is theoretically and practically possible.
Composite primary keys.
Often the primary key consists of one field, but it can also be a combination of several columns, for example two (three, four...). But you remember that the primary key is always unique, which means that the combination of the nth number of fields, in this case 2, must be unique. I'll tell you more about this later.

Auto numbering.

The primary key field is often, but not always, processed by the database itself. You can basically tell the database to automatically assign a unique numeric value to each record when it is created. The database usually starts numbering at 1 and increases this number by one for each record. Such a primary key is called auto-incrementing or auto-numbering. Using auto-incrementing keys – good way to set unique primary keys. The classic name for such a key is a surrogate primary key [As mentioned above. – approx. transl.]. This key does not contain useful information, relating to the entity (object), information about which is stored in the table, which is why it is called surrogate.

5. LINKING TABLES USING FOREIGN KEYS

• defining which entities you want to store in it
• what connections exist between these entities?

One-to-many.

One thing that is important now is that for one-to-many communication it is necessary two separate tables. One for customers, the other for orders. Let's have some practice creating these two tables.

What information will we store? Let's solve the first question.

Designing a customer table.

Orders really belong to the clients, but the order is not minimum block of information, which relates to customers (i.e. this block can be divided into smaller ones: order date, order delivery address, etc., for example).
The fields below are the minimum pieces of information that apply to clients:

• customer_id (primary key) – customer identifier
• first_name - name
• last_name - middle name
• address - address
• zip_code - postal code
• country - country
• birth_date – date of birth
• username – user registration name (login)
• password – password

Let's move on to directly creating this table in SQLyog (of course, you can use any other program). Below is an example of what a table might look like in SQLyog once created. All graphic applications for database management have approximately the same interface structure. You can also create a table using command line without using a graphical utility.

Creating a table in SQLyog. Notice that the primary key (PK) checkbox for the customer_id field is selected. The customer_id field is the primary key. The Auto Incr checkbox is also selected, which means that the database will automatically insert a unique numeric value that, starting at zero, will increment by one each time.

Designing an orders table.
What are the minimum pieces of information we need for an order?

• order_id (primary key) – order identifier
• order_date – order date and time
• customer – the client who made the order

Below is an example of a table in SQLyog.

These two tables ( clients And orders) are connected because the field customer in the orders table references the primary key ( customer_id) customer tables. This connection is called foreign key relationship. You should think of a foreign key as a simple copy (a copy of the value) of another table's primary key. In our case, the field value customer_id from the table clients copied to table orders each time a record is inserted. Thus, with us, each order is tied to the client. And each client can have many orders, as mentioned above.

Creating a foreign key relationship.

You may be wondering, “How can I make sure or how can I see that the customer field in the orders table references the customer_id field in the customers table.” The answer is simple - you cannot do this because I have not yet shown you how to create a connection.
Below is the SQLyog window with the window I used to create the relationship between the tables.

Creating a foreign key relationship between the orders and customers tables.

In the window above, you can see how the customer field of the orders table on the left is linked to the primary key (customer_id) of the customer table on the right.

Now when you look at the data that might be in the tables, you will see that the two tables are related.

Orders are associated with customers through the customer field, which references the customers table.

In the image you can see that the client mary placed three orders, customer pablo placed one, and the client john- not a single one.
You may ask: “A What Is that what all these people ordered?” This good question. You might have expected to see ordered items in the orders table. But this bad example design. How would you fit multiple products into a single entry? Goods are separate entities that should be stored in a separate table. And the relationship between tables orders And goods will be a one-to-many relationship. I'll talk about this further.

6. CREATE AN ENTITY-RELATIONSHIP DIAGRAM

Entities.

In context database design essence is something that deserves your own table in your database model. When you design a database, you must define these essence on the system for which you are creating the database. It is more a matter of dialogue with the client or with yourself in order to find out what data your system will work with.

Let's take an online store as an example. Online store sells goods. Product could become an obvious entity in the online store system. Goods are being orderedclients. So you and I saw two more obvious entities: orders And clients.

The order is paid for by the client... this is interesting. Are we going to create a separate table for payments in our online store database? Maybe. But are payments really the minimum piece of information that relates to orders? This is also possible.

If you're not sure, just think about what payment information you want to store. You may want to store payment method or payment date. But these are still minimal pieces of information that could relate to order. You can change the wording. Payment method - payment method for the order. Payment date – date of payment of the order. So I don't see the need to endure payments into a separate table, although conceptually you could distinguish payments as an entity, because you could think of payments as a container of information (payment method, payment date).

Let's not get too academic.

As you can see, there is a difference between an entity and the table itself in the database, i.e. it's not the same thing. Industry specialists information technology can be VERY academic and pedantic in this matter. I'm not that kind of specialist. This difference depends on your point of view on your data, your information. If you look at data modeling from the point of view software, then you may end up with a lot of entities that cannot be transferred directly to the database. In this tutorial, we're looking at data strictly from a database perspective, and in our little world, the entity is a table.

Hang in there, you're really close to getting your database degree.

As you can see, determining what entities your system has is a bit of an intellectual process that requires some experience and is often a subject for change, revision, reflection, but of course it is not rocket science.

An entity-relationship diagram can be quite large if you are working on a complex application. Some charts may contain hundreds or even thousands of tables.

Connections

So. I told you about the connection one-to-many and I'll tell you more about connections in later parts of this guide, so I won't dwell on it any further for now. Just remember that deciding what relationships your entities will have is an important part of database design and these connections are displayed in the diagram entity-relationship.

Three main entity classes are defined:

1) Rod– an independent entity. The names are placed in a rectangle.

2) Associative– a many-to-many relationship between two or more entities. Associations are treated as entities in their own right. They can participate in other associations and have a set of attributes.

a. Designations (denoting an entity) are many-to-one or one-to-one relationships between two entities. It differs from a characteristic in that it does not depend on the designating entity.

3) Characteristics(characteristic) – a many-to-one or one-to-one relationship between two entities. It is a special case of association. The sole purpose of a characteristic is to describe or clarify some other entity. The existence of a characteristic depends entirely on the entity being characterized.

A key or potential key is just a set of attributes whose values ​​can be used to uniquely find the required instance of an entity.

Minimality means that lexically from a set of any attribute does not allow identifying the entity by the remaining ones.

One of the keys is taken as the primary key and the rest are called alternative keys. Potentially, a key consisting of one attribute is called simple. It is not allowed for the primary key of a core entity to take on an undefined value, otherwise a contradictory situation arises - a non-individual and, therefore, non-existent instance of the core entity will appear. For the same reasons, it is necessary to ensure the uniqueness of the primary key.

If entity C links entities A and B, then it must include foreign keys corresponding to the primary keys of entities A and B.

For each foreign key, three questions need to be addressed:

1) Can an additional foreign key accept null values, in short, can there be some instance of an entity for which the target entity specified by the foreign key is known.

2) What should happen when you try to delete a target entity that is referenced by a foreign key.

There are three possible solutions this issue:

· Cascading

· Restriction

· Setting to a specific value

3) What should happen when trying to update the primary key of a target entity that is referenced by some foreign key.

Thus, for each foreign key in a database project, it is necessary to specialize not only the field or combination of fields that make up that foreign key, but also the answers to the above questions.

Data types and domains.

The relational data model is characterized by a simple data structure and user-friendly presentation.

The relational model is designed to organize data in the form of two-dimensional tables. A relational table is two-dimensional array and has the following properties:

1) Each table element is one data element

2) All columns in the table are homogeneous - all elements in the column have the same data type and length

3) Each column has a unique name

4) There are no identical rows in the table

5) The order of the row of columns can be arbitrary

Data Types

Any data used in programming has its own data types. The relational model requires that the data types used be simple.

Typically, data types are divided into three groups:

1) Simple data types

2) Structured data types

3) reference data types

Simple (atomic) data types have no internal structure. This type of data is called scalar. These include logical, numeric, and string data types. The concept of atomicity is quite relative. Thus, the string data type can be considered as a one-dimensional array of characters, and whole type data as a set of bits. The only important thing here is that when switching to such low level semantics, that is, the meaning of the data, is lost.

Structuring data types is intended to define complex data structures that are constructed from constituent elements, which in turn may have an internal structure (arrays, records, structures).

A reference data type is designed to allow pointing to other data. This data type is intended for procedural languages ​​that have memory areas for storing data.

For relational model data, the type of data used is not that important. The requirement that the data type be simple must be understood to mean that relational operations The internal data structure should not be taken into account.

Domain porno.ru

In the relational data model, the concept of data type is closely related to the concept of domain, which can be considered a clarification of the data type.

Domain – semantic concept. It can be thought of as a subset of the values ​​of some data type.

Domain properties:

1) the domain has a unique name within the database

2) the domain is defined on some simple data type or on another domain

3) a domain may have some logical condition that allows one to describe a subset of data that is valid for a given domain.

4) the domain carries some semantic meaning

For example, some domain D, meaning “employee age”, can be described as some subset of the set of natural numbers

D=(nϵN: n ≥ 18 and n ≤ 60)

The difference between the domain of the concept of a subset is precisely that the domain reflects the semantics of a certain subject area. There may be several domains that coincide as a subset, but carry different meanings. For example, the domains “part weight” and “quantity available” can equally be described as a set of non-negative integers, but the meaning of these domains will be different, and they will be different domains. The main meaning of domain is that domains limit comparisons. It is not logically correct to compare values ​​of different domains, even if they are of the same type. It is syntactically correct to produce a list of all parts whose part weight is greater than the available quantity does not correspond to the meaning of the concepts of quantity and weight.

5. Relations and their properties, attributes and tuples.
The concept of a relationship is a fundamental concept in the relational data model. Relationship attribute:<Имя_атрибута: Имя_домена>. Attribute names must be unique within the relationship. Often the attribute names are the same as the corresponding domain names. Some relation R, defined on the set of domains D 1 , D 2 , ... D n, contains two parts: a header and a body. The relation head contains a fixed number of relation attributes.

(,,…)

(,,… ).

R( ,,…).

The same attitude cannot be depicted different tables, in which the lines go to in different order

3) Attributes are not ordered from left to right. Since each attribute has a unique name within the relationship, the order of the attributes does not matter. The same relationship can be represented by different tables in which the columns are in different orders.

4) All attribute values ​​are atomic.

From the properties of a relation it follows that not every table can define relations. To do this, it needs to have a simple structure, not contain identical rows, any of its columns must contain data of only one type, and all data types used must be simple.

Logic design problem relational base data science consists of making informed decisions about what relationships the database should consist of and what attributes those relationships should have.

The relational data model fixes two basic integrity requirements that must be supported in any relational DBMS.

1) The requirement for the integrity of entities, which is that any map of any relationship must be distinguishable from any other map of this relationship, that is, any relationship must contain a primary key.

2) The referential integrity requirement (foreign key integrity requirement) is that for each foreign key value in the referenced relation,. There must be a map with the same primary key value, or the foreign key value must be undefined.

A primary key is a unique characteristic for each record within a table. Access supports two types of primary keys: simple and compound.

In the role simple key may be one of the existing fields of the table, if this field does not contain empty or duplicate values. Examples of such fields can be vehicle numbers, inventory numbers, and identification codes. A composite key is constructed as a combination of two or more data elements. For the Employees table, for example, you could theoretically use a combination of two fields, Last Name and First Name, as the primary key. However, it is quite possible that the company will have another employee with the same first and last name as someone already employed.

Obviously, the field(s) that claim to be the primary key are subject to fairly stringent requirements. Therefore, it is common practice to create a special identifying field that performs the functions of a key (for example, Customer Code, Order Code). With the addition of each new entry A special value (usually numeric) that uniquely identifies the record is entered into this field in the table. IN Access application Such numbering can be organized using the Counter data type, which assigns each new record its own number, generating a sequence of numbers in increments of 1 (or randomly).

There are basic rules that are accepted for keys in Access:

For convenience, the key field is usually listed first in the table structure;

If a table has a primary key, Access automatically blocks duplicate or Null values ​​from being entered into that field;

Access automatically sorts table records by primary key;

The primary key field is an index that speeds up sorting and searching records.

To set the primary key for a table and complete its creation in Design view, follow these steps:

In design mode, select the field that will act as the primary key;

Click the Key Field button on the Table Designer toolbar or select the main menu command Edit – Key Field (a key symbol will appear on the left next to the name of the selected field);

After specifying the key field, the table must be saved, for which you need to click on the Save button on the table designer toolbar and in the window that opens, enter the name of the table and click on the OK button.

If a primary key is not defined, a warning appears when you exit design mode, and Access prompts you to create a key field before closing the table.

17. Types of connections and their implementation. Referential integrity and its enforcement.

In a relational database, relationships avoid data redundancy. For example, if you create a database containing information about books, you might end up with a table called "Books" that stores parameters for each book, such as its title, publication date, and publisher. In addition, there is additional information about the publisher that you may want to store, such as their phone number, address, and zip code. If you store them in a table with books, then the publisher's phone number will be repeated for each book published by him.

A more correct option is to place information about publishers in a separate “Publishers” table. In this case, the “Books” table will contain links to records in the “Publishers” table.

To maintain synchronization, you must ensure data integrity between the Books and Publishers tables. Data integrity relationships help you ensure that the data in one table matches the data in another. For example, each book in the Books table is associated with a specific publisher in the Publishers table. It is impossible to add a book to a table for a publisher that is not in the database.

Types of relationships between tables

Communication is accomplished by matching data in key columns; These are usually columns that have the same names in both tables. In most cases, the primary key of one table, which contains a unique identifier for each row, is matched with the foreign key of another table. For example, each title you sell can be associated with its sales volume by creating an Title_ID column in the Books table (primary key) and an Title_ID column in the Sales table (foreign key).

There are three types of relationships between tables. The type of relationship that is created depends on how the related columns are defined.

One-to-many relationships

One-to-many relationship is the most common type of relationship. With such a relationship, each row of table A can correspond to many rows of table B, but each row of table B can correspond to only one row of table A. For example, a one-to-many relationship is established between the tables “Publishers” and “Books”: each of the publishers can publish many books, but each book is published by only one publisher.

A one-to-many relationship is created when only one of the columns being linked has a unique constraint or is a primary key.

In Microsoft Access, the side of a one-to-many relationship to which a primary key corresponds is represented by a key symbol. The side of the relationship to which the foreign key corresponds is indicated by an infinity symbol.

Many-to-many relationships

When establishing a many-to-many relationship, each row in table A can correspond to many rows in table B and vice versa. Such a relationship is created using a third table, called a junction table, whose primary key consists of foreign keys associated with tables A and B. For example, a many-to-many relationship is established between the tables “Authors” and “Books”, defined using relationships type "one to many" between each of these tables and the "Authors of Books" table. The primary key of the BookAuthors table is a combination of the columns "Author_ID" (the primary key of the authors table) and "Book_ID" (the primary key of the title table).

One-to-one connections

When establishing a one-to-one relationship, each row of table A can correspond to only one row of table B and vice versa. A one-to-one relationship is created when both related columns are primary keys or have unique constraints.

This type of relationship is rarely used because in this situation the data being linked can usually be stored in a single table. You can use a one-to-one relationship in the following cases:

To split a table that contains too many columns.

To isolate part of a table for security reasons.

To store short-term use data that can most easily be deleted by clearing the table.

To store data that is relevant only to a subset of the main table.

In Microsoft Access, the side of a one-to-one relationship to which a primary key corresponds is represented by a key symbol. The party to which the foreign key corresponds is also indicated by a key symbol.

Creating relationships between tables

When establishing a relationship between tables, the related fields do not have to have the same names. However, they must have the same data type, unless the field that is the primary key is of type Counter. A Count field can only be associated with a Numeric field if the FieldSize property of each is set to the same value. For example, you can link columns of the Counter and Numeric types if the FieldSize property of each is set to Long Integer. Even if both columns being linked are of type Numeric, the FieldSize property value for both fields must be the same.

P rimary Key is a field in a table that uniquely identifies each row/record in a database table. Primary keys must contain unique values. The primary key column cannot have a value.

A table can have only one primary key, which can consist of one or more fields. When multiple fields are used as a primary key, they are called a composite key.

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

Note– You could use these concepts when creating database tables.

Creating a Primary Key

Here is the syntax to define the ID attribute as the primary key in the Customers table.

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

To create a primary key constraint on the "ID" column when the CUSTOMERS table already exists, use the following SQL syntax:

ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID);

Note

If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must have already been declared as not containing NULL values ​​(if the table was created first).

To define a primary key on multiple columns, use the SQL syntax below:

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

To create a primary key constraint on the ID and NAME columns when the CUSTOMERS table already exists, use the following SQL syntax.

ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

Deleting a primary key

You can clear primary key constraints from a table using the syntax given below.

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

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

A note on terminology

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

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

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

Rice. 18.1.

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

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

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

PRIMARY KEY

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

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

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

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

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

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

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

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

UNIQUE

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

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

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

FOREIGN KEY

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