CODEWITHSUNDEEP
sqlinformix
Sleepy
Sleepy

Reputation: 210

Informix to SQL attribute constraint

I have a constraint as follows in my script & I learnt that it's Informix:

create table blaBla
{
 var_name_1 not null constraint n255_153,
 var_name_1 not null constraint n655_699,
}

I can't find an equivalent to this in SQL. I tried just typing it the same but it doesn't work. What's the equivalent in other DBMS?

Upvotes: 0

Views: 126

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 754470

You have a number of problems with your SQL, which reads:

create table blaBla
{
 var_name_1 not null constraint n255_153,
 var_name_1 not null constraint n655_699,
}

Informix uses { and } to enclose comments (also -- to end of line, and /* … */). Both standard SQL and Informix require ( and ) around the body of the CREATE TABLE statement.

All SQL DBMS insist on a type in the column definition, and require different columns in the same table to have distinct names. (The constraint names in a table must be unique across all tables in the database, too.) The comma is a separator, not a terminator, so the final comma must be omitted, too.

Informix also insists on the constraint name after the constraint, but the standard and other SQL DBMS put the constraint name before the constraint. Thus, elsewhere, you'd consider using:

CREATE TABLE blaBla
(
    var_name_1 INTEGER  CONSTRAINT n255_153 NOT NULL,
    var_name_2 CHAR(20) CONSTRAINT n655_699 NOT NULL
)

Speaking frankly, though, most people would not name a NOT NULL constraint at all and would simply write:

CREATE TABLE blaBla
(
    var_name_1 INTEGER  NOT NULL,
    var_name_2 CHAR(20) NOT NULL
)

The system then generates an automatic constraint name similar to the ones you showed. The nXXX_YYY notation means that the table ID (tabid number in the systables system catalog table) was XXX when the constraint was created, and for that table, the constraint was number YYY. Either you have composite notation from several tables, or the table was modified (altered) and the new table number was 655 when the old was 255. That is, however, a quirk of the naming scheme used by Informix and totally unlegislated by standard SQL.

Upvotes: 2

Related Questions