MySQL: CREATE TABLE создание таблиц

Инструкция CREATE TABLE создает таблицу с заданным именем. По умолчанию таблицы создаются в базе данных по умолчанию с использованием механизма хранения InnoDB. Если таблица существует, если нет базы данных по умолчанию или если база данных не существует, то возникает ошибка.

MySQL не имеет ограничений на количество таблиц. Базовая файловая система может иметь ограничение на количество файлов, представляющих таблицы. Отдельные механизмы хранения могут налагать ограничения, специфичные для механизма. Движок InnoDB позволяет использовать до 4 миллиардов таблиц.

Содержание:

• Синтаксис инструкции CREATE TABLE;
• Описание параметров, используемых CREATE TABLE;
• Описание инструкций, используемых CREATE TABLE;
• Создание пустой таблицы на основе определения другой таблицы;
• Создание таблицы с данными на основе другой таблицы.

Синтаксис инструкции CREATE TABLE.

Синтаксис инструкции CREATE TABLE очень обширный. В связи с этим рассмотрим только основные операторы/атрибуты и их параметры.

-- ОСНОВНОЙ СИНТАКСИС СОЗДАНИЯ ТАБЛИЦЫ
CREATE TABLE [IF [NOT] EXISTS] tbl_name (
    -- определение столбцов таблицы
    col_name1 data_type [NOT NULL | NULL] [DEFAULT literal | (expr)]
    [AUTO_INCREMENT] [UNIQUE KEY | PRIMARY KEY]
    [[CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED]] 
    [COMMENT 'string'],
    col_name2 ... ,
    -- определение индексов таблицы как отдельные инструкции
    INDEX | KEY index_name (col_name1[, col_name2, ... ]) [USING BTREE | HASH]
    -- определение вторичного ключа
    FOREIGN KEY (col_id) REFERENCES table_parent(col_parent_id) 
        [ON DELETE RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT]
        [ON UPDATE RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT]
    ) -- определение свойств таблицы 
    ENGINE = InnoDB|MyISAM|MEMORY|CSV|ARCHIVE
    CHARACTER SET=charset_name COLLATE=collation_name

-- СОЗДАНИЕ ПУСТОЙ ТАБЛИЦЫ НА ОСНОВЕ
-- ОПРЕДЕЛЕНИЯ ДРУГОЙ ТАБЛИЦЫ
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    LIKE orig_tbl

-- СОЗДАНИЕ ТАБЛИЦЫ С ДАННЫМИ 
-- НА ОСНОВЕ ДРУГОЙ ТАБЛИЦЫ 
CREATE [TEMPORARY] TABLE new_tbl [AS] SELECT * FROM orig_tbl;

-- ДОБАВЛЕНИЕ ИНДЕКСОВ В ТАБЛИЦУ
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[USING BTREE | HASH] ON tbl_name (col_name1[, col_name2, ... ])

Описание параметров, используемых CREATE TABLE.

• tbl_name: уникальное имя таблицы для базы данных. Можно указать как db_name.tbl_name, чтобы создать таблицу в определенной базе данных. Это работает независимо от того, существует ли база данных по умолчанию. Если использовать идентификаторы в кавычках, то нужно указывать имена базы данных и таблиц отдельно. Например, 'mydb'.'mytbl', а не 'mydb.mytbl'.
• col_name1/col_name2: строка, уникальное имя столбца/колонки в создаваемой таблице.
• data_type: представляет тип данных в определении столбца. Полное описание синтаксиса, доступного для указания типов данных столбца, а также информацию о свойствах каждого типа смотрите в материале "Типы хранимых данных в БД MySQL".
• literal: значение по умолчанию, которое будет принимать столбец, если его значение не указано в инструкции INSERT при добавлении записи в таблицу. Тип этого значения должен соответствовать значению data_type.
• (expr) - выражение заключенное в скобки. Это выражение, значение которого будет использоваться соответствующим оператором. Обычно выражение содержит ограниченные конструкции. Если выражение будет содержать запрещенные конструкции, то возникает ошибка.
• check_name необязательное уникальное имя ограничения, которое используется для проверки вставляемых значений в колонки таблицы. Если оно не задано, то MySQL генерирует его самостоятельно.
• index_name: строка, уникальное имя индекса для столбца/столбцов в создаваемой таблице.
• col_id: имя столбца этой таблицы, используемого в качестве внешнего ключа FOREIGN KEY.
• table_parent: родительская таблица с первичным ключом PRIMARY KEY
• col_parent_id: имя столбца, определенного как первичный ключ PRIMARY KEY родительской таблицы.
• charset_name: имя кодировки, в которой будут храниться символьные и текстовые типы данных столбцов таблицы. Список доступных кодировок можно посмотреть командой SHOW CHARACTER SET;. Подробнее в материале "Преобразование типов и кодировки в БД MySQL".
• collation_name: имя кодировки, которая будет использоваться при поиске/сравнении/сортировке символьных и текстовых типов данных. Список доступных кодировок можно посмотреть командой SHOW COLLATION;. Подробнее в материале "Преобразование типов и кодировки в БД MySQL".
• orig_tbl: имя оригинальной таблицы.
• new_tbl: уникальное имя новой таблицы для базы данных.

Описание инструкций/атрибутов, используемых CREATE TABLE.

• IF [NOT] EXISTS;
• NOT NULL | NULL;
• DEFAULT literal;
• AUTO_INCREMENT;
• PRIMARY KEY;
• UNIQUE KEY;
• CONSTRAINT [check_name] CHECK (expr);
• COMMENT 'string';
• INDEX|KEY ... [USING BTREE | HASH];
• FOREIGN KEY ... REFERENCES ... [ON DELETE|UPDATE ...];
• ENGINE=...;
• [DEFAULT] CHARACTER SET=charset_name;
• [DEFAULT] COLLATE=collation_name.

IF NOT EXISTS:

Оператор IF NOT EXISTS предотвращает возникновение ошибки, если таблица уже существует. Однако нет никакой проверки того, что существующая таблица имеет структуру, идентичную структуре, указанной в операторе CREATE TABLE.

Пример:

-- если таблица с именем `enimals` уже 
-- существует, то инструкция CREATE TABLE 
-- выполнена не будет. 
CREATE TABLE IF NOT EXISTS enimals (
    id int(8),
    name varchar(30)
);

Если необходимо пересоздать таблицу (удалить и создать заново), то необходимо выполнить следующие MySQL-инструкции:

-- удаляем `enimals` (если существует)
DROP TABLE IF EXISTS enimals;
-- заново создаем `enimals`
CREATE TABLE enimals (
    id int(8),
    name varchar(30)
);

NOT NULL | NULL:

Если операторы NULL и NOT NULL не указаны, то столбец создается так, как если бы был указан NULL.

Индексированные столбцы, которые могут иметь значения NULL поддерживают только механизмы хранения InnoDB, MyISAM и MEMORY. В других случаях необходимо объявить индексированные столбцы как NOT NULL, иначе возникнет ошибка.

Пример:

CREATE TABLE IF NOT EXISTS enimals (
    -- столбец `id` создается 
    -- со значением `NULL`
    id int(8),
    -- столбцу `name` запрещено 
    -- иметь значение `NULL`
    name varchar(30) NOT NULL
);

Теперь, при операциях добавления записей инструкцией INSERT, и если столбец name не указан для вставки значения, то будет возникать ошибка целостности данных.

Для ввода данных в столбец с NOT NULL, который не имеет явного определения DEFAULT, если оператор INSERT или REPLACE не содержит значения для столбца или оператор UPDATE устанавливает для столбца значение NULL, то MySQL обрабатывает столбец в соответствии с режимом SQL, действующим в момент время:

• Если включен строгий режим SQL, то для транзакционных таблиц возникает ошибка, и инструкция откатывается.
• Если строгий режим не включен, то MySQL устанавливает для столбца неявное значение по умолчанию для типа данных столбца.

Предположим, что таблица t определена следующим образом:

CREATE TABLE t (i INT NOT NULL);

В этом случае у i нет явного значения по умолчанию, поэтому в строгом режиме каждое из следующих утверждений выдает ошибку, и строка не вставляется. Когда не используется строгий режим, только третий оператор выдает ошибку; неявное значение по умолчанию вставляется для первых двух операторов, но третий завершается ошибкой, поскольку DEFAULT(i) не может выдать значение:

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

DEFAULT literal:

