FK Constraint problem - SQL Server Forums

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)

FK Constraint problem

Author

Topic

jaimin
Starting Member

21 Posts

Posted - 2008-02-19 : 04:20:55

i am creating a table using a script.
facing the following error:
"Could not drop object 'EscalationType' because it is referenced by a FOREIGN KEY constraint."

i am using sql server 2005
This is my script.

USE [ITHelpDept]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

GO
IF EXISTS
(SELECT * FROM sysobjects WHERE name = 'EscalationType')
DROP TABLE EscalationType
CREATE TABLE [dbo].[EscalationType](
[SREscalationTypeGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_EscalationType_SREscalationTypeGuid] DEFAULT (newid()),
[SREscalationTypeName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Escalation] PRIMARY KEY CLUSTERED
(
[SREscalationTypeGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 04:36:00

As evident from the error, the table is referenced by other table via Foreign key constraint. Either remove constraint or drop child table first before removing this table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2008-02-19 : 04:49:05

Is there any way to drop constraint (if exists) and than re-add it again?

Jaimin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 04:53:15

Yes.

Alter Table <table>
Drop Constraint <constraint-name>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2008-02-19 : 04:59:08

This is what i have done, but i think i am going wrong somewhere.
plz check it and correct me.

IF EXISTS
(SELECT * FROM sysobjects WHERE name = 'EscalationType'AND type in (N'U'))
ALTER TABLE EscalationType
DROP CONSTRAINT PK_Escalation
--DROP TABLE EscalationType
--CREATE TABLE [dbo].[EscalationType](
ALTER TABLE [dbo].[EscalationType](
[SREscalationTypeGuid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_EscalationType_SREscalationTypeGuid] DEFAULT (newid()),
[SREscalationTypeName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Escalation] PRIMARY KEY CLUSTERED
(
[SREscalationTypeGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Thanks,
Jaimin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 05:01:31

You should not drop the primary key. You will have to find out where the foreign key is defined which is referencing EscalationType table and remove the foreign key constraint from that table.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2008-02-19 : 05:10:36

I found this -

ALTER TABLE [dbo].[EscalationDets] WITH CHECK ADD CONSTRAINT [FK_EscalationDets_EscalationType1] FOREIGN KEY([SREscalationTypeGuid])
REFERENCES [dbo].[EscalationType] ([SREscalationTypeGuid])
GO
ALTER TABLE [dbo].[EscalationDets] CHECK CONSTRAINT [FK_EscalationDets_EscalationType1]
GO
ALTER TABLE [dbo].[EscalationDets] WITH CHECK ADD CONSTRAINT [FK_EscalationDets_ServiceRequest] FOREIGN KEY([SRGuid])
REFERENCES [dbo].[ServiceRequest] ([SRGuid])
GO
ALTER TABLE [dbo].[EscalationDets] CHECK CONSTRAINT [FK_EscalationDets_ServiceRequest]
GO

Is this the place where i need to change? if I am right, than can you plz send me the correct/modified script.

Thanks
Jaimin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 05:18:15

I already told you the syntax. Just replace table name and constraint name with placeholders.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2008-02-19 : 06:08:34

i did that.
but now how will I add the Foreign key again??

Thanks
Jaimin

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-19 : 06:19:39

Don't you have SQL Server help installed?

ALTER TABLE <table-name>
ADD CONSTRAINT <constraint-name>
FOREIGN KEY REFERENCES <ref_table>(<ref_column>)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jaimin
Starting Member

21 Posts

Posted - 2008-02-19 : 06:33:52

i implemented the same thing. like this:

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EscalationDets_EscalationType1]') AND parent_object_id = OBJECT_ID(N'[dbo].[EscalationDets]'))
ALTER TABLE [dbo].[EscalationDets] DROP CONSTRAINT [FK_EscalationDets_EscalationType1]
GO
ALTER TABLE [dbo].[EscalationDets] WITH CHECK ADD CONSTRAINT [FK_EscalationDets_EscalationType1] FOREIGN KEY([SREscalationTypeGuid])
REFERENCES [dbo].[EscalationType] ([SREscalationTypeGuid])
GO

i executed this script separately, but still it's giving me the same error??
Whatever you told me is working perfectly fine when executed separately.
but when i execute the whole script (which has some tables and SP), than it is throwing me the same error:

"Could not drop object 'EscalationType' because it is referenced by a FOREIGN KEY constraint."

So where is problem lying?? I am not able to figure it out.

Thanks
Jaimin

mahesh_bote
Constraint Violating Yak Guru

298 Posts

Posted - 2008-02-19 : 06:46:40

quote:
Originally posted by jaimin

i did that.
but now how will I add the Foreign key again??

Thanks
Jaimin

It would be better if you would have been Disabled it instead of Dropping.

Mahesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources