SQL Server - Create Table script not working

Question

Trying to add a table to my database in SQL Server Management Studio but it's throwing a wobbly. I'm sure it's real simple but my brain has gone to mush and I can't find the problem. Basically it's telling me the database already exists, yet it clearly doesn't.

Error(s):

Msg 3701, Level 11, State 5, Line 2
Cannot drop the table 'MySchema.mix_Case_Study-Module', because it does not exist or you do not have permission.

Msg 2714, Level 16, State 5, Line 4
There is already an object named 'mix_Case_Study-Module' in the database.

Msg 1750, Level 16, State 0, Line 4
Could not create constraint. See previous errors.

Msg 4902, Level 16, State 1, Line 2
Cannot find the object "MySchema.mix_Case_Study-Module" because it does not exist or you do not have permissions.

SQL:

USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [MySchema].[mix_Case_Study-Module]

CREATE TABLE [MySchema].[mix_Case_Study-Module](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Active] [bit] NOT NULL,
    [Case Study ID] [int] NOT NULL,
    [Module ID] [int] NOT NULL,
    [Position] [int] NOT NULL,
    CONSTRAINT [mix_Case_Study-Module] 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]
) ON [PRIMARY]

GO

ALTER TABLE [MySchema].[mix_Case_Study-Module] ADD  CONSTRAINT [DF_mix_Case_Study-Module_Active]  DEFAULT ((1)) FOR [Active]

GO

Any help appreciated.

do you have drop permission?

Victor Ribeiro da Silva Eloy
– Victor Ribeiro da Silva Eloy

2014-06-04 15:27:20 +00:00
Commented Jun 4, 2014 at 15:27 — Victor Ribeiro da Silva Eloy
– Victor Ribeiro da Silva Eloy, Commented Jun 4, 2014 at 15:27
@VictorRibeirodaSilvaEloy yes

Jamie Barker
– Jamie Barker

2014-06-04 15:28:42 +00:00
Commented Jun 4, 2014 at 15:28 — Jamie Barker
– Jamie Barker, Commented Jun 4, 2014 at 15:28
are you sure about the schema name "[MySchema]"?

Karthik Ganesan
– Karthik Ganesan

2014-06-04 15:30:32 +00:00
Commented Jun 4, 2014 at 15:30 — Karthik Ganesan
– Karthik Ganesan, Commented Jun 4, 2014 at 15:30

codingbiz · Accepted Answer · 2014-06-04 15:28:24Z

5

Your constraint name and table name are the same.

CREATE TABLE [MySchema].[mix_Case_Study-Module]

and

CONSTRAINT [mix_Case_Study-Module] PRIMARY KEY CLUSTERED

answered Jun 4, 2014 at 15:28

codingbiz

26.4k9 gold badges62 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamie Barker Over a year ago

You sir, are a superstar.

Radu Gheorghiu · Accepted Answer · 2014-06-04 15:28:32Z

You are having issues because you're first trying to drop a table that does not exist.

You should be using something like this:

USE [MyDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID([MySchema].[mix_Case_Study-Module], 'u') IS NOT NULL
    DROP TABLE [MySchema].[mix_Case_Study-Module]

CREATE TABLE [MySchema].[mix_Case_Study-Module](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Active] [bit] NOT NULL,
    [Case Study ID] [int] NOT NULL,
    [Module ID] [int] NOT NULL,
    [Position] [int] NOT NULL,
    CONSTRAINT [mix_Case_Study-Module_PK] 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]
) ON [PRIMARY]

GO

ALTER TABLE [MySchema].[mix_Case_Study-Module] ADD  CONSTRAINT [DF_mix_Case_Study-Module_Active]  DEFAULT ((1)) FOR [Active]

GO

Collectives™ on Stack Overflow

SQL Server - Create Table script not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related