You can’t have MySQL do this for you automatically for InnoDB tables – you would need to use a trigger or procedure, or use another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key. Something like the following should work DELIMITER $$ CREATE TRIGGER xxx BEFORE INSERT ON … Read more
For MySql, Check in the EXTRA column: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘my_table’ AND COLUMN_NAME = ‘my_column’ AND DATA_TYPE = ‘int’ AND COLUMN_DEFAULT IS NULL AND IS_NULLABLE = ‘NO’ AND EXTRA like ‘%auto_increment%’ For Sql Server, use sys.columns and the is_identity column: SELECT is_identity FROM sys.columns WHERE object_id = object_id(‘my_table’) AND name=”my_column”
This may help you: CREATE TABLE “custinf” ( “CUST_ID” INT not null primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), “FNAME” VARCHAR(50), “LNAME” VARCHAR(50), “ADDR” VARCHAR(100), “SUBURB” VARCHAR(20), “PCODE” INTEGER, “PHONE” INTEGER, “MOB” INTEGER, “EMAIL” VARCHAR(100), “COMM” VARCHAR(450) ); That’s how i got mine to work… to ages to get the … Read more
You can achieve this by using DDLEvents. This will allow you to run additional SQL statements just after the CREATE TABLE ran. Look at the examples in the link, but I am guessing your code will look similar to below: from sqlalchemy import event from sqlalchemy import DDL event.listen( Article.__table__, “after_create”, DDL(“ALTER TABLE %(table)s AUTO_INCREMENT … Read more
According to SQLite FAQ you have to declare either a INTEGER PRIMARY KEY or INTEGER PRIMARY KEY AUTOINCREMENT column to achieve that.
You can do select max(id)+1 when you do the insertion. For example: INSERT INTO Log (id, rev_no, description) VALUES ((SELECT MAX(id) + 1 FROM log), ‘rev_Id’, ‘some description’) Note that this will fail on an empty table since there won’t be a record with id is 0 but you can either add a first dummy … Read more
If you use the DBCC CHECKIDENT command: DBCC CHECKIDENT (“YourTableNameHere”, RESEED, 1); But use with CAUTION! – this will just reset the IDENTITY to 1 – so your next inserts will get values 1, then 2, and then 3 –> and you’ll have a clash with your pre-existing value of 3 here! IDENTITY just dishes … Read more
Joel’s answer is correct, it is the upper limit of whatever datatype you use. Here’s an example of two of them: int: 2^31-1 (2,147,483,647) bigint: 2^63-1 (9,223,372,036,854,775,807) I have actually hit the limit at a job I worked at. The actual error is: Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting … Read more
Django now has a BigAutoField built in if you are using Django 1.10: https://docs.djangoproject.com/en/1.10/ref/models/fields/#bigautofield
If you are in Microsoft SQL Server, you can “turn off” the autoIncrementing feature by issuing the statement Set Identity_Insert [TableName] On, as in: Set Identity_Insert [TableName] On — ——————————————– Insert TableName (pkCol, [OtherColumns]) Values(pkValue, [OtherValues]) — —- Don’t forget to turn it back off —— Set Identity_Insert [TableName] Off