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?
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
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)
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.
