Forum Stats

  • 3,759,518 Users
  • 2,251,557 Discussions
  • 7,870,690 Comments

Discussions

ERROR: No Discriminator Column found in Arc

Hello,

I am using sqldeveloper 21.2.1 to design a new database.


I made an exclusive relationship (provided in image) but when I generated the sql script I found this error:

ERROR: No Discriminator Column found in Arc Arc_4 - constraint trigger for Arc cannot be generated

What is a discriminating column? And why should I have it?

Thanks,

Gado

Answers

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,348 Employee
    edited Aug 16, 2021 1:51PM

    Hello,

    exclusive relationships in arc means that person can be one of types (gym_emp, coach, member, ...). Discriminator columns will hold the value that describes the type of record(s). So you need to have a discriminator column and type value for each type. Those can be set at arc or at foreign key:


    Check constraint will be generated for discriminator column and triggers to check that only one record exists in child tables for each id in parent (Person in the example) table

    ALTER TABLE Person

       ADD CONSTRAINT Arc_1_LOV CHECK ( discr

       IN ( 'coach', 'Member' ) )

    ;


    Philip

  • Gado
    Gado Member Posts: 621 Blue Ribbon
    edited Aug 18, 2021 9:58PM

    After generating the ddl script and looking into the triggers created. I can see that the trigger checks if the discriminator field is null or a value other than the value of the child.

    So this means that if I am creating a coach I must set the discr column in the person row to 'coach' before inserting the child coach row?


    Thanks @Philip Stoyanov-Oracle

    Gado

  • AndyH
    AndyH Member Posts: 738 Bronze Trophy

    The discriminator tells you what type of person you have.

    You don't have to create a child record linked to the person. Your person could be a coach, but not be associated with gym.

    The arc design doesn't ensure that a coach record is associated with a person with a discriminator set to 'coach', it's just there to help you identify the correct child table. You could easily set the discriminator to 'member' but populate the coach table!

  • Philip Stoyanov-Oracle
    Philip Stoyanov-Oracle Member Posts: 3,348 Employee
    edited Oct 8, 2021 3:09PM

    @AndyH if you set properly discriminator column and values then DM will generate related constraints - there is an option in DDL preferences about those constraints


    Philip

    AndyH