Skip to Content

SQL CHECK constraint in column table

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

SQL
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 16 at 06: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)
    
    Add comment
    10|10000 characters needed characters exceeded