Creating primary keys. Constraint PRIMARY KEY Primary key syntax

In this article we will try to consider everything related to keys in SQL: what are the creation and restrictions of keys for? In general: it will be boring 😉

The plan for today is:

In relational database theory - keys These are certain entities created to establish certain restrictions that maintain the integrity and availability of data in database tables.

In simple words, the keys are in sql are created to indicate additional functionality for a column. Be it uniqueness or the fact that the column references another table (foreign key).

Primary key

A column that must be unique in the database is marked with a primary key. Primary key or primary key means that the value of the primary key column cannot be repeated in the table. Thus, this key allows you to uniquely identify a record in the table without fear that the column value will be repeated. Just an example: let’s say you have a users table. This table has the following fields: full name, year of birth, telephone. How to identify a user? Parameters such as full name and telephone number cannot be trusted. After all, we can have several users not only with the same last name, but also with the same first name. The phone number may change over time and the user with the phone number may not be the one in our database.

This is why the primary key was invented. Once assigned a unique identifier and that’s it. IN mySql on the example of which we perform all the examples from the field AUTO_INCREMENT cannot be set unless you indicate that this is a primary key.

I don't think it's worth mentioning that a field marked as a primary key cannot be empty when creating a record.

External key ( foreign key)

Is there some more external key (foreign key). It is also called reference. It is needed to link tables together.

If you look at the image above, the foreign key will be the supplier field in the shoes table. Typically, when creating a table, you specify a column of unique integer values. How we did it when we created the table supplier

Column supplier_id will be unique for each entry. Its value will appear on the column provider in the table shoes. I suggest immediately looking at an example of how a foreign key is created.

Creating a foreign key

create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, foreign key (supplier) references supplier (supplier_id));

As you can see in the example above, the syntax for creating a foreign key is quite simple. You need to add a field to the table, and then declare this field as a foreign key and indicate where it will refer. In this case the field supplier will refer to the field supplier_id in the table supplier

Composite key (composite key)

As for a composite key, these are several primary keys in a table. Thus, having created composite key, the uniqueness of the record will be checked by the fields that are combined into this key.

There are situations when, when inserting into a table, you need to check a record for uniqueness using several fields at once. This is why a composite key was invented. For example, I'll create a simple table with composite key to show the syntax:

Create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));

In the example above, two fields are combined into a composite key and there will be no records in the table with these identical fields.

That's all about the keys in SQL. This small tutorial is a preparation for the article where we will look in detail at how to combine tables so that they form a single database.

IT APPLIES TO: SQL Server (since 2016)Azure SQL DatabaseAzure SQL Data WarehouseParallel Data Warehouse

You can define a primary key in SQL Server 2016 by 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 begin, complete the following steps.

    Restrictions

    Safety

    Create a primary key using:

    SQL Server Management Studio

Restrictions

    A table can only have one primary key constraint.

    All columns with a PRIMARY KEY constraint must be NOT NULL. If nullability is not specified, then the NOT NULL flag is set for all columns with the PRIMARY KEY constraint.

Safety

Permissions

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.

Creating a Primary Key

    In Object Explorer, right-click the table to which you want to add a unique constraint and select Constructor.

    IN Table designer Click the row selector for the database column that you want to define as the primary key. To select multiple columns, press and hold the CTRL key and click the row selectors for the remaining columns.

    Right-click the Column Row Selector and select Set primary key.

The key source column is identified by the primary key symbol 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 is created, the order of the columns can be changed. For more information, see .

Create a primary key on an existing table

    IN Object Explorer

    Create a request.

    Execute. This example creates a primary key on the TransactionID column.

    USE AdventureWorks2012; GO ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID); GO

