to drop fk - 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 2008 Forums

Transact-SQL (2008)

to drop fk

Author

Topic

Arun.G
Yak Posting Veteran

81 Posts

Posted - 2010-05-26 : 03:31:32

The below one is my table syntax:

CREATE TABLE [dbo].[PNET_PFILE_CHKLIST](
[CHK_ID] [int] IDENTITY(1,1) NOT NULL,
[PI_EMPLOYEEID] [int] NOT NULL,
[CHKLIST_TYPE] [varchar](10) NULL,
[CHKLIST_ID] [int] NOT NULL,
[CL_STATUS] [bit] NULL,
[SUBMIT_DATE] [date] NULL,
[REMARKS] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[CHK_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[PNET_PFILE_CHKLIST] WITH CHECK ADD FOREIGN KEY([CHKLIST_ID])
REFERENCES [dbo].[PNET_MASTER_CHECKLIST] ([CHECKLISTID])
GO

ALTER TABLE [dbo].[PNET_PFILE_CHKLIST] WITH CHECK ADD FOREIGN KEY([PI_EMPLOYEEID])
REFERENCES [dbo].[PNET_PFILE_DATATABLE1] ([PI_EmployeeId])
GO

now i want to drop the foreign key constraint from pnet_master_checklist table

i tried the queries below:

1)alter table pnet_pfile_chklist drop constraint CHKLIST_ID
2)ALTER TABLE pnet_pfile_chklist DROP FOREIGN KEY CHKLIST_ID

BUT BOTH ARE FAILED,

HOW TO DROP THAT CONSTRAINT?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-26 : 09:02:35

It will be better if you specify the constraint name while creating it. This will easy your work.

Try this:

ALTER TABLE [dbo].[PNET_PFILE_CHKLIST]
ADD CONSTRAINT FK_PNET_PFILE_CLIST FOREIGN KEY ([CHKLIST_ID])
REFERENCES [PNET_MASTER_CHECKLIST] ([CHECKLISTID])
GO

ALTER TABLE [dbo].[PNET_PFILE_CHKLIST]
DROP CONSTRAINT FK_PNET_PFILE_CLIST

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources