SQL CHECK constraint in column table

rhightower13 — Fri, 16 Feb 2018 18:08:43 GMT

I'm trying to create a table with an identity column and a check constraint. This SQL creates the table successfully:

CREATE COLUMN TABLE TEST (
    ID		INT 	PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
    GENDER VARCHAR(1),
    CHECK (GENDER IN ('F','M'))
);

However when I try to insert a record using:

INSERT INTO TEST VALUES ('F');

I get the following error:

12:02:48 PM (SQL Editor) Could not execute 'INSERT INTO TEST VALUES ('F')'Error: (dberror) 7 - feature not supported: [new expression] Only comparison condition for DML update is supported

The CHECK constraint works if I don't make it a column oriented table however if I try to create an identity column without the COLUMN directive, I get this error:

12:06:00 PM (SQL Editor) Could not execute 'CREATE TABLE TEST ( ID	INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, GENDER VARCHAR(1), ...'Error: (dberror) 7 - feature not supported: cannot create row table having identity column: ID: line 3 col 4 (at pos 25)

Is it possible to have an identity column and a check constraint?

Thanks,

Ross

Re: SQL CHECK constraint in column table

rhightower13 — Fri, 16 Feb 2018 18:28:06 GMT

I tried changing the check to a comparison like this:

CREATE COLUMN TABLE TEST (
    testID		INT 	PRIMARY KEY GENERATED ALWAYS AS IDENTITY, 
    GENDER VARCHAR(1),
    CHECK (GENDER = 'F' OR GENDER = 'M')
);

But that produces this error:

12:25:50 PM (SQL Editor) Could not execute 'DROP TABLE TEST'Error: (dberror) 259 - invalid table name: TEST: line 1 col 11 (at pos 11)
12:25:50 PM (SQL Editor) Could not execute 'CREATE COLUMN TABLE TEST ( testID	INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, GENDER ...'Error: (dberror) 288 - cannot use duplicate table name: TEST: line 2 col 20 (at pos 20)

topic SQL CHECK constraint in column table in Application Development and Automation Discussions

SQL CHECK constraint in column table

Re: SQL CHECK constraint in column table