Delete Guild ?

[thisismastercap]

when i use query to delete all guild send me error ?

Query

 Spoiler

USE [SRO_VT_SHARD]
GO

ALTER TABLE [dbo].[_Guild] DROP CONSTRAINT [FK__Guild__AlliedClans]
GO

/****** Object: Table [dbo].[_Guild] Script Date: 08/4/2017 8:08:37 PM ******/
DROP TABLE [dbo].[_Guild]
GO

/****** Object: Table [dbo].[_Guild] Script Date: 08/4/2017 8:08:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[_Guild](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](64) NOT NULL,
[Lvl] [tinyint] NOT NULL,
[GatheredSP] [int] NOT NULL,
[FoundationDate] [smalldatetime] NOT NULL,
[Alliance] [int] NULL,
[MasterCommentTitle] [varchar](129) NULL,
[MasterComment] [varchar](2049) NULL,
[Booty] [int] NULL,
[Gold] [bigint] NOT NULL DEFAULT (0),
[LastCrestRev] [int] NOT NULL DEFAULT (0),
[CurCrestRev] [int] NOT NULL DEFAULT (0),
[MercenaryAttr] [tinyint] NOT NULL DEFAULT (0),
CONSTRAINT [PK__Guild] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX__Guild] UNIQUE NONCLUSTERED
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[_Guild] WITH CHECK ADD CONSTRAINT [FK__Guild__AlliedClans] FOREIGN KEY([Alliance])
REFERENCES [dbo].[_AlliedClans] ([ID])
GO

ALTER TABLE [dbo].[_Guild] CHECK CONSTRAINT [FK__Guild__AlliedClans]
GO

Error

 Spoiler

Msg 3726, Level 16, State 1, Line 8
Could not drop object 'dbo._Guild' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 21
There is already an object named '_Guild' in the database.

[MrCatGardens]

Holy ****, who told you to delete the full table?

USE SRO_VT_SHARD;
ALTER TABLE [_Guild] DROP  CONSTRAINT [FK__Guild__AlliedClans];
TRUNCATE TABLE _Guild;
SET IDENTITY_INSERT _Guild ON
INSERT INTO [_Guild] ([ID],[Name],[Lvl],[GatheredSP],[FoundationDate],[Alliance],[MasterCommentTitle],[MasterComment],[Booty],[Gold],[LastCrestRev],[CurCrestRev],[MercenaryAttr]) VALUES (0, 'dummy', 1, 0, getdate(), 0, null, null, 0, 0, 0, 0, 0);
SET IDENTITY_INSERT _Guild OFF
ALTER TABLE [_Guild] ADD CONSTRAINT [FK__Guild__AlliedClans] FOREIGN KEY(Alliance)REFERENCES [_AlliedClans](ID);

This should be the right way...
Drop constraint(s)
Truncate table
Insert the dummy guild with ID '0'
Add your deleted constraint(s)

[thisismastercap]

Originally Posted by skardana

Holy ****, who told you to delete the full table?

USE SRO_VT_SHARD;
ALTER TABLE [_Guild] DROP  CONSTRAINT [FK__Guild__AlliedClans];
TRUNCATE TABLE _Guild;
SET IDENTITY_INSERT _Guild ON
INSERT INTO [_Guild] ([ID],[Name],[Lvl],[GatheredSP],[FoundationDate],[Alliance],[MasterCommentTitle],[MasterComment],[Booty],[Gold],[LastCrestRev],[CurCrestRev],[MercenaryAttr]) VALUES (0, 'dummy', 1, 0, getdate(), 0, null, null, 0, 0, 0, 0, 0);
SET IDENTITY_INSERT _Guild OFF
ALTER TABLE [_Guild] ADD CONSTRAINT [FK__Guild__AlliedClans] FOREIGN KEY(Alliance)REFERENCES [_AlliedClans](ID);

This should be the right way...
Drop constraint(s)
Truncate table
Insert the dummy guild with ID '0'
Add your deleted constraint(s)

Done Fixed Ty

[kubed]

not works with me can any one help !