Как добавить auto increment поле в существующую таблицу mysql

ALTER TABLE 'tTable' ADD COLUMN 'a' INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;

Перед моим вышеприведенным запросом.

Гораздо интереснее переменные auto_increment_increment и auto_increment_offset.
Первая управляет шагом инкремента, а вторая начальным смещением.
Это очень полезно при настройке репликации и масштабирования баз.

Как добавить auto increment поле в существующую таблицу mysql

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

PRIMARY KEY

Атрибут PRIMARY KEY задает первичный ключ таблицы.

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

Установка первичного ключа на уровне таблицы:

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

Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.

AUTO_INCREMENT

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

В данном случае значение столбца Id каждой новой добавленной строки будет увеличиваться на единицу.

UNIQUE

Атрибут UNIQUE указывает, что столбец может хранить только уникальные значения.

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

Также мы можем определить этот атрибут на уровне таблицы:

NULL и NOT NULL

Чтобы указать, может ли столбец принимать значение NULL , при определении столбца ему можно задать атрибут NULL или NOT NULL . Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа — в этом случае по умолчанию столбец имеет значение NOT NULL.

В данном случае столбец Age по умолчанию будет иметь атрибут NULL.

DEFAULT

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

Здесь столбец Age в качестве значения по умолчанию имеет число 18.

CHECK

Атрибут CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:

Кроме проверки возраста здесь также проверяется, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).

Для соединения условий используется ключевое слово AND . Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).

Также CHECK можно использовать на уровне таблицы:

Оператор CONSTRAINT. Установка имени ограничений

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

В данном случае ограничение для PRIMARY KEY называется customers_pk, для UNIQUE — customer_phone_uq, а для CHECK — customer_age_chk. Смысл установки имен ограничений заключается в том, что впоследствии через эти имена мы сможем управлять ограничениями — удалять или изменять их.

Установить имя можно для ограничений PRIMARY KEY, CHECK, UNIQUE, а также FOREIGN KEY, который рассматриватся далее.

How to use MySQL auto-increment

Here, field1 is defined as auto-increment field and the datatype of this field can be any numeric datatype like INT or BIGINT. It is not mandatory to define the auto-increment field as the PRIMARY KEY. But It can be used as a PRIMARY KEY to create a relationship between two tables.

Prerequisite:

Run the following SQL commands to create a database named ‘newdb’ and select the database for creating tables with auto-increment attribute.

Create a table with auto-increment:

Run the following CREATE statement to create a table named students where id field will be created with auto-increment attribute and set as a primary key. Next, two types of INSERT statements will be executed. In the first INSERT statement, no field name is mentioned in the insert query and you have to provide all field values of the table for this type of insertion. Here, the NULL value is used for id field. In the second INSERT statement, all fields except the auto-increment field are mentioned in the insert query because it will be generated automatically. Next, the SELECT statement is executed to display the content of students table.

INSERT INTO students ( name , batch , semester ) VALUES
( ‘Sakib’ , 43 , 7 ) ;

You can set the value of the auto-increment field manually but you have to maintain the sequential order. You can’t set any value lower than the last inserted value or equal to any existing value. The following first INSERT statement will work properly because the last inserted value was 2. The second INSERT statement will generate an error because the value 2 already exists in the table.

Create a table with auto-increment and UNSIGNED ZEROFILL:

It mentioned earlier that, the auto-increment field starts from 1 by default. But if you use UNSIGNED ZEROFILL attribute with auto-increment field and set the length of the number then the number will be generated with leading zero based on the length. The following CREATE statement will create a table named teachers where auto-increment and UNSIGNED ZEROFILL attributes are set for tch_id field and the length of the field is set to 4. Next, some data will be inserted into the table by INSERT statement and the SELECT statement will display all content of the table.

Here, It is shown that 0001, 0002 and 0003 are generated as tch_id values.

Now, if you delete the last record and insert a new record then a new number more the deleted tch_id value will be generated as new tch_id.

Resetting auto-increment field:

If all records are deleted from the teachers table that contains the auto-increment field then the new value of tch_id will be generated after the last inserted value. After running the following SQL statements, it will be shown that the newly generated tch_id is 0005 because the last inserted value was 0004.

If you want to reset the table and start the value from 1 again then you have to execute TRUNCATE statement instead of the DELETE statement. This is shown in the following three statements.

You will get the following output after running the statements.

If you want to change the default value of the auto-increment fields then you have to run the ALTER statement with starting auto-increment value. Next, insert a record and check the value of the auto-increment field. Here, the starting value will be set to 15.

The following output will appear after running the above SQL statements.

Conclusion:

The purposes of the auto-increment attribute are explained properly by using sample tables in this article to help the MySQL user to understand the uses of this attribute.

About the author

Fahmida Yesmin

I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.