Оператор DEFAULT literal|(expr) определяет значение по умолчанию, которое будет принимать столбец, если его значение не указано в инструкции INSERT при добавлении записи в таблицу. Тип этого значения должен соответствовать значению data_type.

Пример:

CREATE TABLE t1 (
  i     INT DEFAULT -1,
  c     VARCHAR(10) DEFAULT '',
  price DOUBLE(16,2) DEFAULT 0.00
);

Значение по умолчанию, указанное в операторе DEFAULT, может быть буквальной константой или выражением expr. За одним исключением, значения выражений по умолчанию необходимо заключать в круглые скобки (expr), чтобы отличить их от значений по умолчанию литеральных констант. Примеры:

CREATE TABLE t1 (
  -- литеральные значения по умолчанию
  i INT         DEFAULT 0,
  c VARCHAR(10) DEFAULT '',
  -- выражения по умолчанию
  f FLOAT       DEFAULT (RAND() * RAND()),
  b BINARY(16)  DEFAULT (UUID_TO_BIN(UUID())),
  d DATE        DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR),
);

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

• Допускаются литералы, встроенные функции (как детерминированные, так и недетерминированные) и операторы.
• Подзапросы, параметры, переменные, хранимые функции и загружаемые функции не допускаются.
• Значение выражения по умолчанию не может зависеть от столбца с атрибутом AUTO_INCREMENT.
• Значение выражения по умолчанию для одного столбца может ссылаться на другие столбцы таблицы, за исключением того, что ссылки на сгенерированные столбцы или столбцы со значениями выражения по умолчанию должны относиться к столбцам, которые ранее были определены в таблице. То есть значения выражения по умолчанию не могут содержать прямые ссылки на сгенерированные столбцы или столбцы со значениями выражения по умолчанию.
• Для CREATE TABLE ... LIKE и CREATE TABLE ... SELECT целевая таблица сохраняет значения выражений по умолчанию из исходной таблицы.

Если значение выражения по умолчанию ссылается на недетерминированную функцию, то любой оператор, который вызывает вычисление выражения, небезопасен для репликации на основе операторов. Сюда входят такие операторы, как INSERT и UPDATE. В этой ситуации, если ведение двоичного журнала отключено, то инструкция выполняется как обычно.

При выполнении операций вставки новой строки INSERT, значение по умолчанию для столбца с выражением по умолчанию можно вставить, либо опуская имя столбца, либо указывая столбец как DEFAULT (так же, как для столбцов с литеральными значениями по умолчанию):

mysql> CREATE TABLE t4 (
    uid BINARY(16) DEFAULT (UUID_TO_BIN(UUID()))
);
-- имя столбца `uid` опускается
mysql> INSERT INTO t4 () VALUES();
-- столбцу `uid` присваивается значение DEFAULT
mysql> INSERT INTO t4 () VALUES(DEFAULT);
-- смотрим результат
mysql> SELECT BIN_TO_UUID(uid) AS uid FROM t4;
+--------------------------------------+
| uid                                  |
+--------------------------------------+
| f1109174-94c9-11e8-971d-3bf1095aa633 |
| f110cf9a-94c9-11e8-971d-3bf1095aa633 |
+--------------------------------------+

AUTO_INCREMENT:

Целочисленный столбец или столбец с плавающей запятой может иметь дополнительный атрибут AUTO_INCREMENT. Когда происходит добавление INSERT и в индексированный столбец AUTO_INCREMENT вставляется значение NULL (рекомендуется) или 0, то для него устанавливается следующее значение последовательности. Обычно это value +1, где value - это наибольшее значение столбца, находящегося в данный момент в таблице. Последовательности AUTO_INCREMENT начинаются с 1.

Столбец, который определяется с атрибутом AUTO_INCREMENT должен содержать оператор UNIQUE KEY.

Чтобы получить значение AUTO_INCREMENT после вставки строки, необходимо использовать MySQL-функцию LAST_INSERT_ID(). В языка Python, значение AUTO_INCREMENT можно получить используя метод курсора Cursor.lastrowid модуля MySQLdb.

Пример использования атрибута AUTO_INCREMENT:

CREATE TABLE IF NOT EXISTS enimals (
    id int(8) NOT NULL AUTO_INCREMENT UNIQUE KEY,
    name varchar(30) NOT NULL DEFAULT ''
);

PRIMARY KEY:

Уникальный индекс (первичный ключ), в котором все ключевые столбцы должны быть определены как NOT NULL. Если они явно не объявлены как NOT NULL, то MySQL объявляет их так неявно. Таблица может иметь только один PRIMARY KEY. Имя index_name первичного ключа PRIMARY KEY всегда создается как PRIMARY, поэтому это имя нельзя использовать в качестве имени для любого другого типа индекса.

Если PRIMARY KEY не определен, а приложение запрашивает из таблицы PRIMARY KEY, то MySQL в качестве PRIMARY KEY возвращает первый индекс UNIQUE KEY, который не имеет столбцов NULL.

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

Пример:

CREATE TABLE IF NOT EXISTS enimals (
    -- определение PRIMARY KEY в описании столбца
    id int(8) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(30) NOT NULL DEFAULT ''
);

В созданной таблице сначала размещается PRIMARY KEY, затем все UNIQUE KEY, а затем неуникальные индексы INDEX. Это помогает оптимизатору MySQL расставить приоритеты, какой индекс использовать, а также быстрее обнаруживать повторяющиеся ключи UNIQUE.

Первичный ключ PRIMARY KEY может быть индексом с несколькими столбцами. Однако нельзя создать индекс с несколькими столбцами, используя атрибут PRIMARY KEY в спецификации столбца. Необходимо использовать отдельное предложение...

CREATE TABLE IF NOT EXISTS enimals (
    id int(8) NOT NULL AUTO_INCREMENT,
    name varchar(30) NOT NULL DEFAULT '',
    nickname varchar(30) NOT NULL DEFAULT '',
    -- определение PRIMARY KEY, который содержит 
    -- несколько столбцов в отдельном предложении
    PRIMARY KEY (id, name) USING BTREE
);

Имя индекса index_name первичного ключа PRIMARY KEY можно не задавать, т.к. оно всегда будет PRIMARY. Для остальных индексов, если не указать имя индексу index_name, то ему будет присвоено то же имя, что и первому проиндексированному столбцу, с необязательным суффиксом (_2, _3, ...), чтобы сделать его уникальным. Можно посмотреть имена индексов таблицы, используя команду SHOW INDEX FROM tbl_name.

UNIQUE KEY:

Индекс UNIQUE KEY создает ограничение, согласно которому все значения в индексе должны быть различными. При попытке добавить новую строку со значением ключа, совпадающим с существующей строкой, возникает ошибка. Для всех движков MySQL, индекс UNIQUE допускает несколько значений NULL для столбцов, которые могут содержать NULL. Если для столбца в индексе UNIQUE указывается значение префикса, то значения столбца должны быть уникальными в пределах длины префикса.

Пример определения UNIQUE KEY в описании столбца:

CREATE TABLE IF NOT EXISTS enimals (
    -- определение UNIQUE KEY в описании столбца
    id int(8) NOT NULL UNIQUE KEY,
    name varchar(30) NOT NULL DEFAULT ''
);

Индекс с уникальными значениями UNIQUE KEY может быть состоять из нескольких столбцов. Однако нельзя создать индекс с несколькими столбцами, используя атрибут UNIQUE KEY в спецификации/описании столбца. Необходимо использовать отдельное предложение...

Пример определения UNIQUE KEY в отдельном предложении:

CREATE TABLE IF NOT EXISTS enimals (
    id int(8) NOT NULL ,
    name varchar(30) NOT NULL DEFAULT '',
    nickname varchar(30) DEFAULT '',
    -- определение UNIQUE KEY в отдельном предложении
    -- может содержать несколько столбцов
    UNIQUE KEY enimals_idname (id, name) USING BTREE
);

CONSTRAINT [check_name] CHECK (expr):

Оператор CHECK позволяет создавать ограничения для проверки вставляемых значений в колонки таблицы.

Для операторов INSERT, UPDATE и REPLACE, если ограничение CHECK оценивается как FALSE, то возникает ошибка. В случае возникновения ошибки, обработка уже примененных изменений различается для транзакционных и нетранзакционных движков хранения, а также зависит от того, действует ли строгий режим SQL.

Для операторов INSERT IGNORE, UPDATE IGNORE, если ограничение CHECK оценивается как FALSE, то появляется предупреждение, а вставка/обновление строки пропускается.

