Need help writing sql to check for records that invalidate a foreign key

[moorcroft]

Hi I am trying to write some sql to because we have a missing foregin key constraint. The following is what I have been asked to do:

1. COMPULSORY_WORK .reference to LISTED_BUILDING .reference

2. The constraint name should be COMPULSORY_WORK _FK

3. Provide data check SQL for records which will invalidate the foreign key

I have created the constraint with the code:

ALTER TABLE COMPULSORY_WORK ADD (
  CONSTRAINT COMPULSORY_WORK_FK FOREIGN KEY (REFERENCE)
    REFERENCES LISTED_BUILDING (REFERENCE));

but before this I am meant to "Provide data check SQL for records which invalidate the foreign key." This is the part I don't know how to do as I am a beginner at SQL.

Below is an example of a completely different requirement and I am assuming I should be editing the code in the select statement, I have no idea what to type though..

SET SERVEROUT ON
DECLARE
  CURSOR GET_PARAMETER IS
    SELECT UPPER(PARAMETER_NAME) PARAMETER_NAME
    FROM STANDARD_PARAMETER
    GROUP BY UPPER(PARAMETER_NAME) HAVING COUNT(*) > 1; 
 
BEGIN
  FOR REC IN GET_PARAMETER LOOP
    DBMS_OUTPUT.PUT_LINE('UNABLE TO CREATE AN UPPER KEY CONSTRAINT ON THE STANDARD_PARAMETER TABLE BECAUSE THE PARAMETER '||  REC.PARAMETER_NAME||' IS DUPLICATED');
 
  END LOOP;
 
END;

[Pilgrim333]

Hi,

Do you need to write your own procedure to validate before you implement the foreign key? Or is a query that retrieves all the invalid data enough?

Pilgrim.

[Pilgrim333]

If you are needed to check the contraint before, then the code below should be what you need

SET SERVEROUT ON 
DECLARE 
  CURSOR GET_INVALID_DATA IS 
    SELECT DISTINCT A.FK
    FROM   TABLE_WITH_FK A
    WHERE  NOT EXISTS
                 (SELECT 1
                  FROM   TABLE_WITH_FK_AS_ID B
                  WHERE  B.ID = A.FK
                  AND    ROWNUM = 1
                 )
   ;  
 
BEGIN 
  FOR REC IN GET_INVALID_DATA LOOP 
    DBMS_OUTPUT.PUT_LINE('UNABLE TO CREATE A FOREIGN KEY CONSTRAINT ON THE TABLE_WITH_FK TABLE BECAUSE THE FOREIGN KEY '||  REC.FK ||' DOES NOT EXIST IN TABLE_WITH_FK_AS_ID'); 
  END LOOP; 
 
END; 
 

It should be something like this, please try and tell your results.

Pilgrim.

[moorcroft]

Hi thanks, this is exactly what I was after