Primary Keys SQLite
В SQLite первичный ключ — это отдельное поле или комбинация полей, однозначно определяющая запись. Таблица может иметь только один первичный ключ.
СОВЕТ . Хотя стандарты SQL-89 и SQL-92 не допускают значения NULL в первичном ключе, SQLite разрешает использование NULL при определенных обстоятельствах. Мы настоятельно рекомендуем не использовать значение NULL в первичном ключе.
Создать первичный ключ (оператор CREATE TABLE)
Первичный ключ может быть создан при выполнении оператора CREATE TABLE в SQLite.
Синтаксис
Синтаксис для создания первичного ключа с помощью оператора CREATE TABLE в SQLite:
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, . pk_col_n)
);
CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
.
);
table_name
Имя таблицы, которую вы хотите создать.
column1 , column2
Столбцы, которые вы хотите создать в таблице.
constraint_name
Название первичного ключа.
pk_col1 , pk_col2 , . pk_col_n
Столбцы, составляющие первичный ключ.
Пример
Рассмотрим пример того, как создать первичный ключ, используя оператор CREATE TABLE в SQLite. Мы начнем с очень простого, где наш первичный ключ состоит всего из одного столбца.
Sqlite уникальное сочетание столбцов
Разумеется, такое возможно. Это называется «составной ключ», обычно речь идет о составном первичном ключе.
Составной первичный ключ создается вместе с таблицей так:
create table TheTable ( a, b, c, CONSTRAINT PRIMARY KEY (a, b) ) Дополнительный ключ, он же ограничение уникальности, задается так:
create table TheTable ( a, b, c, CONSTRAINT UNIQUE (a, b) ) Разница между первичным ключом и дополнительным ключом — в том, что первичный ключ может быть только один и не может содержать необязательные атрибуты (указанные в нем атрибуты автоматически получают модификатор NOT NULL).
Также ограничение уникальности можно наложить при помощи уникального индекса:
CREATE UNIQUE INDEX IX_TheTable ON TheTable (a, b) Разница между уникальным индексом и дополнительным ключом — в семантике. Ограничение уникальности — это часть модели данных, уникальный же индекс — это способ оптимизации поиска в БД. Но реально во всех известным мне реализациях они, как правило, взаимозаменяемы.
Еще одна возможность индекса, которой нет у ограничений — частичные индексы:
CREATE UNIQUE INDEX IX_TheTable ON TheTable (a, b) WHERE c <> 4 Теперь если атрибут c равен 4, то значения пары атрибутов (a, b) могут повторяться. Не знаю, правда, зачем такое может потребоваться.
PS отвечал я, в основном, про SQLite — но и для других СУБД многое из этого ответа применимо, поскольку «вырастает» из теории реляционных баз данных и из стандарта языка SQL.
SQL Primary Keys
В этом учебном материале вы узнаете, как создать и удалить первичный ключ в SQL, с синтаксисом и примерами.
Что такое первичный ключ в SQL?
В SQL первичный ключ — это отдельное поле или комбинация полей, которые однозначно определяют запись. Ни одно из полей, являющихся частью первичного ключа, не может содержать значение NULL. Таблица может иметь только один первичный ключ.
Вы можете использовать оператор CREATE TABLE или оператор ALTER TABLE для создания первичного ключа в SQL.
Создать первичный ключ (оператор CREATE TABLE)
Первичный ключ может быть создан при выполнении оператора CREATE TABLE в SQL.
Синтаксис
Синтаксис для создания первичного ключа с помощью оператора CREATE TABLE в SQL.
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, . pk_col_n)
);
CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
.
);
table_name Имя таблицы, которую вы хотите создать column1 , column2 Столбцы, которые вы хотите создать в таблице constraint_name Название первичного ключа pk_col1 , pk_col2 , . pk_col_n Столбцы, составляющие первичный ключ
Пример
Давайте посмотрим, как создать первичный ключ с помощью оператора CREATE TABLE в SQL. Мы начнем с очень простого, где наш первичный ключ состоит всего из одного столбца.
Например:
CREATE TABLE suppliers
( supplier_id int NOT NULL ,
supplier_name char (50) NOT NULL ,
contact_name char (50),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
В этом примере мы создали первичный ключ для таблицы suppliers , который называется sources_pk . Он состоит только из одного столбца — столбца supplier_id .
Мы могли бы использовать альтернативный синтаксис и создать этот же первичный ключ следующим образом.
CREATE TABLE suppliers
( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY ,
supplier_name char (50) NOT NULL ,
contact_name char (50)
Оба эти синтаксиса действительны при создании первичного ключа только с одним полем.
Если вы создаете первичный ключ, который состоит из 2-х или более столбцов, вы ограничены использованием только первого синтаксиса, в котором первичный ключ определен в конце оператора CREATE TABLE.
Например:
CREATE TABLE contacts
( last_name VARCHAR (30) NOT NULL ,
first_name VARCHAR (25) NOT NULL ,
birthday DATE ,
CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)
Этот пример создает первичный ключ с именем contacts_pk , который состоит из комбинации столбцов last_name и first_name . Поэтому каждая комбинация last_name и first_name должна быть уникальной в таблице contacts .
Создать первичный ключ (оператор ALTER TABLE)
Если ваша таблица уже существует и вы хотите добавить к ней позже первичный ключ, вы можете использовать оператор ALTER TABLE для создания первичного ключа.
Синтаксис
Синтаксис для создания первичного ключа с помощью оператора ALTER TABLE в SQL.
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, . column_n);
table_name Имя таблицы для изменения. Это таблица, к которой вы хотите добавить первичный ключ constraint_name Название первичного ключа column1 , column2 , . column_n Столбцы, составляющие первичный ключ
Пример
Давайте посмотрим, как создать первичный ключ с помощью оператора ALTER TABLE в SQL. Скажем так, у нас уже есть таблица suppliers , созданная в нашей базе данных. Мы могли бы добавить первичный ключ к таблице suppliers с помощью следующего оператора ALTER TABLE.
Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Часть 11.3: Первичные ключи в базах данных SQLite: PRIMARY KEY. Ограничение первичного ключа
- 24.06.2016
- SQLite библиотека, Базы данных
- Комментариев нет
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Мы разобрались с ограничениями уровня столбца и узнали, какие возможности есть у СУБД для поддержания целостности данных на уровне таблицы. Теперь давайте более подробно поговорим про ограничения уровня таблицы и первым ограничение, которое мы рассмотрим, будет ограничение первичного ключа или просто PRIMARY KEY.
Первичные ключи в базах данных SQLite: PRIMARY KEY. Ограничение первичного ключа.
В теории баз данных ключ или ключевой атрибут — это столбец, который позволяет однозначно идентифицировать таблицу в базе данных. На практике, ни одна СУБД в мире не знает о таком столбце и не знает теории баз данных. На практике есть первичный ключ, который является правилом, которое SQLite ни когда не нарушит. SQLite будет следить всегда за тем, чтобы в столбце PRIMARY KEY были всегда уникальные и вечные значения.
В этой записи мы рассмотрим ограничения первичного ключа в базах данных SQLite в теории и на практике, так же узнаем, почему первичный ключ это не только правило базы данных, но еще и индекс, а так же поговорим о том, как реализовать составной первичный ключ для таблицы базы данных SQLite3
Первичный ключ, как ограничение уровня таблицы в базах данных SQLite. PRIMARY KEY в SQLIte3
Первичный ключ, это не только ключевой атрибут, который идентифицирует таблицу в базе данных и даже не только обязательное условие второй нормальной формы, а, соответственно, и третьей нормальной формы. Первичный ключ или столбец PRIMARY KEY – это ограничение уровня таблицы для любой реляционной СУБД.
Ограничение уровня таблицы – это правило, которое никогда не должна нарушать ни одна СУБД и не должна никому давать это делать. Первичный ключ или PRIMARY KEY в базах данных SQLite – это столбец, значения которого должны быть уникальными и вечными, и что бы не произошло, что бы не случилось, SQLite никогда не нарушит правило уникальности и вечности первичного ключа.
Если мы задали первичный ключ для таблицы при помощи PRIMARY KEY, то, во-первых, мы сами должны заботиться об уникальности и вечности, во-вторых, SQLite будет делать это за нас. Если вы видите, что значения в столбце повторяющиеся и SQLite не дает вам их добавить, то, вероятнее всего, вы ошиблись при проектировании базы данных и назначили не тот столбец в качестве PRIMARY KEY.
Когда мы создаем первичный ключ, как ограничение таблицы, то вместо PRIMARY KEY мы вправе использовать конструкцию UNIQUE KEY, SQLite это позволяет и никаких проблем с таким подходом у вас не будет.
Помимо всего прочего, первичный ключ в базе данных SQLite, вернее столбец, который мы назначили, как PRIMARY KEY, является еще и индексом таблицы. Благодаря индексам таблицы операция выборки данных из базы данных при помощи команды SELECT происходит значительно быстрее, но место на диске база данных начинает занимать больше
Вывод: первичный ключ в базах данных SQLite3 или PRIMARY KEY – это правила, которые нельзя нарушать: правила уникальности и вечности значений столбца PRIMARY KEY. Так же первичный ключ является индексом таблицы в базе данных SQLite. Столбец, который объявлен, как PRIMARY KEY – это индекс, которому мы можем даже дать имя.
Пример использования первичного ключа в SQLite. Пример PRIMARY KEY в SQLite
Мы разобрались в теории, что собой представляет первичный ключ в реляционных базах данных, а также выяснили, что для создания первичного ключа необходимо использовать конструкцию PRIMARY KEY, тогда SQLite3 поймет, что данный столбец является первичным ключом, а его значения должны быть уникальными и вечными.
Давайте теперь на практике посмотрим, как первичный ключ может обеспечить целостность данных в базе данных и посмотрим, как в SQLite можно объявить столбец с ограничением PRIMARY KEY.
CREATE TABLE table1 (
a INTEGER PRIMARY KEY,
b TEXT NOT NULL,
Первый столбец мы объявили первичным ключом таблицы table1 при помощи ключевой фразы PRIMARY KEY. Теперь давайте смотреть, как SQLite будет относиться к значениям, которые мы будем добавлять в первый столбец. Чтобы добавить строку в таблицу, нужно воспользоваться командой INSERT.
INSERT INTO table1 (NULL, ‘Hello, World!’, 0.2);
Мы пробуем добавить значение NULL в столбец PRIMARY KEY, и у нас ничего не получается, первичный ключ не может иметь значение NULL, так как значение NULL нельзя ни с чем сравнить однозначно. А ошибку SQLite напишет такую: Error: near «NULL»: syntax error. Давайте теперь попробуем «обмануть» SQLite и добавим две строки:
— Добавляем две строки в таблицу, не указывая значение для столбца PRIMARY KEY
INSERT INTO table1 (b, c) VALUES (‘какой-то текст’, 0.5);
INSERT INTO table1 (b, c) VALUES (‘какой-то текст’, 0.7);
SQLite добавляет эти строки в таблицу, хотя мы не указывали значение для первого столбца, давайте посмотрим на результат, сделав выборку данных из базы данных при помощи команды SELECT:
— Делаем выборку из базы данных и смотрим результат
Как видите, в SQLite3 необязательно указывать ограничение уровня столбца AUTOINCREMENT для столбца с аффинированным типом данных INTEGER, который объявлен, как первичный ключ (PRIMARY KEY). А теперь попробуем добавить строку со значение a = 2 в таблицу table1 (это значение уже есть в таблице и SQLite не должна нам дать возможность добавить такую строку ).
INSERT INTO table1 (2, ‘Hello, World!’, 0.2);
SQLite заблокировала попытку добавления строки со значение a =2, так как правилом первичного ключа является его уникальность. Давайте подсунем SQLite в столбец «a» строку ‘2’. Тип данных будет другим, но, как мы знаем SQLite – это СУБД с динамической типизацией данных:
INSERT INTO table1 (‘2’, ‘Hello, World!’, 0.2);
Да, та же самая ошибка: Error: near «0.2»: syntax error. В данном случае, перед тем, как записать значение в таблицу SQLite его преобразовало в число, так как мы объявили для столбца «а» класс данных INTEGER, а затем сравнила значения из таблицы с тем, что мы хотели добавить, после чего заблокировала операцию добавления данных.
Теперь давайте попробуем изменить значение столбца «а». Для изменения данных в базе данных SQLite есть специальная команда UPDATE, воспользуемся ей:
UPDATE table1 SET a = 2 WHERE c = 0.5;
UPDATE table1 SET a = 3 WHERE c = 0.7;
Когда мы первый раз используем команду UPDATE, SQLite заблокирует нам эту операцию, так как первичный ключ должен быть уникальным, а значение 2 уже есть в столбце «а». Второй UPDATE будет выполнен, так как наша таблица еще не связана с другими таблицами и здесь не произойдет нарушение правила уникальности. Убедимся в этом при помощи команды SELECT:
— Делаем выборку, чтобы убедиться, что второй UPDATE сработал
SELECT * FROM table1;
Если интересно, то при выполнении первого UPDATE, SQLite выдала ошибку: Error: UNIQUE constraint failed: table1.a. Но давайте немного изменим пример, пусть первичный ключ нашей таблицы будет с аффинированным типом данных TEXT. Создадим таблицу table2:
CREATE TABLE table2 (
a TEXT PRIMARY KEY,
b TEXT NOT NULL,
Структура таблицы та же, что и в первом случае, но теперь столбец PRIMARY KEY имеет класс данных TEXT. Выполним первый INSERT из примера с типом данных INTEGER:
INSERT INTO table2 (NULL, ‘Hello, World!’, 0.2);
Ограничение первичного ключа сработало, мы получили ошибку, а это значит, что SQLite не добавила значение NULL в столбец PRIMARY KEY с типом данных TEXT.Выполним второй INSERT, не указав никаких значений для первичного ключа:
INSERT INTO table2 (b, c) VALUES (‘какой-то текст’, 0.5);
SQLite добавила строку в базу данных, несмотря на то, что мы не указывали никаких значений для столбца PRIMARY KEY, давайте сделаем SELECT:
SELECT * FROM table2;
SELECT typeof (a), typeof (b), typeof© FROM table2;
Будьте аккуратны, если вы объявляете первичным ключом столбец с типом данных TEXT при использовании SQLite3, если вы забудете добавить значение, то автоматически добавится значение NULL, в этом случае нужно пользоваться ограничение уровня столбца NOT NULL, чтобы избежать таких ошибок.
А теперь в таблицу table2, попробуем добавить две строки:
INSERT INTO table2 (a,b,c) VALUES (5, ‘Hello, World!’, 0.2);
INSERT INTO table2 VALUES (‘5’, ‘Hello, World!’, 55);
Первая строка будет добавлена, во второй строке мы получим ошибку. Это произошло из-за того, что SQLite перед тем, как добавить первую строку, преобразовал число 5 в строку ‘5’, поэтому на вторую строку было наложено правило уникальности первичного ключа.
А теперь давайте создадим таблицу table3 с первичным ключом PRIMARY KEY, для которого мы не станем указывать тип данных и попробуем повторить последний эксперимент:
— Создаем таблицу с первичным ключом, но без типов данных
CREATE TABLE table3 (
— Добавляем в эту таблицу две строки
INSERT INTO table3 VALUES (‘5’, ‘Hello, World!’, 55);
INSERT INTO table3 (a,b,c) VALUES (5, ‘Hello, World!’, 0.2);
Все команды будут выполнены успешно, так как для столбца, объявленного, как PRIMARY KEY не был указан аффинированный тип данных, а значение 5 и ‘5’ разные значения, так как первое – это число, а второе – строка.
PRIMARY KEY, как индекс в базах данных SQLite3
Мы утверждаем, что первичный ключ – это ограничение уровня таблицы, но столбец PRIMARY KEY мы объявляем, как ограничение уровня столбца, на самом деле, такая форма записи не совсем правильная. Давайте разберемся, почему это так и как нужно делать правильно. Во-первых, стоит сказать, что ограничения уровня таблицы – это такие же объекты баз данных, как и сами таблицы и мы можем им давать имена, которые должны быть уникальны во всей базе данных.
А теперь давайте объявим столбец PRIMARY KEY, как ограничение уровня таблицы:
CREATE TABLE table4 (
b TEXT NOT NULL,
Напишите команду .schema, чтобы увидеть, какой запрос создал данную таблицу. Но, мы утверждали, что первичный ключ – это ни что иное, как индекс таблицы в базе данных SQLite3 и что у столбца PRIMARY KEY может быть имя, давайте этом посмотрим:
CREATE TABLE table5 (
b TEXT NOT NULL,
CONSTRAINT ixpk PRIMARY KEY (a)
Теперь мы создали первичный ключ с именем ixpk, этот первичный ключ является индексом таблицы базы данных table5, в этом легко убедиться, воспользуйтесь командой SELECT, которая покажет все индексы в базе данных SQLite:
SELECT * FROM sqlite_master WHERE type = ‘index’;
Мы создали первичный ключ, объявив столбец «а», как ограничение уровня таблицы при помощи конструкции CONSTRAINT ixpk PRIMARY KEY (a) после объявления всех столбцов, но, в то же время, мы создали индекс с именем ixpk для таблицы table5.
Составной первичный ключ в базах данных SQLite
Бывают такие ситуации, когда в таблице нет столбца, в котором значения были бы уникальные и при этом нежелательно создавать суррогатный ключ, который бы генерировала СУБД. В этом случае используются составные первичные ключи. Создать составной первичный ключ в базе данных SQLite очень просто, давайте посмотрим на пример:
CREATE TABLE table6 (
a INTEGR NOT NULL,
CONSTRAINT new_pk PRIMARY KEY (a, b, c)
Мы помним, что первичный ключ должен обладать двумя параметрами: уникальностью и вечностью. По логике вещей, если мы создаем составной первичный ключ, SQLite должна заботиться об уникальности всех трех значений в сумме, а не по отдельности, давайте это проверим:
INSERT INTO table6 (a,b,c) VALUES (5, ‘Hello, World!’, 0.2);
INSERT INTO table6 (a,b,c) VALUES (5, ‘Hello, World!’, 0.8);
INSERT INTO table6 (a,b,c) VALUES (25, ‘Hello, World!’, 0.2);
INSERT INTO table6 (a,b,c) VALUES (5, ‘Hello, World!’, 0.2);
Все команды INSERT, кроме последней, будут выполнены, последний SQL запрос INSERT выполнен не будет, так как нарушается правило уникальности, мы же создавали составной первичный ключ, поэтому SQLite проверяет на уникальность все три значения и, если все три значения вместе являются не уникальными, то SQLite не дает добавить такую строку в таблицу.
Еще записи о создании сайтов и их продвижении, базах данных, IT-технология и сетевых протоколах
- Часть 11.1: Ограничения уровня столбца в базах данных SQLite
- Часть 11.2: Ограничения уровня таблицы в базах данных SQLite3
- Часть 10.1: Создание таблиц в базах данных SQLite
- Часть 11.4: Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3
- Часть 3.3: Ключи и ключевые атрибуты в базах данных
- Часть 11.7: Индексы в базах данных SQLite. Индексация таблиц в SQLite3. Алгоритм B-дерева в базах данных
- Часть 12.9: Сравнение строк в базах данных: SQL оператор LIKE и SELECT в SQLite. Поиск по шаблону в базах данных при помощи оператора LIKE
- Часть 2.1: SQL комментарии в базах данных SQLite
Возможно, эти записи вам покажутся интересными
Related Posts
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В…
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем рубрику реляционные базы данных и ее раздел библиотека SQLite. Для демонстрации…
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем рубрику реляционные базы данных и начинаем новый раздел библиотека SQLite. Данной…