Create a primary key in a new table

    IN Object Explorer Connect to an instance of the Database Engine.

    On the standard panel, select Create a request.

    Copy the following example into the query window and click the button Execute. This example creates a table and defines a primary key for the TransactionID column.

    USE AdventureWorks2012; GO CREATE TABLE Production.TransactionHistoryArchive1 (TransactionID int NOT NULL , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)); GO

    For more information, see sections , and .

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 learning the language SQL and apply our skills to DBMS MySQL. We'll explore indexes, data types, and more complex queries.

What you need

Please refer to the "What you need" section of the first part, which is located.

If you want to run these examples on your server, do the following:

  1. Open your console MySQL and log in.
  2. Create the database "my_first_db" using the query CREATE, if it has not been created previously.
  3. Change the base using the operator USE.

Indexes

Indexes (or keys) are commonly used to improve the speed of execution of statements that select data (such as SELECT) from the tables.

They are an important part of good database architecture; it is difficult to classify them as "optimization". Typically indexes are added initially, but can be added later using a query ALTER TABLE.

The main reasons for indexing database columns are:

  • Almost every table has a primary key ( PRIMARY KEY), usually this is the "id" column.
  • If a column is intended to store unique values, it must have a unique index ( UNIQUE).
  • If you need a frequent search on a column (using it in a sentence WHERE), it must have a regular index ( INDEX).
  • If a column is used to relate to another table, it should be a foreign key if possible ( FOREIGN KEY) or a regular index.

Primary key (PRIMARY KEY)

Almost all tables have a primary key, usually an integer with an auto-increment option ( AUTO_INCREMET).

Subqueries often cause significant performance degradation, so use them with caution.

UNION: Combining data

Using query UNION, you can combine the results of multiple SELECT queries.

This example combines states that begin with the letter "N" with states with larger populations:

(SELECT * FROM states WHERE name LIKE "n%") UNION (SELECT * FROM states WHERE population > 10000000);

Please note that New York is a large state and begins with the letter "N". However, it appears only once in the list, because duplicates are removed automatically.

Also the beauty of requests UNION is that they can be used to combine queries against different tables.

For example, we have tables employees (employees), managers (managers) and customers (clients). Each table has a field with an email address. If we want to get all E-mail addresses in one request, we can do the following:

(SELECT email FROM employees) UNION (SELECT email FROM managers) UNION (SELECT email FROM customers WHERE subscribed = 1);

By completing this request, we will receive the mailing addresses of all employees and managers, and only those clients who are subscribed to the mailing list.

INSERT Continued

We already talked about the request INSERT in the previous article. Now that we've looked at indexes, we can talk about additional query capabilities INSERT.

INSERT ... ON DUPLICATE KEY UPDATE

This is the most commonly used condition. First the request tries to execute INSERT, and if the request fails due to a duplicate primary ( PRIMARY KEY) or unique ( UNIQUE KEY) key, then the request is executed UPDATE.

Let's first create a test table.

This is a food storage table. The "stock" field stores the number of products available in stock.

Now let's try to insert an existing value into the table and see what happens.

We received an error.

Let's say we got a new bakery and want to update the database, but we don't know if there is already an entry in the database. We can first check for the existence of the record and then run another insert query. Or you can do everything in one simple query:

Works the same as INSERT, but with one important feature. If the record already exists, it is deleted and then the query is executed INSERT, and we will not receive any error messages.

Please note, because a completely new row is inserted, the autoincrement field is increased by one.

This is a way to prevent duplication errors from occurring in the first place to keep the application running. You may need to insert a new line without printing any errors, even if there is a duplication.

There are no errors or updated lines.

Data types

Each column in a table must be a specific type. We have already used types INT, VARCHAR And DATE, but did not dwell on them in detail. We'll also look at a few more data types.

Let's start with numeric data types. I divide them into two groups: Integers and fractions.

Whole

An integer column can only store natural numbers (no decimal point). By default they can be positive or negative. If the option is selected UNSIGNED, then only positive numbers can be stored.

MySQL supports 5 types of integers of different sizes and ranges:

Fractional numeric data types

