SQL脚本执行报错排查：多语法错误及数据库已存在问题

原始SQL脚本 #

USE master;
GO

CREATE DATABASE Sales
ON
(NAME= Sales_Data,
FILENAME = 'F:\\DB\\Customer DB\\Sales_Data.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5)
LOG ON
(NAME = Sales_Log,
FILENAME = 'F:\\DB\\Customer DB\\Sales_Log.ldf',
SIZE = 5,
MAXSIZE= 25,
FILEGROWTH = 5);
GO

USE Sales;
GO

CREATE SCHEMA Sales_schema;
CREATE SCHEMA Production;

USE Sales;
GO

CREATE TABLE Sales.sales.customers
(
    customer_id int,
    first_name varchar (15) NOT NULL,
    last_name varchar (15) NOT NULL,
    email_address varchar (50) NOT NULL,
    phone varchar (15),
    state varchar (15),
    city varchar (15) NOT NULL,
    street varchar (30) NOT NULL,
    zip_code varchar (5) NOT NULL,
 
    CONSTRAINT customers_uq UNIQUE KEY (phone),
    CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE Production.categories
(
    category_id INT PRIMARY KEY,
    category_name VARCHAR (20)
);

CREATE TABLE Production.brands
(
    brand_id INT,
    brand_name VARCHAR (20),

    CONSTRAINT brands_pk PRIMARY KEY (brand_id)
);

报错信息 #

我原本预期该SQL脚本执行无任何错误，但实际运行时出现多个异常，烦请排查以下错误：

Msg 156, Level 15, State 1, Line 21

关键字 'create' 附近有语法错误。

Msg 1801, Level 16, State 3, Line 26

数据库 'SALES' 已存在，请选择其他名称。

Msg 156, Level 15, State 1, Line 46

关键字 'CREATE' 附近有语法错误。

Msg 156, Level 15, State 1, Line 63

关键字 'key' 附近有语法错误。

错误排查与修复方案 #

1. 数据库已存在错误（Msg 1801） #

• 问题：脚本直接执行CREATE DATABASE Sales，若目标数据库已存在则触发报错。
• 修复：添加存在性判断，仅当数据库不存在时才执行创建逻辑：

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Sales')
BEGIN
    CREATE DATABASE Sales
    ON
    (NAME= Sales_Data,
    FILENAME = 'F:\\DB\\Customer DB\\Sales_Data.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5)
    LOG ON
    (NAME = Sales_Log,
    FILENAME = 'F:\\DB\\Customer DB\\Sales_Log.ldf',
    SIZE = 5,
    MAXSIZE= 25,
    FILEGROWTH = 5);
END
GO

2. 架构引用错误（Msg 156 Line21、Line46） #

• 问题：脚本创建了Sales_schema架构，但创建表时错误写为Sales.sales.customers——当前数据库已切换为Sales，无需重复指定数据库名，且架构名应为Sales_schema而非sales，导致语法解析失败。Line21的报错是后续表创建错误引发的连锁提示。
• 修复：将表名改为Sales_schema.customers，省略冗余的数据库名。

3. UNIQUE约束语法错误（Msg 156 Line63） #

• 问题：SQL Server中唯一约束的合法语法为UNIQUE，UNIQUE KEY不属于T-SQL标准语法。
• 修复：将CONSTRAINT customers_uq UNIQUE KEY (phone)修改为CONSTRAINT customers_uq UNIQUE (phone)。

修复后的完整脚本 #

USE master;
GO

-- 仅当数据库不存在时创建
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'Sales')
BEGIN
    CREATE DATABASE Sales
    ON
    (NAME= Sales_Data,
    FILENAME = 'F:\\DB\\Customer DB\\Sales_Data.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5)
    LOG ON
    (NAME = Sales_Log,
    FILENAME = 'F:\\DB\\Customer DB\\Sales_Log.ldf',
    SIZE = 5,
    MAXSIZE= 25,
    FILEGROWTH = 5);
END
GO

USE Sales;
GO

CREATE SCHEMA Sales_schema;
CREATE SCHEMA Production;

GO -- 分隔批次，避免执行逻辑冲突

CREATE TABLE Sales_schema.customers
(
    customer_id int,
    first_name varchar (15) NOT NULL,
    last_name varchar (15) NOT NULL,
    email_address varchar (50) NOT NULL,
    phone varchar (15),
    state varchar (15),
    city varchar (15) NOT NULL,
    street varchar (30) NOT NULL,
    zip_code varchar (5) NOT NULL,
 
    CONSTRAINT customers_uq UNIQUE (phone),
    CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

CREATE TABLE Production.categories
(
    category_id INT PRIMARY KEY,
    category_name VARCHAR (20)
);

CREATE TABLE Production.brands
(
    brand_id INT,
    brand_name VARCHAR (20),

    CONSTRAINT brands_pk PRIMARY KEY (brand_id)
);

内容的提问来源于stack exchange，提问作者Ahmed Adel