composite primary key or composite foreign key pro

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

composite primary key or composite foreign key pro

Author

Topic

maifs
Yak Posting Veteran

57 Posts

Posted - 2010-04-29 : 06:45:24

i made a procedure which retreives all foreign key information against a single primary key table.

but this procedure have a problem, if composite primary or composite foreign key will come into its iteration then it will generate an error and i am altering their foreign key informations by first dropping and then add new foreign key constraints.

i am using sql server 2005.

ALTER PROC [dbo].[sp_sng_fetching_foreign_key_info] @p_table_name varchar(100) , @flag bit
AS

DECLARE @pk_table_nme VARCHAR(150)
DECLARE @fk_table_nme VARCHAR(150)
DECLARE @pk_col_nme VARCHAR(150)
DECLARE @fk_col_nme VARCHAR(150)
DECLARE @constraint_nme VARCHAR(150)
DECLARE @flg bit

BEGIN
set @flg = @flag

DECLARE GETTING_ALL_FOREIGN_KEY_DATA CURSOR FOR
select ISNULL(c.constraint_name,''),

ISNULL(cfk.table_name ,''),

ISNULL(kcu.column_name ,''),

ISNULL(cpk.table_name ,''),

ISNULL(pkt.column_name ,'')

from information_schema.REFERENTIAL_CONSTRAINTS c

INNER JOIN information_schema.TABLE_CONSTRAINTS cpk on c.unique_constraint_name = cpk.constraint_name

INNER JOIN information_schema.TABLE_CONSTRAINTS cfk on c.constraint_name = cfk.constraint_name

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constraint_name = kcu.constraint_name

INNER JOIN
(
SELECT tci.table_name, kcui.column_name FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tci
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcui on tci.constraint_name = kcui.constraint_name
WHERE tci.CONSTRAINT_TYPE = 'PRIMARY KEY'

) PKT ON PKT.TABLE_NAME = CPK.TABLE_NAME
WHERE cpk.table_name = (@p_table_name)
ORDER BY 1,2,3,4

OPEN GETTING_ALL_FOREIGN_KEY_DATA

FETCH NEXT FROM GETTING_ALL_FOREIGN_KEY_DATA INTO @constraint_nme,@fk_table_nme ,@fk_col_nme, @pk_table_nme , @pk_col_nme

WHILE @@FETCH_STATUS = 0

BEGIN

IF @flg = 1
BEGIN
EXEC('ALTER TABLE [dbo].['+ @fk_table_nme +'] DROP CONSTRAINT ['+ @constraint_nme +']');
EXEC('ALTER TABLE [dbo].['+@fk_table_nme+'] WITH NOCHECK ADD CONSTRAINT ['+@constraint_nme+'] FOREIGN KEY(['+@fk_col_nme+'])
REFERENCES [dbo].['+ @pk_table_nme +'] (['+@pk_col_nme+']) ON DELETE CASCADE');
print 'Disabled Foreign key Constraints'
END
ELSE
print @flg

FETCH NEXT FROM GETTING_ALL_FOREIGN_KEY_DATA INTO @constraint_nme,@fk_table_nme ,@fk_col_nme, @pk_table_nme , @pk_col_nme
END

CLOSE GETTING_ALL_FOREIGN_KEY_DATA
DEALLOCATE GETTING_ALL_FOREIGN_KEY_DATA

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources