Foreign key in one table points to the primary key in another
table which enforces there are no orphan records in foreign key table which
references primary key table.
for example let’s say there are two tables F_Table
and P_Table, P_Table_ID is
the primary key in P_Table and foreign key in F_Table, if we need to delete record from P_Table we need to make sure there are no records in F_Table or else the delete statement will fail execution.
If we would like to delete a record from primary key table, we
need to know all the places where the primary key in P_Table
is referenced (Referenced table).
SQL Server
Script:
SELECT
F.NAME
AS FOREIGNKEY,
OBJECT_NAME(F.PARENT_OBJECT_ID) AS TABLENAME,
COL_NAME(FC.PARENT_OBJECT_ID,FC.PARENT_COLUMN_ID) AS COLUMNNAME,
OBJECT_NAME
(F.REFERENCED_OBJECT_ID) AS REFERENCETABLENAME,
COL_NAME(FC.REFERENCED_OBJECT_ID,
FC.REFERENCED_COLUMN_ID)
AS REFERENCECOLUMNNAME
FROM
SYS.FOREIGN_KEYS AS F
INNER
JOIN SYS.FOREIGN_KEY_COLUMNS AS FC
ON
F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
WHERE
OBJECT_NAME (F.REFERENCED_OBJECT_ID) LIKE '%TABLE1%' --change the table name
here
Oracle
Script:
SELECT
UC.R_CONSTRAINT_NAME
AS FOREIGNKEY,
UC.TABLE_NAME,
UCC_S.COLUMN_NAME,
UCC.TABLE_NAME
AS REFERENCETABLENAME,
UCC.COLUMN_NAME AS REFERENCECOLUMNNAME
FROM
USER_CONSTRAINTS UC,
USER_CONS_COLUMNS
UCC,
USER_CONS_COLUMNS
UCC_S
WHERE
UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
AND
UC.CONSTRAINT_NAME = UCC_S.CONSTRAINT_NAME
AND
UC.CONSTRAINT_TYPE = 'R'
AND
UCC.TABLE_NAME LIKE '%TABLE1%' --CHANGE THE TABLE NAME HERE
ORDER
BY UC.TABLE_NAME,
UC.R_CONSTRAINT_NAME,
UCC.TABLE_NAME,
UCC.COLUMN_NAME;
Thank
you for reading this article, check out my other SQL Server vs. Oracle posts.