时间戳

首页 > 解决方案 > 错误代码 1824 - 无法打开引用表 + 我的语法中的其他问题

mysql - 错误代码 1824 - 无法打开引用表 + 我的语法中的其他问题

问题描述

我不确定为什么它无法打开引用的表。我确信我的语法有几个错误。

这是我尝试编写的第一个数据库。

USE AUTOMOBILE_COMPANY_02;

CREATE TABLE IF NOT EXISTS CUSTOMER(
    customerid INT NOT NULL PRIMARY KEY,
    Cfname VARCHAR(30) NOT NULL,
    CLname VARCHAR(30) NOT NULL,
    Cphone VARCHAR(15) NOT NULL UNIQUE,
    Cgender VARCHAR(10),
    Cyearly_salary VARCHAR(15),
    Cstreet VARCHAR (30),
    Ccity VARCHAR (30),
    Cstate VARCHAR(30),
    Cpostcode VARCHAR(6),
    productid INT NOT NULL,
    paymentid INT,
    FOREIGN KEY (productid) REFERENCES PRODUCT(productid),
    FOREIGN KEY (paymentid) REFERENCES PAYMENT(paymentid)
);

CREATE TABLE DEALERS(
    DealerName VARCHAR (10)NOT NULL PRIMARY KEY,
    DealerBrand VARCHAR (7),
    DealerModel VARCHAR (4),
    DealerColour VARCHAR (5),
    DealerAvailability VARCHAR (12)
);

CREATE TABLE EMPLOYEE(
    Essn INT NOT NULL PRIMARY KEY,
    Efirst_name VARCHAR(20) NOT NULL,
    Elast_name VARCHAR(20) NOT NULL,
    Ebirthday INT NOT NULL,
    Estreet VARCHAR (30),
    Ecity VARCHAR (30),
    Epostcode VARCHAR(6),
    Estate VARCHAR(30),
    Esalary DECIMAL(6) NOT NULL,
    Esex VARCHAR(6)
);

CREATE TABLE SUPPLIERS(
    Sparts VARCHAR (20) NOT NULL PRIMARY KEY,
    Sarticle_number INT NOT NULL,
    FOREIGN KEY Suppliers(Sparts)REFERENCES Manufacturer(Sparts) 
);

CREATE TABLE STORE(
    Sid INT NOT NULL PRIMARY KEY,
    Sstreet VARCHAR (30)NOT NULL,
    Scity VARCHAR (30)NOT NULL,
    Sstate VARCHAR(30)NOT NULL,
    Spostcode VARCHAR(6)NOT NULL,
    Smanager_staff VARCHAR(30),
    FOREIGN KEY EMPLOYEE(Essn) REFERENCES STORE(Essn),
    FOREIGN KEY SUPPLIER(Sparts) REFERENCES STORE(Sparts)
);

CREATE TABLE PAYMENT(
    paymentid INT PRIMARY KEY,
    bank VARCHAR (30) NOT NULL,
    customerid VARCHAR(20) NOT NULL,
    Essn INT ,
    amount DECIMAL(7)NOT NULL,
    payment_date DATETIME NOT NULL,
    productid INT NOT NULL,
    FOREIGN KEY PRODUCT(productid) REFERENCES PAYMENT(productid),
    FOREIGN KEY CUSTOMER(customerid) REFERENCES PAYMENT(customerid),
    FOREIGN KEY EMPLOYEE(Essn) REFERENCES PAYMENT(Essn)
);

CREATE TABLE PRODUCT(
    productid INT NOT NULL PRIMARY KEY,
    article_number VARCHAR (30)NOT NULL,
    brand VARCHAR (7),
    model VARCHAR (4),
    colour VARCHAR (5),
    availability VARCHAR (12),
    customerid INT NOT NULL,
    storeid INT NOT NULL,
    FOREIGN KEY CUSTOMER(customerid) REFERENCES PRODCUT(customerid),
    FOREIGN KEY Manufacturer(storeid) REFERENCES PRODCUT(storeid)
);

CREATE TABLE RENTAL(
    rentalid INT NOT NULL PRIMARY KEY,
    rental_date DATETIME NOT NULL,
    return_date DATETIME NOT NULL,
    customerid INT NOT NULL,
    Essn INT NOT NULL,
    FOREIGN KEY CUSTOMER(customerid) REFERENCES RENTAL(customerid),
    FOREIGN KEY EMPLOYEE(Essn) REFERENCES RENTAL(Essn)
);

CREATE TABLE Manufacturer(
    storeid INT NOT NULL PRIMARY KEY,
    street VARCHAR (30)NOT NULL,
    city VARCHAR (30)NOT NULL,
    state VARCHAR(30)NOT NULL,
    postcode VARCHAR(6)NOT NULL,
    article_number VARCHAR (30)NOT NULL,
    Sparts VARCHAR (20) NOT NULL,
    FOREIGN KEY Manufacturer(article_number)REFERENCES PRODUCT(article_number),
    FOREIGN KEY SUPPLIER(Sparts) REFERENCES Manufacturer(Sparts)
);

标签： mysqldatabasehelper

解决方案

您应该先创建表，然后使用 alter 命令添加外键。

CREATE TABLE IF NOT EXISTS CUSTOMER (
customerid INT NOT NULL PRIMARY KEY, 
Cfname VARCHAR(30) NOT NULL, 
CLname VARCHAR(30) NOT NULL,
Cphone VARCHAR(15) NOT NULL UNIQUE, 
Cgender VARCHAR(10), Cyearly_salary VARCHAR(15), 
Cstreet VARCHAR (30), 
Ccity VARCHAR (30), 
Cstate VARCHAR(30), 
Cpostcode VARCHAR(6), 
productid INT NOT NULL, 
paymentid INT );


CREATE TABLE PAYMENT(
paymentid INT PRIMARY KEY,
bank VARCHAR (30) NOT NULL,
customerid VARCHAR(20) NOT NULL,
Essn INT ,
amount DECIMAL(7)NOT NULL,
payment_date DATETIME NOT NULL,
productid INT NOT NULL);


CREATE TABLE PRODUCT(
productid INT NOT NULL PRIMARY KEY,
article_number VARCHAR (30)NOT NULL,
brand VARCHAR (7),
model VARCHAR (4),
colour VARCHAR (5),
availability VARCHAR (12),
customerid INT NOT NULL,
storeid INT NOT NULL );

ALTER TABLE CUSTOMER ADD  FOREIGN KEY (productid) REFERENCES PRODUCT(productid);                                          
ALTER TABLE CUSTOMER ADD  FOREIGN KEY (paymentid) REFERENCES PAYMENT(paymentid);

您可以以同样的方式继续使用其他表。

推荐阅读