FOREIGN KEY QUERIES

 

--1. CREATE A TABLE WITH A FOREIGN KEY CONSTRAINT

CREATE TABLE DEPARTMENTS (

    DEPARTMENT_ID NUMBER PRIMARY KEY,

    DEPARTMENT_NAME VARCHAR2(100) NOT NULL

);

 

CREATE TABLE EMPLOYEES (

    EMPLOYEE_ID NUMBER PRIMARY KEY,

    FIRST_NAME VARCHAR2(50) NOT NULL,

    LAST_NAME VARCHAR2(50) NOT NULL,

    DEPARTMENT_ID NUMBER,

    FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)

);

 

--2. ADD A FOREIGN KEY CONSTRAINT TO AN EXISTING TABLE

ALTER TABLE EMPLOYEES

ADD CONSTRAINT FK_DEPARTMENT

FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);

 

--3. CREATE A TABLE WITH MULTIPLE FOREIGN KEY CONSTRAINTS

CREATE TABLE ORDERS (

    ORDER_ID NUMBER PRIMARY KEY,

    CUSTOMER_ID NUMBER,

    PRODUCT_ID NUMBER,

    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID),

    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)

);

 

--4. MODIFY AN EXISTING FOREIGN KEY CONSTRAINT

ALTER TABLE EMPLOYEES

DROP CONSTRAINT FK_DEPARTMENT;

 

ALTER TABLE EMPLOYEES

ADD CONSTRAINT FK_DEPARTMENT

FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE CASCADE;

 

--5. DROP A FOREIGN KEY CONSTRAINT

ALTER TABLE EMPLOYEES

DROP CONSTRAINT FK_DEPARTMENT;

 

--6. CREATE A TABLE WITH A FOREIGN KEY AND ON DELETE SET NULL

CREATE TABLE PROJECTS (

    PROJECT_ID NUMBER PRIMARY KEY,

    PROJECT_NAME VARCHAR2(100) NOT NULL,

    DEPARTMENT_ID NUMBER,

    FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID) ON DELETE SET NULL

);

 

--7. ADD A FOREIGN KEY CONSTRAINT WITH A SPECIFIC NAME

ALTER TABLE ORDERS

ADD CONSTRAINT FK_CUSTOMER

FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID);

 

--8. LIST ALL FOREIGN KEY CONSTRAINTS IN THE USERS SCHEMA

SELECT CONSTRAINT_NAME, TABLE_NAME

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R';  -- R INDICATES A FOREIGN KEY CONSTRAINT

 

--9. GET COLUMNS OF A SPECIFIC FOREIGN KEY CONSTRAINT

SELECT COLUMN_NAME

FROM USER_CONS_COLUMNS

WHERE CONSTRAINT_NAME = 'FK_DEPARTMENT';  -- REPLACE WITH YOUR CONSTRAINT NAME

 

--10. QUERY ALL TABLES WITH FOREIGN KEY CONSTRAINTS

SELECT TABLE_NAME, CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R';

 

--11. CHECK IF A COLUMN HAS A FOREIGN KEY CONSTRAINT

SELECT CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMPLOYEES'

AND CONSTRAINT_TYPE = 'R';

 

--12. LIST COLUMNS WITH FOREIGN KEY CONSTRAINTS

SELECT COLUMN_NAME

FROM USER_CONS_COLUMNS

WHERE CONSTRAINT_NAME IN (

    SELECT CONSTRAINT_NAME

    FROM USER_CONSTRAINTS

    WHERE CONSTRAINT_TYPE = 'R'

);

 

--13. ENABLE A FOREIGN KEY CONSTRAINT

ALTER TABLE EMPLOYEES

ENABLE CONSTRAINT FK_DEPARTMENT;

 

--14. DISABLE A FOREIGN KEY CONSTRAINT

ALTER TABLE EMPLOYEES

DISABLE CONSTRAINT FK_DEPARTMENT;

 

--15. VALIDATE A FOREIGN KEY CONSTRAINT

ALTER TABLE EMPLOYEES

ENABLE VALIDATE CONSTRAINT FK_DEPARTMENT;

 

--16. ADD A FOREIGN KEY CONSTRAINT WITH ON DELETE CASCADE

ALTER TABLE ORDER_ITEMS

ADD CONSTRAINT FK_ORDER

FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID) ON DELETE CASCADE;

 

--17. CHECK FOR VIOLATIONS BEFORE ADDING A FOREIGN KEY CONSTRAINT

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS);

 

--18. CREATE A VIEW SHOWING FOREIGN KEY CONSTRAINTS

CREATE VIEW FK_CONSTRAINTS AS

SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R';

 

--19. COUNT THE NUMBER OF FOREIGN KEY CONSTRAINTS IN A TABLE

