2

This is my code:

CREATE TABLE table(
        id int(1) UNIQUE,
        name varchar(128) NOT NULL)

 ALTER TABLE table ADD UNIQUE KEY id (id);

I'm not sure if I need the second statement or not, but either way it doesn't work when inserting INSERT INTO table( name ) VALUES( "test" ). It says table.id may not be NULL. What do I need to do to make it work with NULL(and Auto Increment?) Replacing the first column with id int(1) NOT NULL AUTO_INCREMENT, makes it say syntax error.

Rick James
  • 135,179
  • 13
  • 127
  • 222
g19992
  • 75
  • 4
  • Please tag your question with the database you are using. Some databases do allow NULLs in unique keys, some do not. Apparently, the standard is vague on this point. And, you don't need the second statement. The `unique` keyword and the `alter table` statement do pretty much the same thing. – Gordon Linoff Apr 11 '15 at 22:36
  • Ok, I guess I'll remove the alter table part. And I'm using SQLLite. – g19992 Apr 11 '15 at 22:38
  • `id INT PRIMARY KEY` – Colonel Thirty Two Apr 11 '15 at 22:40
  • @karthikmanchala Uhm .. UNIQUE columns certainly *can* contain NULL values. The answer in that post relates to the *difference* in handling between, say SQLite (which *will* allow multiple NULLs in a unique column) and SQL Server (which will not). – user2864740 Apr 11 '15 at 22:52
  • @karthikmanchala The statement "You cant have UNIQUE with NULL values in sqlite" is incorrect. The linked question and FAQ clearly state the opposite - such *is* possible - and then define the rules for determining if NULL is considered a unique value in a unique constraint. (SQL Server will only allow *one* NULL per nullable unique constraint; SQLite will allow an *unlimited* number of NULLs per nullable unique constraint.) – user2864740 Apr 11 '15 at 23:00
  • @karthikmanchala "A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns." - this basically means that NULL values are *excluded* from the "same values" condition, which is why SQLite *allows* duplicate NULL values in a nullable UX. This does *not* say that NULL values cannot be included, but rather only explains the behavior of determining duplicates. – user2864740 Apr 11 '15 at 23:06
  • `The SQL standard requires that a UNIQUE constraint be enforced even if one or more of the columns in the constraint are NULL, but sqlite doesnot do this.` Doesn't it mean that creating UNIQUE constraint on columns with one or more values is not possible? – karthik manchala Apr 11 '15 at 23:07
  • @karthikmanchala My suggestion at this point is to just try it in SQLite. Then try it in SQL Server (eg. with [sqlfiddle.com](http://sqlfiddle.com/)) and compare the differences. The behavior is already explained in the links. – user2864740 Apr 11 '15 at 23:08
  • @user2864740 sure.. and thank you for taking the pain of explaining.. – karthik manchala Apr 11 '15 at 23:10

1 Answers1

1

The column must be an INTEGER PRIMARY KEY for autoincrement behavior.

  1. 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.

  2. 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

  3. 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 ..

  4. 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.

Unique is insufficient to trigger this behavior; and using AUTOINCREMENT is required to ensure a monotonic increment scheme.

Thus, to expose the surrogate ROWID without a guarantee that the ID will never be reused, use:

id INTEGER PRIMARY KEY

To ensure the ID is never reused over the table/database lifetime (which is the MySQL autoincrement and SQL Server identity column behavior), use:

id INTEGER PRIMARY KEY AUTOINCREMENT

No other types or can be used for this behavior - this includes INT, even though it ends up with the same affinity.

Community
  • 1
  • 1
user2864740
  • 60,010
  • 15
  • 145
  • 220
  • You also need the `autoincrement` don't you? –  Apr 11 '15 at 22:42
  • Sorry, does this mean I should type id int(1) NOT NULL INTEGER PRIMARY KEY or just NOT NULL PRIMARY KEY? – g19992 Apr 11 '15 at 22:44
  • @a_horse_with_no_name I've updated the answer with additional details as to how that affects the behavior. Thanks for pointing out the difference. – user2864740 Apr 11 '15 at 22:46