A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
A snippet from http://en.wikipedia.org/wiki/SQL:2003 states that
"Introduced XML-related features, window functions, standardized sequences and columns with auto-generated values (including identity-columns)."
Does anyone know what the syntax for standardized sequences and columns with auto-generated values are? And also whether that syntax is supported in MS SQL, MySQL and Postgres?
I'm trying to have a single CREATE TABLE script (with an auto-increment identity column) that will run on all 3 databases.
I do know that MSSQL doesn't support the IDENTITY syntax from ANSI SQL 2003. I would also doubt that the others would ever consider supporting Microsoft's proprietary format. I think that means that there will most likely never be one common syntax to support autonumbers on those three databases. You can cut Microsoft a break though, because their method existed 15 years before it was in the standard. It wouldn't benefit Microsoft much to support it now.
Why is it such a big deal to have exactly the same syntax on all three platforms? It is far easier to deal with the subtle differences than to wait for the vendors to agree on something. Also, the best features of most database systems are those features that are non-standard. Using only the standards compliant features of any database is wasting a lot of potential.
Well, you didn't ask for DB2, but here's the syntax:
CREATE TABLE fooSchema.fooTable (
myColumn1 INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1
NO MINVALUE NO MAXVALUE
NO CYCLE NO ORDER
CACHE 20 ),
myColumn2 INTEGER NOT NULL DEFAULT 0 ,
myColumn3 DATE NOT NULL DEFAULT CURRENT_DATE ,
CONSTRAINT fooSchema.myContraint PRIMARY KEY( myColumn1 ) );
There are lots of options as you can probably tell. The way this is set up as illustrated, the myColumn1 column must be missing from the INSERT statement, or, if myColumn1 is in the field list of the INSERT statement, the value must be null.
I use Identity columns all the time. I love 'em.
Monday, April 30, 2007
... by the way, myColumn1 and myColumn2 have nothing to do with identity columns. That was just for illustration.
Monday, April 30, 2007
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz