CODEWITHSUNDEEP
sqloracle-database
Tomáš Tranus
Tomáš Tranus

Reputation: 1

Add check constraint for a column in child table

I have 2 tables

Account:

ID (NUMBER), type_of_acc (VARCHAR), (only values ADMIN or USER);

BAN:

ID_A(NUMBER), ID_U(NUMBER);

both ID_A and ID_U are foreign keys referencing ID from table Account.

I need to add a constraint that checks ID_A has only values ID

WHERE type_of_acc = 'ADMIN'

Upvotes: 0

Views: 162

Answers (1)

jccampanero
jccampanero

Reputation: 53421

As you can see in the Oracle documentation, you cannot do that which CHECKs constraints for several reasons, in your use case, specially because:

The condition of a check constraint can refer to any column in the table, but it cannot refer to columns of other tables.

And:

Conditions of check constraints cannot contain the following constructs:

  • Subqueries and scalar subquery expressions
  • Calls to user-defined functions

In order to achieve that functionally, probably the way to go will be to create a trigger for your BAN table. Consider for instance:

CREATE OR REPLACE TRIGGER tg_ban_boiu
BEFORE INSERT OR UPDATE
ON BAN  
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  v_aux NUMBER;
BEGIN
  IF :NEW.id_a IS NOT NULL THEN
    SELECT COUNT(*) INTO v_aux
      FROM account
     WHERE id = :NEW.id_a
       AND type_of_acc = 'ADMIN';

    IF v_aux = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Invalid value for ID_A');
    END IF;
  END IF;
END;

Upvotes: 1

Related Questions