Asked
12 years, 3 months ago
Viewed
34k times
I get a syntax error near AUTOINCREMENT. What is the cause of this error?
CREATE TABLE person (
id INTEGER NOT NULL AUTOINCREMENT,
name TEXT NOT NULL
);
CREATE TABLE department (
id INTEGER NOT NULL AUTOINCREMENT,
name TEXT NOT NULL,
FOREIGN KEY (leader) REFERENCES person(id)
);
msrd0
7,4909 gold badges45 silver badges79 bronze badges
asked Oct 28, 2010 at 12:22
According to SQLite FAQ you have to declare either a INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT column to achieve that.
answered Oct 28, 2010 at 13:16
sharptoothsharptooth
166k99 gold badges508 silver badges963 bronze badges
0
In SQLite you need not to specify AUTOINCREMENT if you are specifying a column as Primary Key…
answered Apr 9, 2014 at 9:47
Tapan kumarTapan kumar
6,5791 gold badge24 silver badges23 bronze badges
SQLite AUTOINCREMENT : You Should Avoid Using It
Unless you create a table specifying the WITHOUT ROWID option, you get an implicit auto increment column called rowid.
The rowid column store 64-bit signed integer that uniquely identifies a row within the table.
rustyx
78k24 gold badges193 silver badges257 bronze badges
answered Mar 14, 2019 at 17:55
1
It’s an easy solution. Just use AUTOINCREMENT instead of AUTO_INCREMENT
answered May 8, 2020 at 3:28
flashflash
71 bronze badge
1
Asked
12 years, 3 months ago
Viewed
34k times
I get a syntax error near AUTOINCREMENT. What is the cause of this error?
CREATE TABLE person (
id INTEGER NOT NULL AUTOINCREMENT,
name TEXT NOT NULL
);
CREATE TABLE department (
id INTEGER NOT NULL AUTOINCREMENT,
name TEXT NOT NULL,
FOREIGN KEY (leader) REFERENCES person(id)
);
msrd0
7,4909 gold badges45 silver badges79 bronze badges
asked Oct 28, 2010 at 12:22
According to SQLite FAQ you have to declare either a INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT column to achieve that.
answered Oct 28, 2010 at 13:16
sharptoothsharptooth
166k99 gold badges508 silver badges963 bronze badges
0
In SQLite you need not to specify AUTOINCREMENT if you are specifying a column as Primary Key…
answered Apr 9, 2014 at 9:47
Tapan kumarTapan kumar
6,5791 gold badge24 silver badges23 bronze badges
SQLite AUTOINCREMENT : You Should Avoid Using It
Unless you create a table specifying the WITHOUT ROWID option, you get an implicit auto increment column called rowid.
The rowid column store 64-bit signed integer that uniquely identifies a row within the table.
rustyx
78k24 gold badges193 silver badges257 bronze badges
answered Mar 14, 2019 at 17:55
1
It’s an easy solution. Just use AUTOINCREMENT instead of AUTO_INCREMENT
answered May 8, 2020 at 3:28
flashflash
71 bronze badge
1
Содержание
- [SQLite] Не могу создать базу с двумя автоинкрементирущимися полями.
- Re: [SQLite] Не могу создать базу с двумя автоинкрементирущимися полями.
- Re: [SQLite] Не могу создать базу с двумя автоинкрементирущимися полями.
- http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql
- 1. Summary
- 2. Background
- 3. The AUTOINCREMENT Keyword
[SQLite] Не могу создать базу с двумя автоинкрементирущимися полями.
Нужно мне создать в SQLite базу, в которой бы было два автоинкрементирующихся поля. Вот такую:
Проблема в том, что SQLite не может создать базу с такой структурой. Выдается ошибка:
Если в поле order_id убрать ключевое слово AUTOINCREMENT, то база данных создается. Но поле order_id уже не автоинкрементируется при добавлении записи.
И вместо автоматизации процесса присвоения уникального номера, нужно писать в приложении выборку максимального номера по столбцу order_id, инкрементировать его, и записывать полученное число в новую запись. Что неслабо напрягает.
Вопрос. Поддерживает ли SQLite автоинкремент в нескольких столбцах? Или автоинкремент возможен только для столбца PRIMARY KEY, и как следствие — только для одного столбца в таблице?
У тебя таблица с двумя автоинкрементирущимися полями, а не база. Выкини поле order_id (оно итак всегда будет равно id)
У тебя таблица с двумя автоинкрементирущимися полями, а не база.
Выкини поле order_id (оно итак всегда будет равно id)
id — это уникальный идентификатор.
order_id — это последовательность, в которой нужно выстраивать записи.
С чего бы order_id итак всегда будет равно id ? Это совершенно разные вещи.
в том, что в таблице хранится информация, в какой последовательности надо выстраивать записи. Для этого и используется поле order_id.
Re: [SQLite] Не могу создать базу с двумя автоинкрементирущимися полями.
Он тебе намекает, кто нужно проводить нормализацию базы, ибо лишнее поле в таблице — не есть хорошо.
Я правильно понял, что между id и order_id есть зависимость по всей таблице? Если да, то значит order_id не нужно.
> Он тебе намекает, кто нужно проводить нормализацию базы, ибо лишнее поле в таблице — не есть хорошо.
Я правильно понял, что между id и order_id есть зависимость по всей таблице? Если да, то значит order_id не нужно.
Йоба, это чо значит, что указывать последовательность записей в самой таблице нельзя? Обязательно нужно создавать отдельную сортирующую таблицу, в которой будут перечислены пары id order_id, причем столбец order_id должен быть PRIMARY KEY, чтоб автоинкрементирование работало?
И помимо этого надо писать следилку, чтобы в сортирующей таблице удалялась соответствующая запись, если в основной таблице удалена запись? Причем, удаление в сортирущей таблице будет дико тормозить, так как ключ в ней построен по полю order_id а не id?
Они Вам намекают что при вставке новой записи она получает заведомо максимальные значения в оба поля с autoincrement, т.е. по какому сортировать без разницы — одно избыточно.
в том, что в таблице хранится информация, в какой последовательности надо выстраивать записи. Для этого и используется поле order_id.
а для чего тогда нужен id?
Re: [SQLite] Не могу создать базу с двумя автоинкрементирущимися полями.
SQLite поддерживает автоинкремент только в поле, отмеченном INTEGER PRIMARY KEY.
Oracle DBMS не поддерживает инкремент вообще.
В постгресе есть тип serial, который создает автоматом последовательность и триггер before insert, как и оракл. Враппер по сути. И ты сделай также, раз уж надо.
> в том, что в таблице хранится информация, в какой последовательности надо выстраивать записи. Для этого и используется поле order_id.
1) order_id не очень удачное название. У меня ассоциируется с неким номером заказа. лучше переименуйте в sort_order
2)
insert into list
(sort_order, time, name)
values(
case when (select max(sort_order) from test) IS NULL then 0 else (select max(sort_order) from test)+1 end,
‘ ‘,
‘ ‘
);
http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql
AUTO_INCREMENT is possibly the worst way of doing unique ids for tables. It requires cached per-connection-handle last_insert_id() values. And you’re probably already familiar with how much of a hack THAT is.
извините, что влезаю, а где можно посмотреть сценарий атаки через auto_increment? Мне просто для интереса, я не кулхацкер
>С чего бы order_id итак всегда будет равно id ? Это совершенно разные вещи.
C того, что ежели они оба с автоинкрементом, то при каждой вставке оба будут увеличиваться на единицу. Если они равны изначально — будут равны и дальше. Если отличаются — то и дальше будут отличаться на ту же величину.
Если вам нужно переодически менять очередность — то автоинкремент order_id не нужен совершенно. Если не нужно — сортируйте по id, в чем проблема?
в данном случае hack = костыль.
> C того, что ежели они оба с автоинкрементом, то при каждой вставке оба будут увеличиваться на единицу. Если они равны изначально — будут равны и дальше. Если отличаются — то и дальше будут отличаться на ту же величину.
Есть база, например, передовиков производства. Впереди самые лучшие по итогам месяца, позади похуже.
id order_id Name
1 2 Вася
2 1 Петя
5 7 Саша
7 5 Коля
9 9 Витя
То есть, передовики перечисляются:
Если вам нужно переодически менять очередность — то автоинкремент order_id не нужен совершенно. Если не нужно — сортируйте по id, в чем проблема?
Как вы собираетесь, не изменяя уникального идентификатора человека (id), сортировать их список?
> AUTO_INCREMENT is possibly the worst way of doing unique ids for tables. It requires cached per-connection-handle last_insert_id() values. And you’re probably already familiar with how much of a hack THAT is.
У меня плохо с англицким. О чем тут написано?
>> в том, что в таблице хранится информация, в какой последовательности надо выстраивать записи. Для этого и используется поле order_id.
Для чего нужен уникальный идентификатор? Наверняка не для сортировки записей, которая может меняться со временем.
from list наверно?
Кстати, это допустимый SQLite синтаксис?
Наверняка не для сортировки записей, которая может меняться со временем.
если сортировка будет меняться — тебе не нужен autoincrement на order_id. Иначе сортировку можно делать по id и он нужен в том числе и для этих целей, раз уж всё равно собираешься сортировать в порядке добавления. Я тебе пытаюсь наглядно продемонстрировать, что у тебя одно поле лишнее. Посмотри сам. Одно из них не нужно. Если хочешь сортировать не в порядке добавления, да ещё и с возможностью изменения порядка сортировки — тебе не нужен autoincrement.
Впереди самые лучшие по итогам месяца, позади похуже.
это вообще, да. вот что для такого совершенно не подходит — так это поле order_id непосредственно в таблице работников. А если через пару месяцев будет два варинта вортировки — по передовичности труда и по повышенной активности участия в субботниках? Учи нормализацию баз данных, а пока вот тебе очевидный фикс. Работники труда — в одну таблицу. Показатели труда — в другую. Связь внешними ключами. Выбираешь показатели труда за последнюю неделю, джоинишь с работниками — получаешь отсортированный список. И не нужно делать ручной апдейт таблицы каждую неделю.
Я не вижу тут автоинкремента поля order_id. И не нужен он тут.
И как уже сказали выше — такую сортировку лучше в отдельную таблицу.
AUTO_INCREMENT is possibly the worst way of doing unique ids for tables. It requires cached per-connection-handle last_insert_id() values. And you’re probably already familiar with how much of a hack THAT is.
AUTO_INCREMENT скорее всего худший метод получения уникальных id для строк таблиц. Оно требует кешируемых для каждого коннекшна значений last_insert_id(). И вам, наверное, и так известно, КАКОЙ это костыль.
> from list наверно?
тьфу, да. сделал у себя тестилку и не поменял.
Кстати, это допустимый SQLite синтаксис?
Источник
1. Summary
The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.
In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.
On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.
If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
2. Background
In SQLite, table rows normally have a 64-bit signed integer ROWID which is unique among all rows in the same table. (WITHOUT ROWID tables are the exception.)
You can access the ROWID of an SQLite table using one of the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.
If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.
When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine. To specify a ROWID manually, just include it in the list of values to be inserted. For example:
If no ROWID is specified on the insert, or if the specified ROWID has a value of NULL, then an appropriate ROWID is created automatically. The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.
The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might not be in strictly ascending order.
3. The AUTOINCREMENT Keyword
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the largest possible ROWID has previously been inserted, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error. Only ROWID values from previous transactions that were committed are considered. ROWID values that were rolled back are ignored and can be reused.
SQLite keeps track of the largest ROWID using an internal table named «sqlite_sequence». The sqlite_sequence table is created and initialized automatically whenever a normal table that contains an AUTOINCREMENT column is created. The content of the sqlite_sequence table can be modified using ordinary UPDATE, INSERT, and DELETE statements. But making modifications to this table will likely perturb the AUTOINCREMENT key generation algorithm. Make sure you know what you are doing before you undertake such changes. The sqlite_sequence table does not track ROWID changes associated with UPDATE statement, only INSERT statements.
The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. These are important properties in certain applications. But if your application does not need these properties, you should probably stay with the default behavior since the use of AUTOINCREMENT requires additional work to be done as each row is inserted and thus causes INSERTs to run a little slower.
Note that «monotonically increasing» does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.
Because AUTOINCREMENT keyword changes the behavior of the ROWID selection algorithm, AUTOINCREMENT is not allowed on WITHOUT ROWID tables or on any table column other than INTEGER PRIMARY KEY. Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.
Источник