Добавить автоинкремент в столбец с Primary_key в SQL Server
Есть существующая таблица, где первый столбец [ID] — PK. Как добавить в него автоинкремент? Прочитал общий тезис о том, что таблицу нужно пересобирать (то есть, так просто не «включишь»), но не могу составить скрипт.
Отслеживать
задан 24 окт 2017 в 9:47
Виталий Яндулов Виталий Яндулов
2,474 2 2 золотых знака 16 16 серебряных знаков 43 43 бронзовых знака
CREATE TABLE table_name ( field_name int IDENTITY(1,1) PRIMARY KEY, . )
24 окт 2017 в 9:54
24 окт 2017 в 9:55
с внешними ключами еще разобраться надо, если есть. Вообще в SSMS можно в дизайнере изменить что надо, и нажать «generate scripts».
24 окт 2017 в 10:48
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Если лень пересобирать таблицу, добавьте sequence.
Есть, к примеру, таблица с данными:
CREATE TABLE [Some] (ID int NOT NULL CONSTRAINT PK_Some PRIMARY KEY); INSERT INTO [Some] VALUES (1), (2), (3); добавляем последовательность и назначаем значением по умолчанию на столбец:
BEGIN TRAN DECLARE @nextID int; SELECT @nextID = COALESCE(MAX(ID), 0) + 1 FROM [Some] WITH (TABLOCKX); EXEC('CREATE SEQUENCE Some_ID AS INT START WITH ' + @nextID); ALTER TABLE [Some] ADD CONSTRAINT DF_Some_ID DEFAULT (NEXT VALUE FOR Some_ID) FOR ID; COMMIT Если принципиально IDENTITY (например, планируете пользоваться функциями @@IDENTITY или SCOPE_IDENTITY() ), то придётся повозиться.
Вариант с ALTER TABLE . SWITCH TO будет предпочтительным на большом количестве данных (мне он нравится и на небольшом количестве данных тоже).
Нужно создать таблицу в точности с той же структурой (включая индексы и ограничения), но со столбцом IDENTITY :
CREATE TABLE [Some2] (ID int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Some2 PRIMARY KEY); потом перекинуть данные, удалить старую таблицу, а новую переименовать в старую:
BEGIN TRAN -- здесь нужно удалить FK-ограничения ссылающиеся на таблицу [Some] ALTER TABLE [Some] SWITCH TO [Some2]; DROP TABLE [Some]; EXEC sp_rename 'Some2', 'Some', 'OBJECT'; EXEC sp_rename 'PK_Some2', 'PK_Some', 'OBJECT'; -- здесь нужно восстановить FK-ограничения ссылающиеся на таблицу [Some] DBCC CHECKIDENT('Some'); COMMIT На самом деле SWITCH TO не перемещает данные, а лишь изменяет метаинформацию, переназначая области данных, занятые таблицей, с одной таблицы на другую.
Этим же способом можно проделать и обратную операцию, если IDENTITY , наоборот, нужно убрать со столбца.
Автоинкремент — Основы реляционных баз данных
Мы уже создавали значения первичных ключей самостоятельно. Так можно делать в учебных целях, но в промышленной разработке эту задачу берут на себя СУБД. За это отвечает механизм автогенерации. В этом уроке мы разберем принцип его работы.
Автогенерация первичного ключа
Первичный ключ в базах данных принято заполнять автоматически, используя встроенные в базу данных возможности. Такой подход лучше ручного заполнения по двум причинам. Во-первых, это просто реализовать. Во-вторых, база данных сама следит за уникальностью во время генерации.
Автогенерация работает по следующим принципам:
- Внутри базы создается отдельный счетчик, который привязывается к каждой таблице
- Счетчик увеличивается на единицу при вставке новой строки
- Получившееся значение записывается в поле, которое помечается как автогенерируемое
Автогенерацию первичного ключа часто называют автоинкрементом (autoincrement). Что переводится как автоматическое увеличение и напоминает операцию инкремента из программирования ++.
До определенного момента механизм автоинкремента был реализован по-своему в каждой СУБД разными способами. Это создавало проблемы при переходе от одной СУБД к другой и усложняло реализацию программного слоя доступа к базе данных.
Эта функциональность добавлена в стандарт SQL:2003, то есть очень давно. И только в 2018 году PostgreSQL в версии 10 стал его поддерживать. Такой автоинкремент известен под именем GENERATED AS IDENTITY:
CREATE TABLE colors ( -- Одновременное использование и первичного ключа и автогенерации id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY, name varchar(255) ); INSERT INTO colors (name) VALUES ('Red'), ('Blue'); SELECT * FROM colors; Этот запрос вернет:
| id | name |
|---|---|
| 1 | Red |
| 2 | Blue |
Если удалить запись с id равным двум и вставить еще одну запись, то значением поля id будет 3 . Автогенерация не связана с данными в таблице. Это отдельный счетчик, который всегда увеличивается. Так избегаются вероятные коллизии и ошибки, когда один и тот же идентификатор принадлежит сначала одной записи, а потом другой.
Вот его структура из документации:
AS IDENTITY[ ( sequence_option ) ]
- Тип данных может быть SMALLINT, INT или BIGINT
- GENERATED ALWAYS — не позволит добавлять значение самостоятельно, используя UPDATE или INSERT
- GENERATED BY DEFAULT — в отличие от предыдущего варианта, этот вариант позволяет добавлять значения самостоятельно
PostgreSQL позволяет иметь более одного автогенерируемого поля на таблицу.
Открыть доступ
Курсы программирования для новичков и опытных разработчиков. Начните обучение бесплатно
- 130 курсов, 2000+ часов теории
- 1000 практических заданий в браузере
- 360 000 студентов
Наши выпускники работают в компаниях:
SQL AUTO INCREMENT
AUTO INCREMENT позволяет автоматически генерировать уникальное число при вставке новой записи в таблицу.
Часто поле первичного ключа, которое мы хотели бы создавать автоматически при каждой вставке новой записи.
Синтаксис для MySQL
Следующая инструкция SQL определяет, что столбец «Personid», который должен быть полем первичного ключа с автоматическим приращением в поле первичного ключа в таблице «Persons»:
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
MySQL использует ключевое слово AUTO_INCREMENT для выполнения функции автоматического приращения.
По умолчанию начальное значение для AUTO_INCREMENT равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Чтобы последовательность AUTO_INCREMENT начиналась с другого значения, используйте следующую инструкцию SQL:
ALTER TABLE Persons AUTO_INCREMENT=100;
Чтобы вставить новую запись в таблицу «Persons», нам не нужно будет указывать значение для столбца «Personid» (уникальное значение будет добавлено автоматически):
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);
Приведенная выше инструкция SQL вставит новую запись в таблицу «Persons». Столбцу «Personid» будет присваивается уникальное значение. Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName»- «Monsen».
Синтаксис для SQL Server
Следующая инструкция SQL определяет столбец «Personid» как поле первичного ключа автоинкремента в таблице «Persons»:
CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
MS SQL Server использует ключевое слово IDENTITY для выполнения функции автоматического приращения.
В приведенном выше примере начальное значение идентификатора равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Совет: Чтобы указать, что столбец «Personid» должен начинаться со значения 10 и увеличиваться на 5, измените его на IDENTITY(10,5).
Чтобы вставить новую запись в таблицу «Persons», нам не нужно будет указывать значение для столбца «Personid» (уникальное значение будет добавлено автоматически):
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);
Приведенная выше инструкция SQL вставит новую запись в таблицу» Persons». Столбцу «Personid» будет присвоено уникальное значение. Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName»- «Monsen».
Синтаксис для Access
Следующая инструкция SQL определяет столбец «Personid» как поле первичного ключа автоинкремента в таблице «Persons»:
CREATE TABLE Persons (
Personid AUTOINCREMENT PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
MS Access использует ключевое слово AUTOINCREMENT для выполнения функции автоматического приращения.
По умолчанию начальное значение для AUTOINCREMENT равно 1, и оно будет увеличиваться на 1 для каждой новой записи.
Совет: Чтобы указать, что столбец «Personid» должен начинаться со значения 10 и увеличиваться на 5, измените значение autoincrement на AUTOINCREMENT(10,5).
Чтобы вставить новую запись в таблицу «Persons», нам не нужно будет указывать значение для столбца «Personid» (уникальное значение будет добавлено автоматически):
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);
Приведенная выше инструкция SQL вставит новую запись в таблицу «Persons». Столбцу «Personid» будет присвоено уникальное значение. Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName» — «Monsen».
Синтаксис для Oracle
В Oracle код немного сложнее.
Вам нужно будет создать поле автоинкремента с объектом SEQUENCE (этот объект генерирует числовую последовательность).
Используйте следующий синтаксис CREATE SEQUENCE:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
Приведенный выше код создает объект последовательности под названием «seq_person», который начинается с 1 и будет увеличиваться на 1. Он также будет кэшировать до 10 значений для повышения производительности. Параметр «CACHE» указывает, сколько значений последовательности будет храниться в памяти для более быстрого доступа.
Чтобы вставить новую запись в таблицу «Persons», нам придется использовать функцию «nextval» (эта функция извлекает следующее значение из последовательности «seq_person»):
INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,’Lars’,’Monsen’);
Приведенная выше инструкция SQL вставит новую запись в таблицу «Persons». Столбецу «Personid» будет присвоен следующий номер из последовательности «seq_person». Столбец «FirstName» будет иметь значение «Lars», а столбец «LastName»- «Monsen».
Как установить автоинкремент в поле таблицы SQL в phpMyAdmin?
Нужно, чтобы при регистрации каждому следующему пользователю присваивалось новое значение (по типу id ВКонтакте), но я не вижу ни одной команды в перечне доступных, чтобы это реализовать.
и ещё — оно же.
- Вопрос задан более трёх лет назад
- 12955 просмотров