Sql ключи во всех подробностях

Before You Begin! Limits and Restrictions

  • A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

  • When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.

  • FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER.

  • FOREIGN KEY constraints can reference another column in the same table. This is referred to as a self-reference.

  • A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.

  • A FOREIGN KEY constraint specified at the table level must have the same number of reference columns as the number of columns in the constraint column list. The data type of each reference column must also be the same as the corresponding column in the column list.

  • The Database Engine does not have a predefined limit on either the number of FOREIGN KEY constraints a table can contain that reference other tables, or the number of FOREIGN KEY constraints that are owned by other tables that reference a specific table. Nevertheless, the actual number of FOREIGN KEY constraints that can be used is limited by the hardware configuration and by the design of the database and application. A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 (13.x) increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. (Requires at least 130 compatibility level.) The increase has the following restrictions:

    • Greater than 253 foreign key references are supported for DELETE and UPDATE DML operations. MERGE operations are not supported.
    • A table with a foreign key reference to itself is still limited to 253 foreign key references.
    • Greater than 253 foreign key references are not currently available for columnstore indexes, memory-optimized tables, or Stretch Database.
  • FOREIGN KEY constraints are not enforced on temporary tables.

  • If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. For more information, see CLR User-Defined Types.

  • A column of type varchar(max) can participate in a FOREIGN KEY constraint only if the primary key it references is also defined as type varchar(max).

The EmbeddedId Annotation

@EmbeddedId is an alternative to the @IdClass annotation.

Let’s consider another example where we have to persist some information of a Book with title and language as the primary key fields.

In this case, the primary key class, BookId, must be annotated with @Embeddable:

@Embeddable
public class BookId implements Serializable {
    private String title;
    private String language;

    // default constructor

    public BookId(String title, String language) {
        this.title = title;
        this.language = language;
    }

    // getters, equals() and hashCode() methods
}

Then, we need to embed this class in the Book entity using @EmbeddedId:

@Entity
public class Book {
    @EmbeddedId
    private BookId bookId;

    // constructors, other fields, getters and setters
}

Key types and values

While EF Core supports using properties of any primitive type as the primary key, including , , and others, not all databases support all types as keys. In some cases the key values can be converted to a supported type automatically, otherwise the conversion should be specified manually.

Key properties must always have a non-default value when adding a new entity to the context, but some types will be generated by the database. In that case EF will try to generate a temporary value when the entity is added for tracking purposes. After SaveChanges is called the temporary value will be replaced by the value generated by the database.

Important

If a key property has its value generated by the database and a non-default value is specified when an entity is added, then EF will assume that the entity already exists in the database and will try to update it instead of inserting a new one. To avoid this turn off value generation or see how to specify explicit values for generated properties.

SQL внешний ключ при создании таблицы

Следующий SQL создает внешний ключ в столбце «PersonID» при создании таблицы «Orders»:

MySQL:

CREATE TABLE Orders
(
   
OrderID int NOT NULL,
   
OrderNumber int NOT NULL,
   
PersonID int,
   
PRIMARY KEY (OrderID),
   
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
   
OrderID int NOT NULL PRIMARY KEY,
   
OrderNumber int NOT NULL,
   
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

Чтобы разрешить именование ограничения внешнего ключа и для определения ограничения внешнего ключа для нескольких столбцов, используйте следующий синтаксис SQL:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
   
OrderID int NOT NULL,
   
OrderNumber int NOT NULL,
   
PersonID int,
   
PRIMARY KEY (OrderID),
   
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
   
REFERENCES Persons(PersonID)
);

Создать первичный ключ — используя оператор ALTER TABLE

Вы можете создать первичный ключ в PostgreSQL с помощью оператора ALTER TABLE.

Синтаксис

синтаксис для создания первичного ключа с помощью оператора ALTER TABLE в PostgreSQL:

ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (index_col1, index_col2,… index_col_n)

table_name
Имя таблицы для изменения.
constraint_name
Название первичного ключа.
index_col1, index_col2,… index_col_n
Столбцы, составляющие первичный ключ.

Пример

Рассмотрим пример того, как создать первичный ключ, используя оператор ALTER TABLE в PostgreSQL.

ALTER TABLE order_details ADD CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id);

В этом примере мы создали первичный ключ для существующей таблицы order_details с именем order_details_pk. Он состоит из столбца order_detail_id. Мы также можем создать первичный ключ с более чем одним полем, как в примере ниже:

