CODEWITHSUNDEEP
sqlcheck-constraints
oraclejunior1
oraclejunior1

Reputation: 37

Check constraints-1

I am wondering about unclear point to me when I am defining check constraint on the column in a table as following :

Create test 
(
emp_id number(8) ,
salary number(9,2) constraint test_sal_check CHECK(salary>0,emp_id between 0 and 9)
);

In this case its correct to use the emp_id column within the constraint on salary column .

please help me

Upvotes: 1

Views: 279

Answers (1)

Andreas
Andreas

Reputation: 159086

You didn't specify which database you're using, but Microsoft SQL Server says:

A column-level CHECK constraint can reference only the constrained column, and a table-level CHECK constraint can reference only columns in the same table.

PostgreSQL says:

A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns.

Oracle says:

The syntax for inline and out-of-line specification of check constraints is the same. However, inline specification can refer only to the column (or the attributes of the column if it is an object column) currently being defined, whereas out-of-line specification can refer to multiple columns or attributes.

As you can see, they all agree, although PostgreSQL might be more lenient (using "should").

Also, the CHECK(expr) argument is an expression, which means that the , comma is not valid.

You have multiple choices:

CREATE test (
  emp_id NUMBER(8)   CONSTRAINT test_emp_check CHECK(emp_id BETWEEN 0 AND 9),
  salary NUMBER(9,2) CONSTRAINT test_sal_check CHECK(salary > 0)
);

CREATE test (
  emp_id NUMBER(8),
  salary NUMBER(9,2),
  CONSTRAINT test_sal_check CHECK(salary > 0),
  CONSTRAINT test_emp_check CHECK(emp_id BETWEEN 0 AND 9)
);

CREATE test (
  emp_id NUMBER(8),
  salary NUMBER(9,2),
  CONSTRAINT test_check CHECK(salary > 0 AND emp_id BETWEEN 0 AND 9)
);

If you meant the constraint to mean "salary > 0 when emp_id between 0 and 9", then do this:

CREATE test (
  emp_id NUMBER(8),
  salary NUMBER(9,2),
  CONSTRAINT test_sal_check CHECK(salary > 0 OR emp_id NOT BETWEEN 0 AND 9)
);

Upvotes: 1

Related Questions