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

CodePudding user response：

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.

1. similar question answer 1
2. how to add constraint via trigger