SELECT COUNT(*)

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMPLOYEES' AND CONSTRAINT_TYPE = 'R';

 

--20. GET DDL FOR FOREIGN KEY CONSTRAINTS

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME)

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R';

 

--21. LIST FOREIGN KEY CONSTRAINTS WITH CREATION DATES

SELECT CONSTRAINT_NAME, TABLE_NAME, CREATED

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R'

ORDER BY CREATED;

 

--22. INSERT DATA THAT VIOLATES A FOREIGN KEY CONSTRAINT

INSERT INTO EMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID)

VALUES (1, 'JOHN', 'DOE', 999);  -- THIS WILL FAIL IF 999 IS NOT A VALID DEPARTMENT_ID

 

--23. UPDATE A COLUMN TO AVOID FOREIGN KEY CONSTRAINT VIOLATION

UPDATE EMPLOYEES

SET DEPARTMENT_ID = 10

WHERE EMPLOYEE_ID = 1;

 

--24. DELETE RECORDS THAT CAUSE FOREIGN KEY CONSTRAINT VIOLATIONS

DELETE FROM EMPLOYEES

WHERE DEPARTMENT_ID = 999;  -- ENSURE THE FOREIGN KEY IS MAINTAINED

 

--25. LIST ALL CONSTRAINTS FOR A TABLE

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMPLOYEES';

 

--26. GET INFORMATION ABOUT FOREIGN KEY CONSTRAINTS

SELECT *

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R';

 

--27. LIST TABLES WITH NO FOREIGN KEY CONSTRAINTS

SELECT TABLE_NAME

FROM USER_TABLES

WHERE TABLE_NAME NOT IN (

    SELECT TABLE_NAME

    FROM USER_CONSTRAINTS

    WHERE CONSTRAINT_TYPE = 'R'

);

 

--28. DROP ALL FOREIGN KEY CONSTRAINTS IN A TABLE

BEGIN

    FOR REC IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES' AND CONSTRAINT_TYPE = 'R') LOOP

        EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES DROP CONSTRAINT ' || REC.CONSTRAINT_NAME;

    END LOOP;

END;

 

--29. ENABLE ALL FOREIGN KEY CONSTRAINTS IN A TABLE

BEGIN

    FOR REC IN (SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES' AND CONSTRAINT_TYPE = 'R') LOOP

        EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEES ENABLE CONSTRAINT ' || REC.CONSTRAINT_NAME;

    END LOOP;

END;

 

--30. CHECK FOREIGN KEY CONSTRAINT VIOLATIONS IN A REPORT FORMAT

SELECT EMPLOYEE_ID, DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS);

 

--31. CREATE A SUMMARY OF TABLES WITH FOREIGN KEY CONSTRAINTS

SELECT TABLE_NAME, COUNT(*) AS FK_COUNT

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R'

GROUP BY TABLE_NAME;

 

--32. CHECK FOR FOREIGN KEY CONSTRAINTS ON SPECIFIC COLUMNS

SELECT CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE TABLE_NAME = 'EMPLOYEES'

AND R_CONSTRAINT_NAME = 'FK_DEPARTMENT';  -- REPLACE WITH YOUR CONSTRAINT NAME

 

--33. ADD A COMMENT TO A FOREIGN KEY CONSTRAINT

COMMENT ON CONSTRAINT FK_DEPARTMENT IS 'FOREIGN KEY TO DEPARTMENTS TABLE';

 

--34. GET FOREIGN KEY CONSTRAINTS WITH SPECIFIC PATTERNS

SELECT CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R' AND CONSTRAINT_NAME LIKE 'FK_%';

 

--35. COUNT FOREIGN KEY CONSTRAINTS BY TABLE

SELECT TABLE_NAME, COUNT(*) AS FK_COUNT

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R'

GROUP BY TABLE_NAME;

 

--36. VIEW FOREIGN KEY CONSTRAINTS WITH RELATED COLUMNS

SELECT A.TABLE_NAME, B.COLUMN_NAME

FROM USER_CONSTRAINTS A

JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

WHERE A.CONSTRAINT_TYPE = 'R';

 

--37. GET UNIQUE VALUES FROM A COLUMN WITH A FOREIGN KEY CONSTRAINT

SELECT DISTINCT DEPARTMENT_ID

FROM EMPLOYEES

WHERE DEPARTMENT_ID IS NOT NULL;

 

--38. CREATE A TABLE WITH FOREIGN KEY CONSTRAINTS AND COMPOSITE KEYS

CREATE TABLE ORDER_ITEMS (

    ORDER_ITEM_ID NUMBER PRIMARY KEY,

    ORDER_ID NUMBER,

    PRODUCT_ID NUMBER,

    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID),

    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)

);

 