ALTER TABLE order_details ADD CONSTRAINT order_details_pk PRIMARY KEY (order_date, customer_id);

В этом примере мы создали первичный ключ contacts_pk, который состоит из комбинации столбцов last_name и first_name.

Что такое первичный ключ в БД

В базе данных первичный ключ таблицы – это один из ее столбцов (Primary key). Разберемся на примере, как это выглядит. Представим простое отношение студентов университета (назовем его «Студенты»).

Вам будет интересно:Как узнать «битность» системы Windows 7 и более новых версий?

Фамилия Имя Год рождения Факультет Курс Средний балл Иванов Иван 1999 Экономический 3 3,8 Кузнецов Петр 2000 Журналистики 2 4,1 Михайлов Сергей 2000 Технологический 2 4,0 Козлов Евгений 1998 Технологический 4 4,5 Иванов Антон 2000 Юридический 2 3,6

Нам необходимо однозначно определить студента по одному столбцу. Для этого информация в этом столбце для каждой записи должна быть уникальной. Но имеющиеся данные в этом отношении не дают нам однозначно идентифицировать запись, так как на одном курсе и одном факультете могут учиться однофамильцы, тезки и учащиеся с одинаковыми фамилиями и именами. Первичный ключ в базе данных служит для точного определения необходимой строки в отношении. Чаще всего в этом качестве используется числовое поле, автоматически возрастающее с вводом записи (автоинкрементный столбец-идентификатор).

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the «ID» column when the «Persons» table is created:

MySQL:

