CODEWITHSUNDEEP
sqlpostgresqlcreate-table
Jimmy
Jimmy

Reputation: 847

Check Constraint Returning Error Upon Creation of Tables

Really simple error when I'm creating some tables and it's probably just me being stupid but I'd rather post it here and have it sorted.

I'm using Postgresql.

(This table is just one of many but it's the one that's giving me a problem)

CREATE TABLE Ticket
(
    ID      INTEGER NOT NULL PRIMARY KEY,
    Problem VARCHAR(1000),
    Status  VARCHAR(20) NOT NULL DEFAULT 'open', 
    Priority    INTEGER NOT NULL,
    LoggedTime  TIMESTAMP NOT NULL,
    CustomerID  INTEGER NOT NULL,
    ProductID   INTEGER NOT NULL,
    FOREIGN KEY (ProductID) REFERENCES Product(ID),
    FOREIGN KEY (CustomerID) REFERENCES Customer(ID),
    CHECK (status='open' OR 'closed' AND priority =  1 OR 2 OR 3)
);

The error:

ERROR: invalid input syntax for type boolean: "closed"

Also if anybody can give me a better method of doing the CHECK:

CHECK (status='open' OR 'closed' AND priority =  1 OR 2 OR 3)

Then that would be really appreciated!

Upvotes: 1

Views: 147

Answers (2)

onedaywhen
onedaywhen

Reputation: 57033

I think two constraints would be better: increased granularity would mean you could give more meaningful feedback to the user i.e. do they need amend the Status value or is it the Priority value that is the problem? Giving each constraint a meaningful name would assist the 'front end' coder in this e.g. 

...
CONSTRAINT ticket_status__invalid_value
    CHECK ( Status IN ( 'open' , 'closed' ) ), 
CONSTRAINT ticket_priority__invalid_value 
    CHECK ( Priority IN ( 1 , 2 , 3 ) ), 
...

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453453

Use

CHECK (Status IN ('open','closed') AND Priority IN (1,2,3))

You could also use

(Status ='open' OR  Status ='closed') AND
      (Priority =  1 OR Priority =  2 OR Priority =  3)

Upvotes: 3

Related Questions