While SQLite does “allow” this… can anyone think of a valid reason you would ever do this?
create table xyz (key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, .... other fields .... )Since by definition an AutoInc Integer PK can never be null, its “redundant” for one,
plus it defeats (depending on the app) the ability to INSERT a record with a null PK (which is the whole purpose of AutoInc)
Although in practice one would never do it, it may not be redundant… In theory (I have never tried it) one COULD set the key field to NULL AFTER the record is created and NOT NULL would prevent that…
Inserting a record with a null PK is not the same as inserting a record with a missing PK. To get the autoinc value, you would not include the PK column in the Insert statement. If you do include the PK column, then whatever value you supply is used, including null. So, the NOT NULL prevents both this and what Karen stated.
not true
INSERT INTO mytable (mKey,test) values(null,'abc')
INSERT INTO mytable(test) values('abc')produce identical results
INSERT INTO mytable (mKey,test) values(5,'abc')will succeed if 5 does not exist, and fail if it does
update mytable set mkey=null where mkey=1produces an error, as I would hope it would
Isn’t SQLite a special case in that it’s mapping the id to rowid under these conditions? Or do I have that backwards?
If you define your own PK AutoInc Integer field, then yes, Sqlite maps that to the RowID, but also creates a Sequence table (which it does not do if you don’t define a PKI)
If you do an Insert with a missing (or NULL assigned) PKI then SQLite uses the sequnce table to determine the next value and inserts it for you, and updates the Sequence table.
rowid becomes a synonym for your defined key
[quote=328016:@Dave S]While SQLite does “allow” this… can anyone think of a valid reason you would ever do this?
create table xyz (key INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, .... other fields .... )Since by definition an AutoInc Integer PK can never be null, its “redundant” for one,
plus it defeats (depending on the app) the ability to INSERT a record with a null PK (which is the whole purpose of AutoInc)[/quote]
which is kind of the point of the “NOT NULL” constraint and AUTOINCREMENT (plus PK is by definition “NOT NULL”)
This behaves as I’d expect SQLite to
[quote=328032:@Dave S]If you define your own PK AutoInc Integer field, then yes, Sqlite maps that to the RowID, but also creates a Sequence table (which it does not do if you don’t define a PKI)
If you do an Insert with a missing (or NULL assigned) PKI then SQLite uses the sequnce table to determine the next value and inserts it for you, and updates the Sequence table.[/quote]
Not strictly true.
the sqlite_sequence table is only created if you specify AUTOINCREMENT. If you do not use AUTOINCREMENT in your column definition then the sqlite_sequence table is not created, but the auto numbering still occurs!
So:
CREATE TABLE test (
mKey integer NOT NULL PRIMARY KEY AUTOINCREMENT,
name text);
INSERT INTO test (name) VALUES ('Simon');
will produce a table called sqlite_sequence and the data will be inserted into the table.
If you use:
CREATE TABLE test (
mKey integer NOT NULL PRIMARY KEY,
name text);
INSERT INTO test (name) VALUES ('Simon');
will NOT produce a table called sqlite_sequence and the data will be inserted into the table.
If you use:
CREATE TABLE test (
mKey integer PRIMARY KEY,
name text);
INSERT INTO test (name) VALUES ('Simon');
will NOT produce a table called sqlite_sequence and the data will be inserted into the table.
SQLite is interesting!
which is what I said
If you define your own PK AutoInc Integer field,
unless it is PK Autoincrement Integer field, no sequence table is created
[quote=328060:@Dave S]which is what I said
If you define your own PK AutoInc Integer field,
unless it is PK Autoincrement Integer field, no sequence table is created[/quote]
Sorry, Dave.
Re-reading your post I am still missing the point but I accept that was what you meant!
My post was just to point out that SQLite seems pretty forgiving in its implementation. That does not make it easy to exactly understand what you are getting or reading when parsing the sql returned by sqlite_master in the Create table section.
NOT NULL is redundant for an INTEGER PRIMARY KEY,
with or without AUTOINCREMENT.
The syntax allows it since it isn’t an error, but it’s not necessary.
If your table is quite big better not use the statement:
alter table goods add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;
because it makes a copy of all data in a temporary table, alter table and then copies it back. Better do it manually. Rename your table:
rename table goods to goods_old;create new table with primary key and all necessary indexes:
create table goods (
id int(10) unsigned not null AUTO_INCREMENT
... other columns ...
primary key (id)
);
move all data from the old table into new, disabling keys and indexes to speed up copying
Nihar,
did you read here ?
[code]The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:
The column may not have a PRIMARY KEY or UNIQUE constraint.
The column may not have a default value of CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP, or an expression in parentheses.
If a NOT NULL constraint is specified, then the column must have a default value other than NULL.
If foreign key constraints are enabled and a column with a REFERENCES clause is added, the column must have a default value of NULL.[/code]Nihar seems to be promoting that “learn sql” website… which seems to be msSQL oriented…
And he does not seem to realize that SQLite is different in how it does or does not handle certain things
Personally I think the information he has posted so far is a detrement to anyone reading it and assuming it applies to “SQLite”
Sent a note to customer service.