SQL Server - Create Table script not working

1
votes

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.

2
sql-servertsqlcreate-tablessms
do you have drop permission? – Victor Ribeiro da Silva Eloy
@VictorRibeirodaSilvaEloy yes – Jamie Barker
are you sure about the schema name "[MySchema]"? – Karthik Ganesan

2 Answers

5
votes

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
0
votes

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