• До MySQL 8.0.16, (узнать версию SELECT VERSION();) инструкция CREATE TABLE разрешает только ограниченную версию CHECK (expr), которая анализируется и игнорируется.
• Начиная с MySQL 8.0.16, CREATE TABLE разрешает основные функции ограничений таблицы и столбца CHECK для всех движков хранения.

-- расширенный синтаксис для определения 
-- в отдельном предложении
CONSTRAINT [check_name] CHECK (expr) [[NOT] ENFORCED]

Необязательный CONSTRAINT check_name указывает имя ограничения. Если опущено, то MySQL генерирует имя из имени таблицы, литерала _chk_ и порядкового номера (1, 2, 3, ...). Имена ограничений имеют максимальную длину 64 символа. Они чувствительны к регистру.

Выражение expr задает условие ограничения как логическое выражение, которое должно оцениваться как TRUE или UNKNOWN (для значений NULL) для каждой строки таблицы. Если условие оценивается как FALSE, то оно не выполняется и происходит нарушение ограничения. Эффект нарушения зависит от выполняемого оператора, как описано далее в этом разделе.

Необязательная инструкция [NOT] ENFORCED указывает, будет ли применяться проверка. Другими словами включает/отключает проверку, особенно полезно при изменении таблицы ALTER TABLE:

• Если она опущена или указана как ENFORCED, то ограничение создается и применяется.
• Если указано значение NOT ENFORCED, ограничение создается, но не применяется.

Ограничение CHECK указывается либо как ограничение таблицы, либо как ограничение столбца:

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

Рассмотрим определение таблицы:

CREATE TABLE t1 (
  CHECK (c1 <> c3),
  c1 INT CHECK (c1 > 10),
  c3 INT CHECK (c3 < 100),
  CONSTRAINT c1_nonzero CHECK (c1 <> 0),
  CHECK (c1 > c3)
);

Определение включает ограничения таблицы и ограничения столбца в именованном и неименованном форматах:

• Первое ограничение - это табличное ограничение: оно возникает вне любого определения столбца, поэтому оно может (и делает) ссылаться на несколько столбцов таблицы. Это ограничение содержит прямые ссылки на еще не определенные столбцы. Имя ограничения не указано, поэтому MySQL его генерирует.
• Следующие два ограничения являются ограничениями столбца: каждое из них встречается в определении столбца и, таким образом, может относиться только к определяемому столбцу. MySQL генерирует имя ограничения для каждого из них.
• Последние два ограничения являются ограничениями таблицы. Одному из них имя c1_nonzero задано явно. MySQL сгенерирует имя ограничения для другого.

Как уже упоминалось, MySQL генерирует имя для любого ограничения CHECK, указанного без него. Чтобы увидеть имена, сгенерированные для предыдущего определения таблицы, используйте SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE t1;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),
  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),
  CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),
  CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),
  CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))
) ENGINE=InnoDB;

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

Выражения условий expr должны соответствовать следующим правилам. Возникает ошибка, если выражение expr содержит запрещенные конструкции.

• Разрешены несгенерированные и сгенерированные столбцы, за исключением столбцов с атрибутом AUTO_INCREMENT и столбцов, которые определены в других таблицах.
• Допускаются литералы, детерминированные встроенные функции и операторы. Функция является детерминированной, если при одних и тех же данных в таблицах несколько вызовов дают один и тот же результат независимо от подключенного пользователя. Примеры недетерминированных функций, которые не соответствуют этому определению: CONNECTION_ID(), CURRENT_USER(), NOW().
• Сохраненные функции и загружаемые функции не разрешены.
• Параметры хранимой процедуры и функции не разрешены.
• Переменные (системные переменные, пользовательские переменные и локальные переменные сохраненной программы) не допускаются.
• Подзапросы не допускаются.

COMMENT 'string':

Комментарий для столбца можно указать с помощью параметра COMMENT длиной до 1024 символов. Комментарий отображается операторами SHOW CREATE TABLE и SHOW FULL COLUMNS. Он также отображается в столбце COLUMN_COMMENT таблицы INFORMATION_SCHEMA.COLUMNS.

Пример:

CREATE TABLE IF NOT EXISTS enimals (
    id int(8) NOT NULL AUTO_INCREMENT UNIQUE KEY,
    name varchar(30) NOT NULL DEFAULT '' COMMENT 'Название животного'
);

