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

Question

I am trying to create a Basic pay table (BP) 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 next to the ending, I want to check if upperlimit is upperlimit lowerlimit , how can I do this?

Answer 1

It may (possibly depends) on the database used.

Comparison with oracle syntax (for example, here: http://www.techonthenet.com/oracle/check.php ), what you are missing may be ',' between NULL and CONSTRAINT

Answer 2

Here's the correct 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)); 

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

Answer 3

The problem is that you defined it as a column level restriction, but referring to other columns. You must define a table level constraint.

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