1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Conditional FK

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Jun 22, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Is it possible to create a conditional FK, the scenario is, i have a table containing pensioner's data. Pension is of different types i.e Old Age,Invalidity,widow.... i need a FK which activate on old age pension record but remain silent for invalidity pension.

    How i can do this.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    What do you mean by 'activate'? Foreign keys are not conditionally functioning constraints.
     
    Tariq Bashir Malhi likes this.
  3. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260

    No foreign key cannot do that. You will need a BEFORE INSERT trigger on your table.

    http://psoug.org/reference/table_trigger.html

    Regards,
    Dariyoosh
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please provide an example showing how that would work, which will be interesting not knowing what Tariq means by 'activate'.
     
  5. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    There is no such thing "activate" a foreign key. When a foreign key is defined, all values are checked upon any INSERT/UPDATE operation according to the referenced table. That's why I told him, he cannot achieve this purpose by defining a foreigne key. Instead he may define a BEFORE INSERT trigger, in order to make the needed tests according the type of the PERNSION whenever a row is going to be inserted.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    But you still provide no example of how this trigger is intended to function. A foreign key is a referential integrity constraint independent of the transaction; a trigger is a transactional mechanism whicn cannot 'see' beyond it's own transactional scope. Explain, please, how a trigger will replace a referential integrity constraint and do so reliably.
     
  7. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    As he has not given the complete description of his tables, I give my own example:

    Code (SQL):

    CREATE TABLE DEPARTMENT(dep_id VARCHAR2(20));
    ALTER TABLE DEPARTMENT ADD CONSTRAINT PK_DEPARTMENT PRIMARY KEY(dep_id);


    CREATE TABLE TEAM(dep_id VARCHAR2(20), team_id VARCHAR2(20), project_id VARCHAR2(20));
    ALTER TABLE TEAM ADD CONSTRAINT PK_TEAM PRIMARY KEY(team_id, project_id);
    ALTER TABLE TEAM ADD CONSTRAINT FK_TEAM(dep_id) REFERENCES DEPARTMENT(dep_id)
        ON DELETE CASCADE;


    CREATE TABLE AGENT(agent_id VARCHAR2(20), team_id VARCHAR2(20))
    ALTER TABLE AGENT ADD CONSTRAINT PK_AGENT PRIMARY KEY(agent_id);
     
    Here an agent can be a member of a group. However he can also be a person who works alone without
    being member of any groupe.

    - If he/she is member of a group then the "team_id" in any UPDATE or INSERT must be a valid value among
    the "team_id" defined for the table "TEAM".

    - If he/she works alone, then the "team_id" in any UPDATE or INSERT must be NULL.

    So here, the "team_id" is somewhat a foreign key for the table "AGENT" but not always. Besides the
    primary key in the table "TEAM" is (team_id, project_id) So you cannot put an index on "team_id" alone
    (in order to have the possibility of NULL values), otherwise you impose a single project per team.

    A BEFORE INSERT trigger can solve this problem.

    Code (SQL):

    CREATE OR REPLACE TRIGGER AGENT_UPD
    BEFORE INSERT OR UPDATE
    ON AGENT
    FOR EACH ROW
    DECLARE
        CURSOR table_cursor IS
            SELECT * FROM TEAM;
        flag BOOLEAN := FALSE;
    BEGIN
        IF (:NEW.team_id IS NOT NULL) THEN
            FOR I IN table_cursor LOOP
                IF (I.team_id = :NEW.team_id) THEN
                    flag := TRUE;
                END IF;
                EXIT WHEN (flag = TRUE);
            END LOOP;
            IF (flag = FALSE) THEN
                RAISE_APPLICATION_ERROR(-20005, 'Invalid team ID');
            END IF;
        END IF;
    END;
    /
     
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not really as the result set is only valid from the time the query starts because of the read consistency mechanism Oracle employs; any activity in the referenced table AFTER the query begins (inserts/deletes) won't be 'seen' by the trigger and thus may throw a false negative resulting in a valid row not being inserted or a false positive resulting in an orphaned record. This is the problem with using transactional mechanisms to attempt to replicate a non-transactional constraint. Test this all you want, you will find that there are cases which result in false rejections and false validations making this 'option' unsuitable.
     
  9. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    In fact for the DELETE operation there must be another TRIGGER (AFTER DELETE), that removes the row also in another table.

    I agree, and I have seen so far many situations like this that you need to simulate a foreign key with ON DELETE CASCADE, and as it seems to me there is not always a definit and proper solution (except if you know one)
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You miss the point entirely; it's possible to insert and delete records from the REFERENCED table (the one WITHOUT the trigger) making the trigger on the REFERENCING table ineffective by producing wrong results. No AFTER DELETE trigger will fix this; the 'FK' trigger fires, the referenced table is inserted into or deleted from AFTER the trigger query starts thus causing the trigger to miss those records and you now have failed inserts that should not have failed or successful inserts that SHOULD have failed. ON DELETE CASCADE ONLY works when a proper FOREIGN KEY is in force.

    You've suggested a poor 'solution' for Tariq and it appears that you fail to see that fact since you continue to argue in favor of your suggestion.
     
  11. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    I did not claim that my solution was perfect. I just gave an example in order to help. If you have a perfect solution, why don't you just write it down here? it will help both me and Taiq to learn, after all this is what this forum is about, share what you know with others.
     
  12. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    When Tariq decides to share more information on what he considers 'activating' this pseudo-foreign key construct I'll be happy to provide suggestions. Note that at the beginning of this I asked him for more informaton, a request he has yet to fulfill. Your assumptions regarding this question may not be true; I will wait until Tariq posts a clearer description of his problem and the solution he expects to find before I venture any suggestions.
     
  13. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Relax folks... I think it's great that Dariyoosh shared a suggestion. And he is right, this forum is about sharing what we know. Our suggestions may not always be the perfect solution, but i am very happy when members post their suggestions and solutions to others problems. Also zargon is exactly right, the description of the problem given by the original poster is incomplete.