02-16-2018 6:08 PM
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
02-16-2018 6:28 PM
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)