My app needs some databases set up. because I'm a good boy the SQL
that's needed for the schemas is included with the app and there's even
a command to run it for you and get everything set up.
Wünderbar.
Except, can you spot what's wrong with this SQL statement?
CREATE TABLE IF NOT EXISTS foo (
id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
);
Well, the answer is nothing. If you're using MySQL. If you're using
SQLite then it's a syntax error because what you need is AUTOINCREMENT.
For no good reason that I can find.
Oh, and in Postgres the syntax is
CREATE SEQUENCE id_sequence;
CREATE TABLE IF NOT EXISTS foo (
id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('id_sequence')
);
or
CREATE TABLE IF NOT EXISTS foo (
id SERIAL
);
Which fills me with frabulous joy.
Oh, and in Oracle, I believe it's
CREATE TABLE foo (
id NUMBER
);
CREATE SEQUENCE id_sequence;
CREATE TRIGGER id_trigger BEFORE INSERT ON foo
for each row
when (new.id is null)
begin
SELECT id_sequence.nextval INTO :new.id FROM dual
end;
*sob*
