sql - 表中不存在键列。SQL
问题描述
所以我尝试查找其他问题并尝试了它们,但我无法弄清楚为什么我的 SQL 不起作用。它一直说 DepartmentID 表不存在,我将其声明为 Department 下的主键。
CREATE TABLE EMPLOYEE(
SSN INT NOT NULL,
WorkID INT NOT NULL,
BirthDate DATE NOT NULL,
Name VARCHAR(20) NOT NULL,
UNIQUE(WorkID),
PRIMARY KEY(SSN),
FOREIGN KEY(DepartmentID) REFERENCES DEPARTMENT(DepartmentID),
FOREIGN KEY(DeviceID) REFERENCES DEVICE(DeviceID)
);
CREATE TABLE DEPARTMENT(
DepartmentID INT NOT NULL,
DepartmentName VARCHAR (20) NOT NULL,
PRIMARY KEY(DepartmentID),
UNIQUE(DepartmentName)
);
CREATE TABLE PRODUCT(
ProductID INT NOT NULL,
Name VARCHAR(20) NOT NULL,
Isle VARCHAR(5) NOT NULL,
Company VARCHAR(20) NOT NULL,
PRIMARY KEY(ProductID),
FOREIGN KEY(DepartmentID) REFERENCES DEPARTMENT(DepartmentID)
);
CREATE TABLE DEVICE(
DeviceID INT NOT NULL,
DateReceived DATE NOT NULL,
PRIMARY KEY(DeviceID)
);
CREATE TABLE SALES(
SalesID INT NOT NULL,
Profit INT NOT NULL,
Revenue INT NOT NULL,
PRIMARY KEY(SalesID)
);
CREATE TABLE AmountSold(
FOREIGN KEY (SalesID) REFERENCES SALES(SalesID),
FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID)
);
解决方案
问题是您正在制作外键,但您引用的表中不存在 DepartmentID 列,第二个问题是您的订单不正确。
CREATE TABLE DEPARTMENT(
DepartmentID INT NOT NULL,
DepartmentName VARCHAR (20) NOT NULL,
PRIMARY KEY(DepartmentID),
UNIQUE(DepartmentName)
);
CREATE TABLE DEVICE(
DeviceID INT NOT NULL,
DateReceived DATE NOT NULL,
PRIMARY KEY(DeviceID)
);
CREATE TABLE EMPLOYEE(
SSN INT NOT NULL,
WorkID INT NOT NULL,
BirthDate DATE NOT NULL,
DepartmentID INT NOT NULL,
DeviceID INT NOT NULL,
Name VARCHAR(20) NOT NULL,
UNIQUE(WorkID),
PRIMARY KEY(SSN),
FOREIGN KEY(DepartmentID) REFERENCES DEPARTMENT(DepartmentID),
FOREIGN KEY(DeviceID) REFERENCES DEVICE(DeviceID)
);
CREATE TABLE PRODUCT(
ProductID INT NOT NULL,
Name VARCHAR(20) NOT NULL,
DepartmentID INT NOT NULL,
Isle VARCHAR(5) NOT NULL,
Company VARCHAR(20) NOT NULL,
PRIMARY KEY(ProductID),
FOREIGN KEY(DepartmentID) REFERENCES DEPARTMENT(DepartmentID)
);
CREATE TABLE SALES(
SalesID INT NOT NULL,
Profit INT NOT NULL,
Revenue INT NOT NULL,
PRIMARY KEY(SalesID)
);
CREATE TABLE AmountSold(
FOREIGN KEY (SalesID) REFERENCES SALES(SalesID),
FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID)
);
推荐阅读
- c# - 使用 NewtonsoftJSON 从 Spotify JSON 响应中获取 id
- laravel - 更新模型时如何在验证 API 中设置 Rule::unique()->ignore()?
- java - Java中状态模式的封装损坏
- php - 如何在url中指定城市
- java - Java - Rest API 场景
- python - 在使用 bs4 汤中找到第三个
- reactjs - 为什么这个 api 调用在 React 中被调用了两次?
- .net-core - .net 核心模型验证
- c# - 将 C# 类型化列表转换为具有指定属性的 JSON 数组
- sql - 在 4 个相似列上合并两个表并保留唯一列