--39. CHECK FOR CONSTRAINT VIOLATIONS ACROSS ALL FOREIGN KEY CONSTRAINTS

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS);

 

--40. UPDATE TABLE TO ADD A FOREIGN KEY CONSTRAINT AFTER DATA CLEANUP

DELETE FROM EMPLOYEES WHERE DEPARTMENT_ID NOT IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS);

 

ALTER TABLE EMPLOYEES

ADD CONSTRAINT FK_DEPARTMENT FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID);

 

--41. CREATE A TRIGGER TO ENFORCE BUSINESS RULES WITH FOREIGN KEY LOGIC

CREATE OR REPLACE TRIGGER TRG_CHECK_FK_DEPARTMENT

BEFORE INSERT OR UPDATE ON EMPLOYEES

FOR EACH ROW

BEGIN

    IF :NEW.DEPARTMENT_ID IS NOT NULL AND NOT EXISTS (SELECT 1 FROM DEPARTMENTS WHERE DEPARTMENT_ID = :NEW.DEPARTMENT_ID) THEN

        RAISE_APPLICATION_ERROR(-20001, 'DEPARTMENT DOES NOT EXIST.');

    END IF;

END;

 

--42. GENERATE DDL FOR FOREIGN KEY CONSTRAINTS

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', CONSTRAINT_NAME)

FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE = 'R';

 

--43. LIST FOREIGN KEY CONSTRAINTS WITH A SPECIFIC COLUMN NAME

SELECT CONSTRAINT_NAME

FROM USER_CONSTRAINTS

WHERE R_CONSTRAINT_NAME IN (

    SELECT CONSTRAINT_NAME

    FROM USER_CONSTRAINTS

    WHERE SEARCH_CONDITION LIKE '%DEPARTMENT_ID%'

);

 

--44. CHECK FOREIGN KEY CONSTRAINT VIOLATIONS BEFORE UPDATE

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID = 1 AND DEPARTMENT_ID NOT IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS);  -- UPDATE CHECK

 

--45. IDENTIFY FOREIGN KEY CONSTRAINTS AND THEIR RELATED COLUMNS

SELECT A.TABLE_NAME, B.COLUMN_NAME

FROM USER_CONSTRAINTS A

JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

WHERE A.CONSTRAINT_TYPE = 'R';

 

--46. GET COLUMN METADATA FOR COLUMNS WITH FOREIGN KEY CONSTRAINTS

SELECT *

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME IN (

    SELECT COLUMN_NAME

    FROM USER_CONS_COLUMNS

    WHERE CONSTRAINT_NAME IN (

        SELECT CONSTRAINT_NAME

        FROM USER_CONSTRAINTS

        WHERE CONSTRAINT_TYPE = 'R'

    )

);

 

--47. CREATE A TEMPORARY TABLE WITH FOREIGN KEY CONSTRAINTS

CREATE GLOBAL TEMPORARY TABLE TEMP_ORDERS (

    ORDER_ID NUMBER NOT NULL,

    CUSTOMER_ID NUMBER,

    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)

) ON COMMIT PRESERVE ROWS;

 

--48. GET INFORMATION ABOUT FOREIGN KEY CONSTRAINTS WITH SPECIFIC CONDITIONS

SELECT A.CONSTRAINT_NAME, A.TABLE_NAME, A.R_CONSTRAINT_NAME

FROM USER_CONSTRAINTS A

WHERE A.CONSTRAINT_TYPE = 'R' AND A.R_CONSTRAINT_NAME = 'FK_CUSTOMER';  -- REPLACE WITH YOUR CONSTRAINT NAME

 

--49. CREATE A PROCEDURE TO ADD FOREIGN KEY CONSTRAINTS DYNAMICALLY

CREATE OR REPLACE PROCEDURE ADD_FOREIGN_KEY_CONSTRAINT(P_TABLE_NAME IN VARCHAR2, P_COLUMN_NAME IN VARCHAR2, P_REF_TABLE IN VARCHAR2, P_REF_COLUMN IN VARCHAR2) AS

BEGIN

    EXECUTE IMMEDIATE 'ALTER TABLE ' || P_TABLE_NAME || ' ADD CONSTRAINT FK_' || P_COLUMN_NAME || ' FOREIGN KEY (' || P_COLUMN_NAME || ') REFERENCES ' || P_REF_TABLE || '(' || P_REF_COLUMN || ')';

END;

 

--50. IDENTIFY TABLES WITH NO FOREIGN KEY CONSTRAINTS

SELECT TABLE_NAME

FROM USER_TABLES

WHERE TABLE_NAME NOT IN (

    SELECT TABLE_NAME

    FROM USER_CONSTRAINTS

    WHERE CONSTRAINT_TYPE = 'R'

);