Foreign Key Reference
-
Hi All,
I have a Master Table and n Transaction Tables.
Ex. ItemMaster As Master
and TranReceipt, TranTransfer as Transaction Tables.
If Some Item referred in Master has affected in the Transaction Tables,
how to know the item has affected Transaction tables, from the Master Table.
Help Me.
-
not enough information, i think Ashok;
do all the transaction tables have Foreign key references to the master table? Are you talking about the CASCADE option on foreign keys for update or delete?(see Cascading Referential Integrity Constraints in BOL)
you can find out all the FK's by using the built in proc sp_fkeys [tablename]
it will list all tables and columns that refer tot eh table in question.
with that, you could build a query actual data to find which tables refer to a specific column in your table.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
hi,
I need to know, which r the tables affected (contains) a particular item that's referred in the Master table.
i.e., ItemID - 0580 in Master Table.
This ItemID may be reflected in any of the transaction table. how to retrieve what are those tables.
Thx
Help Me
-
Hi Ashok,
If you post an example with your table structure, some sample data and the result you would like to get based on that sample data, you'll get an answer in no time...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
assuming you know the tablename and the PK value you wanted to test for (assuming a single column for the PK)
This also assumes that Foreign keys are created, and not implied references by column name or something.
this will do exactly what you ask:
i.e. exec PR_AFFECTED_ROWS 'GMACT',2
typical results:
AFFECTED_TABLE AFFECTED_COUNT
----------------- --------------
GMACIDIS 6
GMATDET 5
GMATRANS 5
GMAACCP 4
GMPVDET 2
GMSTATUS 1
GMBENEF 1
GMBUDGET 1
GMESG 1
GMACTATTRIBUTES 1
GMACTBUD 1
GMACTTAB 1
GMAMEMO 1
GMASUMM 1
--############################################################################
--Procedure to follow all foreign keys for a given table, and find out how many
--rows in the foreign keyd tables are affected.
--hope this helps someone
--lowell at sotrmrage dot com
--usage: exec PR_AFFECTED_ROWS 'TABLENAME',2
--#############################################################################
CREATE PROCEDURE PR_AFFECTED_ROWS (@PKTABLE VARCHAR(30), @PKVALUE INT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ISQL VARCHAR(2000)
CREATE TABLE #FKS (
PKTABLE_QUALIFIER VARCHAR(50),
PKTABLE_OWNER VARCHAR(50),
PKTABLE_NAME VARCHAR(50),
PKCOLUMN_NAME VARCHAR(50),
FKTABLE_QUALIFIER VARCHAR(50),
FKTABLE_OWNER VARCHAR(50),
FKTABLE_NAME VARCHAR(50),
FKCOLUMN_NAME VARCHAR(50),
KEY_SEQ INT,
UPDATE_RULE INT,
DELETE_RULE INT,
FK_NAME VARCHAR(50),
PK_NAME VARCHAR(50),
DEFERRABILITY INT,
SQLSTMT AS 'SELECT ''' + FKTABLE_NAME + ''' AS AFFECTED_TABLE , COUNT(*) FROM ' + FKTABLE_NAME + ' WHERE ' + FKCOLUMN_NAME + ' = '
)
CREATE TABLE #RESULTS(
AFFECTED_TABLE VARCHAR(50),
AFFECTED_COUNT INT
)
INSERT INTO #FKS ( PKTABLE_QUALIFIER ,
PKTABLE_OWNER ,
PKTABLE_NAME ,
PKCOLUMN_NAME ,
FKTABLE_QUALIFIER ,
FKTABLE_OWNER ,
FKTABLE_NAME ,
FKCOLUMN_NAME ,
KEY_SEQ ,
UPDATE_RULE ,
DELETE_RULE ,
FK_NAME ,
PK_NAME ,
DEFERRABILITY )
EXEC SP_FKEYS @PKTABLE
DECLARE C1 CURSOR FOR SELECT SQLSTMT FROM #FKS
OPEN C1
FETCH NEXT FROM C1 INTO @ISQL
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @ISQL = @ISQL + CONVERT(VARCHAR,@PKVALUE)
PRINT @ISQL
INSERT INTO #RESULTS(AFFECTED_TABLE,AFFECTED_COUNT)
EXEC(@ISQL)
FETCH NEXT FROM C1 INTO @ISQL
END
CLOSE C1
DEALLOCATE C1
SELECT * FROM #RESULTS WHERE AFFECTED_COUNT > 0 ORDER BY AFFECTED_COUNT DESC
END
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply