SQL报错：无法找到数据类型Schedule_type，求技术解决方案

这个报错的核心原因很明确：你尝试使用一个名为Schedule_type的用户定义表值类型，但要么你的数据库系统不支持这种类型（比如MySQL、PostgreSQL就没有表值类型作为列的特性），要么你的使用方式不符合数据库的要求。而且从你后续的代码逻辑来看，你其实根本不需要这个表值类型——你真正想要的是让Employee表关联Schedule表来记录员工班次。

具体问题分析 #

• 你先创建了Schedule_type表值类型，然后在第一个Employee表定义里用它作为列类型，但这在多数数据库中是不允许的（只有SQL Server等少数数据库支持表值类型，且用法也不是直接作为列）。
• 后续你又创建了Schedule表来存储班次数据，然后又删除它，接着修改Employee表，最后又重新创建Employee表关联Schedule——这一系列操作不仅冗余，还因为你删除了Schedule表，导致最后创建Employee表的外键约束直接失效。

修正后的完整SQL代码 #

我已经帮你整理了逻辑清晰、无错误的版本：

-- 创建Section表并插入数据
Create table Section (
    id_section int not null,
    namesection varchar(25) not null,
    description varchar(100),
    Primary Key (id_section)
);

Insert into Section (id_section, namesection, description) 
values 
(1, 'Women', 'Clothes for women'),
(2, 'Men', 'Clothes for men'),
(3, 'Children', 'Clothes for children');

-- 创建Product表并插入数据
Create table Product (
    id_product int not null,
    nameproduct varchar(25) not null,
    price int not null,
    id_section int,
    Primary key (id_product),
    constraint fk_section foreign key (id_section) references Section (id_section)
);

Insert into Product (id_product, nameproduct, price, id_section) 
values 
(1, 'T-shirt blue', 15, 1),
(2, 'Skirt- red', 30, 1),
(3, 'Dress black', 50, 1),
(4, 'Shirt', 45, 2),
(5, 'T-shirt white', 15, 2),
(6, 'Pants', 25, 2),
(7, 'T-shirt black', 15, 3),
(8, 'Pants', 25, 3);

select * from Product;

alter table Product drop price;

-- 创建Showcase表并插入数据
Create table Showcase (
    id_showcase int not null,
    description varchar(200),
    capacity int,
    Primary key(id_showcase),
    id_product int,
    constraint fk_product foreign key (id_product) references Product (id_product)
);

Insert into Showcase (id_showcase, description, id_product ) 
values
(1,'women of showcase', 1),
(2,'women of showcase', 2),
(3,'women of showcase', 1),
(4,'women of showcase', 2),
(5,'men of showcase', 3),
(6,'men of showcase', 3),
(7,'men of showcase', 4),
(8,'children of showcase', 5),
(9,'children of showcase', 6),
(10,'children of showcase', 5),
(11,'children of showcase', 6);

-- 创建Warehouse表并插入数据
Create table Warehouse(
    id_warehouse int not null,
    description varchar(200),
    Primary Key(id_warehouse),
    id_product int,
    foreign key (id_product) references Product (id_product)
);

Insert into Warehouse (id_warehouse, description, id_product ) 
values
(1,'women of showcase', 1),
(2,'men of showcase', 4),
(3,'children of showcase', 6);

-- 先创建Schedule表（不要删除它！）
Create table Schedule(
    id_schedule int not null,
    description varchar(100),
    Primary key (id_schedule)
);

Insert into Schedule(id_schedule, description) 
values
(1, 'Day Shift'),
(2, 'Night Shift');

select * from Schedule ;

-- 直接创建正确的Employee表，关联Schedule
Create table Employee (
    id_employee int not null,
    employeename varchar(25) not null,
    address varchar(30) not null,
    email varchar(20) not null,
    phone varchar(9) not null,
    Primary Key (id_employee),
    id_schedule int,
    foreign key(id_schedule) references Schedule (id_schedule)
);

Insert into Employee(id_employee, employeename, address, email, phone,id_schedule) 
values
(1, 'Grumet Alina', 'bl.Unirii 1B','gr.alina@gmail.com','069888555',1),
(2, 'Stefan Sabin', 'str.Alex.Makedonski','str.sab@gmail.com','085588555',1),
(3, 'Dimitrescu Alex', 'Liverpool str.1c','sim.alexa@gmail.com','45885585',1),
(4, 'Gori Eva', 'Bucharest str.24','gr.eva@gmail.com','85885885',1),
(5, 'Istrate Maria', 'sectorul1','ist.mar@gmail.com','555584751',2),
(6, 'Georghiu Alina', 'Ilfov','geor.and@gmail.com','069585585',2),
(7, 'Gonta Mihaela', 'lozova','gont.mih@gmail.com','258968589',2);

-- 创建Sale表
Create table Sale (
    id_sale int not null,
    cod_bar int not null,
    sale_nr varchar(4) not null,
    Primary Key (id_sale),
    id_employee int,
    foreign key(id_employee) references Employee(id_employee)
);

关键修正点 #

• 移除了Create Type Schedule_type语句：你不需要这个表值类型，因为你的需求是通过外键关联Schedule表来记录员工班次，而不是在Employee表中存储一个表类型的列。
• 调整了Employee表的创建顺序：先创建并保留Schedule表，再创建Employee表并建立外键关联，避免了因删除Schedule表导致的外键失效问题。
• 整理了冗余操作：删除了重复创建Employee表、删除Schedule表等无意义的步骤，让整个SQL逻辑更连贯。

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