SQL

How to delete and regenerate a foreign key object

Last a college asked if it was possible to delete a foreign key object and regenerate it from the original database.

His database objects where dossiers and clients. Client are managed in another application. Therefore it was needed that he could throw his clients table away and regenerate it with the latest version of the data.

But he showed me an error that says there was a constraints on the foreign key. That was the reason that he could not delete the foreign key object.
The error messages looked like this:

image

I had never had this issue before and went to do some research work.

Actually the solution was really simple. You simply need to change your create statement of your query to disable the constraints check.
For this example I used 2 object. Artists and their music tracks.

First the standard query lookes like this:

CREATE TABLE artist2(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
)
GO

CREATE TABLE [dbo].[track](
	[trackid] [int] NULL,
	[trackname] [text] NULL,
	[trackartist] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[track]  WITH CHECK ADD FOREIGN KEY([trackartist])
REFERENCES [dbo].[artist] ([artistid])
GO

You need to change the query so there is a “no check” in the query. The new query looks like this:

CREATE TABLE artist2(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
)
GO

CREATE TABLE [dbo].[track2](
	[trackid] [int] NULL,
	[trackname] [text] NULL,
	[trackartist] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[track2]  WITH NOCHECK ADD  CONSTRAINT [FK__track2__trackartist] FOREIGN KEY([trackartist])
REFERENCES [dbo].[artist2] ([artistid])
GO

ALTER TABLE [dbo].[track2] NOCHECK CONSTRAINT [FK__track2__trackartist]
GO

After creation your database schema looks like this:

As you can see the sql diagram of the first query looks exactly the same as the diagram of the second query.

image

First I thought this was too simple for a blog post but then I thought that there was not much information about this object on the net.

Leave a Reply

Your email address will not be published. Required fields are marked *