Oracle – constraint to check a value exists in another column

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

>Solution :

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