CREATE TABLE Persons
(
    ID int NOT NULL,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int,
   
PRIMARY KEY (ID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
    ID int NOT NULL PRIMARY KEY,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
    ID int NOT NULL,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int,
   
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

Простой и составной первичный ключ

Primary key может быть простым и составным. Если уникальность записи определяется значением только в одном поле, как описано выше, мы имеем дело с простым ключом. Составной ключ – это первичный ключ базы данных, состоящий из двух и более полей. Рассмотрим следующее отношение клиентов банка.

Ф. И. О. Дата рождения Серия паспорта Номер паспорта Иванов П.А. 12.05.1996 75 0553009 Сергеев В.Т. 14.07.1958 71 4100654 Краснов Л.В. 22.01.2001 73 1265165

Паспорта людей могут содержать одни и те же серии либо номера, но паспортов с одним и тем же сочетанием серии и номера не существует. Таким образом, поля «Серия паспорта» и «Номер паспорта» станут составным ключом указанного отношения, однозначно идентифицируя человека.

Создать primary key – при помощи CREATE TABLE

Вы можете создать primary key в Oracle/PLSQL с помощью оператора CREATE TABLE.

Синтаксис

CREATE TABLE table_name ( column1 datatype null/not null, column2 datatype null/not null, …

CONSTRAINT constraint_name PRIMARY KEY (column1, column2, … column_n) );

Пример

Рассмотрим пример того, как создать primary key, используя оператор Oracle/PLSQL CREATE TABLE:

Oracle PL/SQL

CREATE TABLE supplier
(
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);

1
2
3
4
5
6
7

CREATETABLEsupplier
(

supplier_idnumeric(10)notnull,

supplier_namevarchar2(50)notnull,

contact_namevarchar2(50),

CONSTRAINTsupplier_pkPRIMARYKEY(supplier_id)
);

В этом примере мы создали primary key таблицы supplier c названием supplier_pk. Он состоит только из одного поля — supplier_id. Мы могли бы также создать primary key с больше чем одним полем, как в примере ниже:

Oracle PL/SQL

CREATE TABLE supplier
(
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);

1
2
3
4
5
6
7

CREATETABLEsupplier
(

supplier_idnumeric(10)notnull,

supplier_namevarchar2(50)notnull,

contact_namevarchar2(50),

CONSTRAINTsupplier_pkPRIMARYKEY(supplier_id,supplier_name)
);

Examples

Let’s see an example. We will create an table and a table. Both tables have a primary key called . also has a foreign key composed by a field called , which refers to the primary key. The foreign key constraint name is optional, but we’ll specify it because we want it to appear in error messages: .

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

Now, if we try to insert a book with a non-existing author, we will get an error:

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

The error is very descriptive.

Now, let’s try to properly insert two authors and their books:

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

It worked!

Now, let’s delete the second author. When we created the foreign key, we specified . This should propagate the deletion, and make the deleted author’s books disappear:

DELETE FROM author WHERE name = 'H.P. Lovecraft';

SELECT * FROM book;
+----+--------------+-----------+
| id | title        | author_id |
+----+--------------+-----------+
|  3 | Necronomicon |         1 |
+----+--------------+-----------+

We also specified . This should prevent us from modifying an author’s (the column referenced by the foreign key) if a child row exists:

UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

1.2.5. Первичный ключ

Мы уже достаточно много говорили про ключевые поля, но ни разу их не использовали. Самое интересное, что все работало. Это преимущество, а может недостаток базы данных Microsoft SQL Server и MS Access. В таблицах Paradox такой трюк не пройдет и без наличия ключевого поля таблица будет доступна только для чтения.

В какой-то степени ключи являются ограничениями, и их можно было рассматривать вместе с оператором CHECK, потому что объявление происходит схожим образом и даже используется оператор CONSTRAINT. Давайте посмотрим на этот процесс на примере. Для этого создадим таблицу из двух полей «guid» и «vcName». При этом поле «guid» устанавливается как первичный ключ:

CREATE TABLE Globally_Unique_Data
(
 guid uniqueidentifier DEFAULT NEWID(),
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (Guid)
)

Самое вкусное здесь это строка CONSTRAINT. Как мы знаем, после этого ключевого слова идет название ограничения, и объявления ключа не является исключением. Для именования первичного ключа, я рекомендую использовать именование типа PK_имя, где имя – это имя поля, которое должно стать главным ключом. Сокращение PK происходит от Primary Key (первичный ключ).

После этого, вместо ключевого слова CHECK, которое мы использовали в ограничениях, стоит оператор PRIMARY KEY, Именно это указывает на то, что нам необходима не проверка, а первичный ключ. В скобках указывается одно, или несколько полей, которые будут составлять ключ.

Помните, что в ключевом поле не может быть одинакового значения у двух строк, в этом ограничение первичного ключа идентично ограничению уникальности. Это значит, что если сделать поле для хранения фамилии первичным ключом, то в такую таблицу нельзя будет записать двух Ивановых с разными именами. Это нарушает ограничение первичного ключа. Именно поэтому ключи являются ограничениями и объявляются также как и ограничение CHECK. Но это не верно только для первичных ключей и вторичных с уникальностью.

В данном примере, в качестве первичного ключа выступает поле типа uniqueidentifier (GUID). Значение по умолчанию для этого поля – результат выполнения серверной процедуры NEWID.

Внимание

Только один первичный ключ может быть создан для таблицы

Для простоты примеров, в качестве ключа желательно использовать численный тип и если позволяет база данных, то будет лучше, если он будет типа «autoincrement» (автоматически увеличивающееся/уменьшающееся число). В MS SQL Server таким полем является IDENTITY, а в MS Access это поле типа «счетчик».

Следующий пример показывает, как создать таблицу товаров, в которой в качестве первичного ключа выступает целочисленное поле с автоматическим увеличением:

CREATE TABLE Товары
(
  id int IDENTITY(1, 1),
  товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY (id)
)

Именно такой тип ключа мы будем использовать чаще всего, потому что в ключевом поле будут храниться легкие для восприятия числа и с ними проще и нагляднее работать.

Первичный ключ может состоять из более, чем одной колонки. Следующий пример создает таблицу, в которой поля «id» и «Товар» образуют первичный ключ, а значит, будет создан индекс уникальности на оба поля:

CREATE TABLE Товары1
(
  id int IDENTITY(1, 1),
  Товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY 
         (id, )
)

Очень часто программисты создают базу данных с ключевым полем в виде целого числа, но при этом в задаче четко стоит, что определенные поля должны быть уникальными. А почему не создать сразу первичный ключ из тех полей, которые должны быть уникальны и не надо будет создавать отдельные решения для данной проблемы.

Единственный недостаток первичного ключа из нескольких колонок – проблемы создания связей. Тут приходиться выкручиваться различными методами, но проблема все же решаема. Достаточно только ввести поле типа uniqueidentifier и производить связь по нему. Да, в этом случае у нас получаются уникальными первичный ключ и поле типа uniqueidentifier, но эта избыточность в результате не будет больше, чем та же таблица, где первичный ключ uniqueidentifier, а на поля, которые должны быть уникальными установлено ограничение уникальности. Что выбрать? Зависит от конкретной задачи и от того, с чем вам удобнее работать.

Внешний ключ и целостность данных в БД

Все вышеизложенное приводит нас к внешнему ключу (Foreign key) и целостности БД. Foreign key – это поле, ссылающееся на Primary key внешнего отношения. В таблице успеваемости это столбцы «Студент» и «Дисциплина». Их данные отсылают нас к внешним таблицам. То есть поле «Студент» в отношении «Успеваемость» — это Foreign key, а в отношении «Студент» это первичный ключ в базе данных.

Важным принципом построения баз данных является их целостность. И одно из ее правил – целостность по ссылкам. Это значит, что внешний ключ таблицы не может ссылаться на несуществующий Primary key другого отношения. Нельзя удалить из отношения «Студент» запись с кодом 1000 – Иванов Иван, если на нее ссылается запись из таблицы успеваемости. В правильно построенной БД при попытке удаления вы получите ошибку, что это поле используется.

Существуют и другие группы правил целостности, а также другие ограничения баз данных, которые также заслуживают внимания и должны быть учтены разработчиками.

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the «ID» column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must
already have been declared to not contain NULL values (when the table was first created).

Syntax

Note: MariaDB accepts the REFERENCES clause in ALTER TABLE and CREATE TABLE statements, but that syntax does nothing. MariaDB simply parses it without returning any error or warning, for compatibility with other DBMS’s. However, only the syntax described below creates foreign keys.

Foreign keys are created with CREATE TABLE or ALTER TABLE. The definition must follow this syntax:

] FOREIGN KEY
     (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    
    

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

The clause, if specified, is used in error messages and must be unique in the database.

The columns in the child table must be an index, or the leftmost part of an index. Index prefixes are not supported (thus, TEXT and BLOB columns cannot be used as foreign keys). If MariaDB automatically creates an index for the foreign key (because it does not exist and is not explicitly created), its name will be .

The referenced columns must be a PRIMARY KEY or a UNIQUE index.

Both the foreign key columns and the referenced columns can be PERSISTENT columns. However, the ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL clauses are not allowed in this case.

The foreign key columns and the referenced columns must be of the same type, or similar types. For integer types, the size and sign must also be the same.

The parent and the child table must use the same storage engine, and must not be or partitioned tables. They can be the same table.

Оптимизация индексирования

Какие индексы лучше всего создавать по вышеуказанным сценариям, особенно если мы скажем, что №1 является наиболее распространенным сценарием, поэтому он должен работать очень быстро.

Я бы сказал, что одной из лучших возможностей было бы создание этих индексов:

Таким образом, мы в основном получаем три индекса, из которых вторая группируется.

Поэтому для того, чтобы # 1 работал очень быстро, я установил кластерный индекс в столбце , потому что кластеризованные индексы особенно велики, когда мы проводим сравнение по ним. И мы будем упорядочивать вопросы, хронологически новейшие для самых старых, поэтому я установил направление упорядочения, а также включил тип в кластерный индекс.

Итак, что мы решили с этим?

  1. сценарий № 1 очень эффективно покрывается индексом 2 по мере его кластеризации и полного охвата; мы также можем легко и эффективно выполнять поиск результатов;
  2. сценарий № 2 несколько покрывается уникальным индексом 1 (чтобы получить вопрос) и индексом 3 для получения всех связанных ответов (сценарий № 3), заказанных ; и если мы решили хронологически упорядочить ответы, которые также охватываются индексом 2;

Alternate Keys

An alternate key serves as an alternate unique identifier for each entity instance in addition to the primary key; it can be used as the target of a relationship. When using a relational database this maps to the concept of a unique index/constraint on the alternate key column(s) and one or more foreign key constraints that reference the column(s).

Tip

If you just want to enforce uniqueness on a column, define a unique index rather than an alternate key (see Indexes). In EF, alternate keys are read-only and provide additional semantics over unique indexes because they can be used as the target of a foreign key.

Alternate keys are typically introduced for you when needed and you do not need to manually configure them. By convention, an alternate key is introduced for you when you identify a property which isn’t the primary key as the target of a relationship.

You can also configure a single property to be an alternate key:

You can also configure multiple properties to be an alternate key (known as a composite alternate key):

Finally, by convention, the index and constraint that are introduced for an alternate key will be named (for composite alternate keys becomes an underscore separated list of property names). You can configure the name of the alternate key’s index and unique constraint:

Introduction to MySQL primary key

A primary key is a column or a set of columns that uniquely identifies each row in the table.  The primary key follows these rules:

  • A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.
  • A primary key column cannot have values. Any attempt to insert or update to primary key columns will result in an error. Note that MySQL implicitly adds a constraint to primary key columns.
  • A table can have one an only one primary key.

Because MySQL works faster with integers, the data type of the primary key column should be the integer e.g., . And you should ensure sure that value ranges of the integer type for the primary key are sufficient for storing all possible rows that the table may have.

A primary key column often has the attribute that automatically generates a sequential integer whenever you insert a new row into the table.

When you define a primary key for a table, MySQL automatically creates an index called .

The IdClass Annotation

Let’s say we have a table called Account and it has two columns – accountNumber, accountType – that form the composite key. Now we have to map it in JPA.

As per the JPA specification, let’s create an AccountId class with these primary key fields:

public class AccountId implements Serializable {
    private String accountNumber;

    private String accountType;

    // default constructor

    public AccountId(String accountNumber, String accountType) {
        this.accountNumber = accountNumber;
        this.accountType = accountType;
    }

    // equals() and hashCode()
}

Next, let’s associate the AccountId class with the entity Account.

In order to do that, we need to annotate the entity with the @IdClass annotation. We must also declare the fields from the AccountId class in the entity Account and annotate them with @Id:

@Entity
@IdClass(AccountId.class)
public class Account {
    @Id
    private String accountNumber;

    @Id
    private String accountType;

    // other fields, getters and setters
}

Constraints

If a foreign keys exists, each row in the child table must match a row in the parent table. Multiple child rows can match the same parent row. A child row matches a parent row if all its foreign key values are identical to a parent row’s values in the parent table. However, if at least one of the foreign key values is , the row has no parents, but it is still allowed.

MariaDB performs certain checks to guarantee that the data integrity is enforced:

  • Trying to insert non-matching rows (or update matching rows in a way that makes them non-matching rows) in the child table produces a 1452 error (SQLSTATE ‘23000’).
  • When a row in the parent table is deleted and at least one child row exists, MariaDB performs an action which depends on the clause of the foreign key.
  • When a value in the column referenced by a foreign key changes and at least one child row exists, MariaDB performs an action which depends on the clause of the foreign key.
  • Trying to drop a table that is referenced by a foreign key produces a 1217 error (SQLSTATE ‘23000’).
  • A TRUNCATE TABLE against a table containing one or more foreign keys is executed as a DELETE without WHERE, so that the foreign keys are enforced for each row.

The allowed actions for and are:

  • : The change on the parent table is prevented. The statement terminates with a 1451 error (SQLSTATE ‘2300’). This is the default behavior for both and .
  • : Synonym for .
  • : The change is allowed and propagates on the child table. For example, if a parent row is deleted, the child row is also deleted; if a parent row’s ID changes, the child row’s ID will also change.
  • : The change is allowed, and the child row’s foreign key columns are set to .
  • : Only worked with PBXT. Similar to , but the foreign key columns were set to their default values. If default values do not exist, an error is produced.

The delete or update operations triggered by foreign keys do not activate triggers and are not counted in the and status variables.

Foreign key constraints can be disabled by setting the server system variable to 0. This speeds up the insertion of large quantities of data.

Отношение один к одному

Пока что мы рассмотрели классическую связь, когда одной строке основной таблицы данных соответствует одна строка из связанной таблицы. Такая связь называется один ко многим. Но существуют и другие связи, и сейчас мы рассмотрим еще одну – один к одному, когда одна запись основной таблице связана с одной записью другой. Чтобы это реализовать, достаточно связать первичные ключи обеих таблиц. Так как первичные ключи не могут повторяться, то в обеих таблицах связанными могут быть только одна строка.

Следующий пример создает две таблицы, у которых создана связь между первичными ключами:

 
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 PRIMARY KEY (idPhone),
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Names (idName)
)

Внешний ключ нужен только у одной из таблиц. Так как связь идет один к одному, то не имеет значения, в какой таблице создать его.

Ссылка на основную публикацию