INDEX|KEY ... [USING BTREE | HASH]:

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

Некоторые механизмы хранения позволяют указывать тип индекса при создании индекса, например: INDEX (col_name) USING BTREE или INDEX USING BTREE (col_name).

CREATE TABLE lookup (
    id INT NOT NULL DEFAULT 0,  
    -- создание индексированного столбца
    -- без указания имени индекса
    INDEX (id) USING BTREE
) ENGINE = MEMORY;

Оператор KEY обычно является синонимом INDEX. Атрибут ключа PRIMARY KEY также может быть указан как просто KEY, если он указан в определении столбца. Это было реализовано для совместимости с другими системами баз данных. Другими словами, оператор KEY указывается в определении столбца, а INDEX для описания индекса в отдельном предложении.

Пример определения индекса столбца url_addr в отдельном предложении:

CREATE TABLE IF NOT EXISTS contents (
    id int(8) NOT NULL UNIQUE KEY,
    url_addr varchar(150) NOT NULL DEFAULT '',
    title varchar(150) NOT NULL DEFAULT '',
    -- создание индекса столбца `url_addr` с явным
    -- указанием имени `contents_url` индекса 
    INDEX contents_url (url_addr) USING BTREE
);

FOREIGN KEY (col_id) REFERENCES table_parent(col_parent_id) [ON DELETE|UPDATE ...]:

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

Ограничение внешнего ключа FOREIGN KEY включает родительскую таблицу table_parent, содержащую начальные значения столбцов col_parent_id, и дочернюю таблицу со значениями столбцов col_id, которые ссылаются на значения родительского столбца col_parent_id. Ограничение внешнего ключа определяется для дочерней таблицы.

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

Когда операция UPDATE или DELETE влияет на значение ключа в родительской таблице, которая имеет совпадающие строки в дочерней таблице, то результат зависит от действия, указанного в операторах ON UPDATE и ON DELETE (смотри синтаксис) предложения FOREIGN KEY.

• CASCADE: удаляет или обновляет строку из родительской таблицы и автоматически удаляет или обновляет соответствующие строки в дочерней таблице. Поддерживаются как ON DELETE CASCADE, так и ON UPDATE CASCADE. Между двумя таблицами не нужно определять несколько предложений ON UPDATE CASCADE, которые воздействуют на один и тот же столбец в родительской или дочерней таблицах.
• SET NULL: удаляет или обновляет строку из родительской таблицы и устанавливает для столбца или столбцов внешнего ключа в дочерней таблице значение NULL. Поддерживаются оба предложения ON DELETE SET NULL и ON UPDATE SET NULL. Перед установкой этого действия убедитесь, что столбцы в дочерней таблице НЕ определены как NOT NULL.
• RESTRICT: отклоняет операцию удаления или обновления для родительской таблицы. Указание RESTRICT (или NO ACTION) равносильно пропуску предложения ON DELETE или ON UPDATE.
• NO ACTION: ключевое слово из стандартного SQL. В MySQL эквивалент RESTRICT. Сервер MySQL отклоняет операцию удаления или обновления для родительской таблицы, если в таблице, на которую ссылаются, есть связанное значение внешнего ключа. Некоторые системы баз данных имеют отложенные проверки, и NO ACTION является отложенной проверкой. В MySQL ограничения внешнего ключа проверяются немедленно, поэтому NO ACTION совпадает с RESTRICT.
• SET DEFAULT: это действие распознается синтаксическим анализатором MySQL, но InnoDB отклоняют определения таблиц, содержащие предложения ON DELETE SET DEFAULT или ON UPDATE SET DEFAULT.

Примеры использования FOREIGN KEY:

Этот простой пример связывает родительскую и дочернюю таблицы через внешний ключ с одним столбцом:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

Это более сложный пример, в котором таблица product_order имеет внешние ключи для двух других таблиц. Один внешний ключ ссылается на индекс из двух столбцов в таблице product. Другой ссылается на одностолбцовый индекс в таблице customer:

CREATE TABLE product (
    category INT NOT NULL, id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
)   ENGINE=INNODB;

CREATE TABLE customer (
    id INT NOT NULL,
    PRIMARY KEY (id)
)   ENGINE=INNODB;

