What is the correct syntax to create an integer primary key auto incremental field in PostgreSQL using C++?
I started with
db->ExecuteSQL("CREATE TABLE mytable (\"mytableid\" INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
This compiles but the process crashes and no field is created.
db->ExecuteSQL("CREATE TABLE mytable (\"mytableid\" serial PRIMARY KEY NOT NULL,
This works and does create the field correctly.
Do I need the NOT NULL or is this not necessary with serial?
Is this the best syntax and method in Postgres for primary key field creation?
You do not need the NOT NULL. It is implied when you define the column PRIMARY KEYS. Per documentation:
Technically, a primary key constraint is simply a combination of a
unique constraint and a not-null constraint.
In addition, serial also implies NOT NULL. It's not a data type per se, just a notational convenience for integer NOT NULL with an attached sequence.
So this is perfect syntax:
CREATE TABLE mytable (mytableid serial PRIMARY KEY);
You don't need to double quote the column name as long as you don't want to use mixed case identifiers, reserved words or "illegal" characters. I would advise to use legal, lower case identifiers exclusively to make your code less error-prone (and your life simpler).
