iXora Custom Software Development Blog

Read | Practice | Advance

What If While Updating Large Data Loaded 'Primary Key Table' That References 'Foreign Key Table'?

Posted by on in Blog
  • Font size: Larger Smaller
  • Hits: 1275
  • 0 Comments
  • Print

An error is encountered when the primary key table is updated but it is referenced by a foreign key table. The error indicates that the update statement conflicted with the foreign Key constraint. So how can we get overcome of this error? The steps are as the following as in SQL statements.

STEP 1:
USE [YourDatabase]
GO

STEP 2:
ALTER TABLE [dbo].[YourForeignKeyTable]  DROP CONSTRAINT [FK_YourForeignKeyTable_YourPrimaryKeyTable]
GO

STEP 3:
DROP TABLE [dbo].[YourPrimaryKeyTable]
GO

STEP 4:
CREATE TABLE [dbo].[YourPrimaryKeyTable](
	[YourPrimaryKeyTableId] [uniqueidentifier] NOT NULL,
	[YourPrimaryKeyTableName] [nvarchar](100) NULL,	
 CONSTRAINT [PK_YourPrimaryKeyTable] PRIMARY KEY CLUSTERED 
(
	[YourPrimaryKeyTableId] ASC
)
) ON [PRIMARY]
GO

STEP 5:
INSERT [dbo].[YourPrimaryKeyTable] ([YourPrimaryKeyTableId], [YourPrimaryKeyTableName]) VALUES (N'c7954f89-b5f0-47b7-962f-0c5b2800cff2', N'Civil')
GO
INSERT [dbo].[YourPrimaryKeyTable] ([YourPrimaryKeyTableId], [YourPrimaryKeyTableName]) VALUES (N'c7954f89-b5f0-47b7-962f-0c5b2800cff3', N'Mechanical')
GO

STEP 6:
ALTER TABLE [dbo].[YourForeignKeyTable]  WITH CHECK ADD  CONSTRAINT [FK_YourForeignKeyTable_YourPrimaryKeyTable]
FOREIGN KEY([YourForeignKeyTableId]) REFERENCES [dbo].[YourPrimaryKeyTable] ([YourPrimaryKeyTableId])
GO

 And now you are done. 

Rate this blog entry:
1

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest
Guest Wednesday, 21 August 2019