CREATE TABLE product_order (
    no INT NOT NULL AUTO_INCREMENT,
    product_category INT NOT NULL,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,

    PRIMARY KEY(no),
    INDEX (product_category, product_id),
    INDEX (customer_id),

    FOREIGN KEY (product_category, product_id)
      REFERENCES product(category, id)
      ON UPDATE CASCADE ON DELETE RESTRICT,

    FOREIGN KEY (customer_id)
      REFERENCES customer(id)
)   ENGINE=INNODB;

Условия и ограничения FOREIGN KEY.

На FOREIGN KEY распространяются следующие условия и ограничения:

• Родительские и дочерние таблицы должны использовать один и тот же механизм хранения, и их нельзя определить как временные таблицы.
• Соответствующие столбцы во внешнем ключе и ссылочном ключе должны иметь одинаковые типы данных. Размер и знак типов с фиксированной точностью, таких как INTEGER и DECIMAL, должны быть одинаковыми. Длина строковых типов не обязательно должна быть одинаковой. Для столбцов недвоичных (символьных) строк набор символов и параметры сортировки должны быть одинаковыми.
• MySQL требует наличия индексов для внешних ключей и ключей, на которые они ссылаются (для обеспечения быстрого доступа не требующего сканирования таблицы).
• Движок InnoDB позволяет внешнему ключу ссылаться на любой индексный столбец или группу столбцов. Однако в таблице на которую ссылается внешний ключ должен быть индекс, в котором ссылочные столбцы или группа столбцов являются первыми столбцами в том же порядке.
• Префиксы индексов для столбцов внешнего ключа не поддерживаются. Следовательно, столбцы BLOB и TEXT не могут быть включены во внешний ключ, поскольку индексы этих столбцов всегда должны включать длину префикса.
• Таблицу в отношении внешнего ключа нельзя изменить для использования другого движка хранения. Чтобы изменить механизм хранения, вы должны сначала удалить все ограничения внешнего ключа.
• Внешний ключ не может ссылаться на виртуальный сгенерированный столбец.

ENGINE = InnoDB|MyISAM|MEMORY|CSV|ARCHIVE:

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

Оператор ENGINE может принимать значения (перечислены часто используемые движки):

• InnoDB: Безопасные для транзакций таблицы с блокировкой строк и внешними ключами. Механизм хранения по умолчанию для новых таблиц. (Используется там, где при частых транзакциях критична потеря данных.)
• MyISAM: Механизм двоичного портативного хранилища, который в основном используется для рабочих нагрузок, доступных только для чтения или в основном для чтения. (Отлично подходит для сайтов. Работает быстрее, чем InnoDB)
• MEMORY: Данные для этого механизма хранения хранятся только в памяти.
• CSV: Таблицы, в которых хранятся строки в формате значений, разделенных запятыми.
• ARCHIVE: Механизм хранения большого количества записей, которые не нужно изменять и часто использовать. То есть, это движок архивного хранилища.
• NDB: Кластеризованные, отказоустойчивые, основанные на памяти таблицы, поддерживающие транзакции и внешние ключи. Также известен как NDBCLUSTER.

CREATE TABLE product (
    category INT NOT NULL, 
    id INT NOT NULL,
    price DECIMAL,
    PRIMARY KEY(category, id)
    -- механизм хранения данных 
    -- указывается для всей таблицы
)   ENGINE=MyISAM;

CHARACTER SET = charset_name:

Инструкция CHARACTER SET = charset_name указывает набор символов (кодировку) по умолчанию для таблицы. Инструкция CHARSET является синонимом CHARACTER SET.

Если имя набора символов charset_name указывается как DEFAULT (CHARACTER SET=DEFAULT), то используется набор символов (кодировка), используемая базой данных, в которой создается таблица.

Пример:

CREATE TABLE product (
    id INT NOT NULL,
    price DECIMAL
    -- кодировка для хранения данных 
    -- указывается для всей таблицы
)   ENGINE=InnoDB CHARACTER SET=utf8;

-- или
CREATE TABLE product (
    id INT NOT NULL,
    price DECIMAL
) ENGINE=MyISAM CHARSET=utf8;

Список доступных кодировок можно посмотреть командой SHOW CHARACTER SET;.

COLLATE= collation_name:

Инструкция COLLATE= collation_name задает параметры сравнения/сортировки по умолчанию для данных, хранящихся в таблице.

Если имя набора символов collation_name указывается как DEFAULT (COLLATE=DEFAULT), то используется набор символов (кодировка) для сортировки, используемая базой данных, в которой создается таблица.

Пример:

CREATE TABLE product (
    id INT NOT NULL,
    price DECIMAL
    -- кодировка для сравнения/сортировки 
    -- указывается для всей таблицы
)   ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci;

-- или
CREATE TABLE product (
    id INT NOT NULL,
    price DECIMAL
    -- используем кодировку для сравнения строк,
    -- заданную для базы данных при ее создании
)   ENGINE=InnoDB COLLATE=DEFAULT;

Список доступных кодировок для выполнения сравнения/сортировки можно посмотреть командой SHOW COLLATION;. Подробнее в материале "Преобразование типов и кодировки в БД MySQL".

Создание пустой таблицы на основе определения другой таблицы.

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

CREATE TABLE new_tbl LIKE orig_tbl;

Копия создается с использованием той же версии формата хранения таблиц, что и исходная таблица. Оператор LIKE работает только для базовых таблиц, но не для представлений, созданных при помощи инструкции CREATE VIEW ....

Важно. Нельзя выполнить CREATE TABLE ... LIKE, пока действует оператор LOCK TABLES. CREATE TABLE ... LIKE выполняет те же проверки, что и CREATE TABLE. Это означает, что если текущий режим SQL отличается от режима, действовавшего при создании исходной таблицы, определение таблицы может быть сочтено недопустимым для нового режима и привести к сбою оператора.

Для CREATE TABLE ... LIKE:

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

Если исходная таблица является таблицей TEMPORARY, то CREATE TABLE ... LIKE не сохраняет TEMPORARY. Чтобы создать временную целевую таблицу, нужно использовать инструкцию CREATE TEMPORARY TABLE ... LIKE.

Создание таблицы с данными на основе другой таблицы.

Можно создать одну таблицу из другой, добавив оператор SELECT в конце оператора CREATE TABLE:

-- синтаксис
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

-- например инструкция ниже создаст таблицу
-- `test1` cо столбцами `a`, `b`, `c` и заполнит
--  ее данными из таблицы `test`
CREATE TABLE test1 SELECT a, b, c FROM test;

Можно использовать ключевое слово TEMPORARY при создании временной таблицы. Временная таблица видна только в рамках текущего сеанса и автоматически удаляется при закрытии сеанса. Это означает, что два разных сеанса могут использовать одно и то же имя временной таблицы, не конфликтуя друг с другом или с существующей не-TEMPORARY таблицей с таким же именем. (Существующая таблица скрыта до тех пор, пока временная таблица не будет удалена.)

Пример создания временной таблицы на основе существующей:

CREATE TEMPORARY TABLE test1 SELECT a, b, c FROM test;

TEMPORARY таблицы имеют очень слабую связь с базами данных (схемами). Удаление базы данных не приводит к автоматическому удалению временных таблиц, созданных в этой базе данных.

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

При создании новой обычной таблицы на основе существующей, MySQL создает новые столбцы для всех элементов в SELECT.

Например:

CREATE TABLE test (
    a INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (a), KEY(b)
) ENGINE=InnoDB 
-- создаем столбцы `b` и `c` и заполняем 
-- таблицу `test` данными из `test2`
SELECT b, c FROM test2;

Это создает таблицу InnoDB с тремя столбцами: a, b и c. Параметр ENGINE является частью оператора CREATE TABLE и не должен использоваться после оператора SELECT, это приведет к синтаксической ошибке. То же самое верно и для других параметров CREATE TABLE, таких как CHARSET.

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

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

Для каждой строки в foo в таблице bar вставляется строка со значениями из foo и значениями по умолчанию для новых столбцов.

В таблице, полученной в результате CREATE TABLE ... SELECT, столбцы, созданные только в части CREATE TABLE, идут первыми. После этого идут столбцы, созданные в обеих частях или только в части SELECT. Тип данных столбцов SELECT можно переопределить, также указав столбец в части CREATE TABLE.

Если при копировании данных в таблицу возникают ошибки, то таблица автоматически удаляется и не создается.