These types can store fractional numbers: FLOAT, DOUBLE and DECIMAL.

FLOAT takes 4 bytes, DOUBLE takes 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 decimal places.

String data types

You can guess from the name that they can store strings.

CHAR(N) can store N characters and has a fixed value. For example, CHAR(50) must always contain 50 characters per line in the entire column. Maximum possible value is 255 characters

VARCHAR(N) works the same, but the range may vary. N - denotes the maximum value. If the stored string is shorter than N characters, then it will take up less space on the hard drive. The maximum possible value is 65535 characters.

Variants of the TEXT type are more suitable for long strings. TEXT has a limit of 65535 characters, MEDIUMTEXT has a limit of 16.7 million, and LONGTEXT has a limit of 4.3 billion characters. MySQL usually stores them in separate storages on the server, so that the main storage is as small and fast as possible.

.

Conclusion

Thanks for reading the article. SQL is an important language and tool in a web developer's arsenal.

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;

During the database design process, decisions are made about which tables should be included in the database, what names they will have (identifiers), what types of data will be needed to build the tables, and what users will have access to each of them. In addition, to effectively create tables, you need to answer the following questions:

  • What type and size of columns will make up each of the tables, and what names should you choose for the table columns?
  • Which columns can contain a NULL value?
  • Will they be used? integrity constraints, default values ​​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 the MS SQL Server environment, use the command:

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

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

Typically, the owner of a table (dbo) is the person who created it.

<Выражение>sets the value for calculated column. Calculated Columns- these are virtual columns, i.e. 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 bound by one or more operators. Subqueries cannot participate in such an expression. Calculated Columns can be included in a SELECT clause when specifying a list of columns to be returned as a result of the query. Calculated Columns cannot be included in a foreign key; default values ​​are not used for them. Besides, 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 counter column. Only one column with this property can be defined per table. You can additionally specify the initial value and increment step. If these values ​​are not specified, they both default to 1. If NOT FOR REPLICATION is specified with the IDENTITY keyword, the server will not automatically generate values ​​for that column, but will allow arbitrary values ​​to be inserted into the column.

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

<ограничение_столбца>::= [ CONSTRAINT constraintname ] ( [ NULL | NOT NULL ] | [ (PRIMARY KEY | UNIQUE ) [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR=fillfactor ] [ ON (filegroupname | DEFAULT ) ] ] ] | [ [ FOREIGN KEY ] REFERENCES table_genus_name [(column_name_table_genus) ] [ 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_gen [ ,...n])] [ ON DELETE ( CASCADE | NO ACTION ) ] [ ON UPDATE ( CASCADE | NO ACTION ) ] | NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] (logical expression) )

Let us consider individual parameters of the presented designs associated with 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 constraint, CHECK constraint.

Primary key constraint (PRIMARY KEY)

A table typically has a column or 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 that are part of the primary key. It speeds up data access of these columns when used 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 it as primary key column (or group of columns) for which primary key constraints are not executed, the primary key will not be created, and the system will display an error message.

Because the PRIMARY KEY constraint ensures that data is unique, it is often defined to counter columns. Creation integrity constraints PRIMARY KEY is possible both during creation and when changing the table. One of the appointments primary key is the provision referential integrity data from several tables. Naturally, this can only be implemented by defining corresponding foreign keys in other tables.

FOREIGN KEY CONSTRAINT

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

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

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

Foreign key columns can contain a NULL value, but the FOREIGN KEY constraint check is ignored. The foreign key can be indexed, then the server will find the necessary data faster. The foreign key is defined both at creation and at 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 a relationship column in a record of the parent table in the presence of a child record is blocked, as well as deleting the parent record (prohibition of cascading changes and deletions), which is guaranteed by the default ON DELETE NO ACTION and ON UPDATE NO ACTION parameters. To enable cascading effects, use the ON DELETE CASCADE and ON UPDATE CASCADE parameters.

RELATED ARTICLES