CHECK constraint problem - SQL Anywhere

[indigodream]

Hello All,

I could do with a nudge in the right direction with a problem I have. I say nudge as the problem is connected to an assignment and I would like to answer it on my own.

There is a table which holds details about the different tariffs countries' impose on imports. It has three columns - country, classification (Chemicals, Cereals or Agricultural) and tariff. The check constraint I have to add to the table is to ensure that no country's tariff for a particular classification is more than 15% higher than the average tariff for that classification.

So far I have this:
ALTER TABLE import_tariff
ADD CHECK
(tariff <=
(SELECT AVG(tariff) * 1.15
FROM import_tariff a
WHERE a.classification = classification));

This works fine when there is only one type of classification in the table. EG. if there are two countries in the table who are importing Chemicals and their tariffs are 23.00 and 24.00, then no country who imports Chemicals can be added to the table whose tariff is more than 27.02.

The problem with the code is it will not allow a country to be added to the table with a different classification and a tariff over 27.02 - in other words the CHECK is not distinguising between classifications. I think the problem is the condition in the WHERE clause is not taking the value for classification from the row being inputted and I'm not sure how to fix this. It needs to filter out rows in the table with a different classification than the row being inputted.

Any hints will be greatly received.

Thanks.

[skhanal]

Check contraints checks the values before new row is entered. So better alternative is to write a trigger.

[indigodream]

Check contraints checks the values before new row is entered. So better alternative is to write a trigger.

Thanks for that Skhanal, but the question definitely asks for a CHECK constraint. We haven't covered triggers in the course yet.

[skhanal]

Have you tried this.

ALTER TABLE import_tariff b
ADD CHECK
(b.tariff <=
(SELECT AVG(tariff) * 1.15
FROM import_tariff a
WHERE a.classification = b.classification));

Does SQL Anywhere support sub query in check constraint?

[indigodream]

Have you tried this.

ALTER TABLE import_tariff b
ADD CHECK
(b.tariff <=
(SELECT AVG(tariff) * 1.15
FROM import_tariff a
WHERE a.classification = b.classification));

Does SQL Anywhere support sub query in check constraint?

Thanks for trying, but it fails at the alias b on the first line.

Sub queries are supported.