How to create a check constraint between two columns in SQL?

Question

I am trying to create a Basic pay (BP) table with

CREATE TABLE bp (
   bpid       VARCHAR(5), 
      FOREIGN KEY (bpid) REFERENCES designation(desigid), 
   upperlimit DECIMAL(10,2) NOT NULL, 
   lowerlimit DECIMAL(10,2) NOT NULL, 
   increment  DECIMAL(10,2) NOT NULL 
      CONSTRAINT llvalid CHECK (upperlimit > lowerlimit)
 );

As you can see near the ending, I want to check if upperlimit is greater than lowerlimit, how can I do that?

Basis

Answer 1

It might (probably does) depend on the data base you use.

Comparing to the oracle syntax (e.g. here: http://www.techonthenet.com/oracle/check.php), what you are missing might be a ',' between NULL and CONSTRAINT

Varini

Answer 2

Here's proper the SQL query...

CREATE TABLE bp (bpid VARCHAR(5),
FOREIGN KEY (bpid) REFERENCES designation(desigid), 
upperlimit DECIMAL(10,2) NOT NULL,
lowerlimit DECIMAL(10,2) NOT NULL,
increment DECIMAL(10,2) NOT NULL,
CONSTRAINT llvalid CHECK (upperlimit > lowerlimit));

Note the comma after NOT NULL and CONSTRAINT in the last line.

Basis

Answer 3

The problem is that you have defined it as a column level constraint but it references other columns. You must define a constraint at the table level.

ALTER TABLE bp
    ADD CONSTRAINT CK_limit CHECK ( upperlimit > lowerlimit)

Acarology

Why? What kind of differences would it make? – Basis