I need to check if the value of ‘montant’ exists in the column ‘prix’ of the table ‘service’ in order to add this value in the table.
This is what I tried so far :
ALTER TABLE abonnement ADD CONSTRAINT montant_inclus CHECK(montant in (select prix from service));
Here is the error I got :
ORA-02251: subquery not allowed here
As per error it’s clear Oracle is not allowing to do
Subqueries in CHECK constraints.
This is doable by JOIN.
ALTER TABLE abonnement ADD CONSTRAINT montant_inclus CHECK(EXISTS (SELECT 1 FROM service WHERE service.prix = abonnement.montant));
It’s also possible using Trigger and other ways.