The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

ISO/ANSI SQL:2003 and the IDENTITY column

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.
Fung Send private email
Friday, April 27, 2007
 
 
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.
JSmith Send private email
Friday, April 27, 2007
 
 
Oracle doesn't come close to supporting and identity column.
XYZZY
Saturday, April 28, 2007
 
 
Yeah, but Oracle has sequences instead, so the need for identity columns is reduced.
Entries of Confusion Send private email
Monday, April 30, 2007
 
 
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.
OneMist8k
Monday, April 30, 2007
 
 
... by the way, myColumn1 and myColumn2 have nothing to do with identity columns.  That was just for illustration.
OneMist8k
Monday, April 30, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz