ANSI compliance of constraint names?

Hi again, More newbie stuff - I have some SQL I'm moving from SQL Server 7.0 and Cloudscape 3.0: CREATE TABLE link ( id INT NOT NULL, o1_host VARCHAR(256) NULL, o1_db VARCHAR(64) NULL, o1_id INT NOT NULL, o2_host VARCHAR(256) NULL, o2_db VARCHAR(64) NULL, o2_id INT NOT NULL); ALTER TABLE link ADD CONSTRAINT link_pk PRIMARY KEY (id) The ALTER TABLE statement causes a syntax error. After a bit of playing I realized that Informix (IDS 2000 9.20.UC1) wants the constraint name ('link_pk') in a different place. In fact, the syntax for ALTER TABLE ... ADD CONSTRAINT seems to be non-standard. Examples from Informix's docs: ALTER TABLE customer ADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust; Note that the name is at the *end*, following "CONSTRAINT". Here's the syntax from SQL Server docs: ALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1) From the Cloudscape docs: ALTER TABLE Countries ADD CONSTRAINT new_unique UNIQUE(country) Both SQL Server and Cloudscape agree on the name following "ADD CONSTRAINT", but Informix is different. Crud - Informix is supposed to comply with SQL-92, so I don't understand. I'd appreciate any clarification... matt cornell@cs.umass.edu

"Matthew Cornell" <cornell@cs.umass.edu> wrote in message news:39f790e4$1@oit.umass.edu... > Hi again, > > More newbie stuff - I have some SQL I'm moving from SQL Server 7.0 and > Cloudscape 3.0: > > CREATE TABLE link ( > id INT NOT NULL, > o1_host VARCHAR(256) NULL, > o1_db VARCHAR(64) NULL, > o1_id INT NOT NULL, > o2_host VARCHAR(256) NULL, > o2_db VARCHAR(64) NULL, > o2_id INT NOT NULL);> > ALTER TABLE link > ADD CONSTRAINT link_pk PRIMARY KEY (id)> > The ALTER TABLE statement causes a syntax error. After a bit of playing I > realized that Informix (IDS 2000 9.20.UC1) wants the constraint name > ('link_pk') in a different place. In fact, the syntax for ALTER TABLE ... > ADD CONSTRAINT seems to be non-standard. Examples from Informix's docs: > > ALTER TABLE customer > ADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust;> > > Note that the name is at the *end*, following "CONSTRAINT". > ... > > Both SQL Server and Cloudscape agree on the name following "ADD CONSTRAINT", > but Informix is different. Crud - Informix is supposed to comply with > SQL-92, so I don't understand. I'd appreciate any clarification... ALTER TABLE is an extension to ANSI SQL. So every dbms vendor mightcreate his own stuff. Informix does not need named constraints. The SQL parser will find it easier if the optional parts come after the required parts. HTH -- Christian Knappke The opinions stated above are my own and not necessarily those of my employer.