时间戳

首页 > 解决方案 > 尝试学习Mysql并遇到外键约束是错误的错误

mysql - 尝试学习Mysql并遇到外键约束是错误的错误

问题描述

出于某种原因，由于外键错误，我的代码不允许我创建项目表。我尝试了一些不同的事情，但似乎无法让它发挥作用，我尝试在这里寻找解决方案，但似乎无法做到。任何帮助将非常感激。

CREATE TABLE PEOPLE (
    NAME VARCHAR(32) NOT NULL,
    GENDER ENUM('Male', 'Female', 'Other'),
    DOB DATE NOT NULL,
    SALARY VARCHAR(16) NOT NULL,
    PROJECT VARCHAR(32) NOT NULL,
    BUSINESS_NAME VARCHAR(32) NOT NULL,
    PRIMARY KEY(NAME, PROJECT)
);

CREATE TABLE PEOPLE_EMAILS (
    NAME_ID VARCHAR(32) NOT NULL,
    EMAIL VARCHAR(64) NOT NULL,
    PRIMARY KEY(EMAIL),
    FOREIGN KEY(NAME_ID) REFERENCES PEOPLE(NAME)
);

CREATE TABLE PEOPLE_PHONE (
    NAME_ID2 VARCHAR(32) NOT NULL,
    PHONE_NUMBER VARCHAR(32) NOT NULL,
    PRIMARY KEY (PHONE_NUMBER),
    FOREIGN KEY(NAME_ID2) REFERENCES PEOPLE(NAME)
);

CREATE TABLE PROJECTS (
    PROJECT_NAME VARCHAR(32) NOT NULL,
    PROJECT_LOCATION VARCHAR(32) NOT NULL,
    BUDGET VARCHAR(16) NOT NULL,
    FOREIGN KEY(PROJECT_NAME) REFERENCES PEOPLE(PROJECT)
);

标签： mysqlsqldatabase-designforeign-keyscreate-table

解决方案

大概，您想要相反的外键。你会期望人们引用项目而不是项目引用人。

这意味着您需要先创建项目表，然后再创建人员表。此外，您需要一个适当的项目主键，以便您可以在人们中引用它（我假设project_name）。

CREATE TABLE PROJECTS (
    PROJECT_NAME VARCHAR(32) NOT NULL,
    PROJECT_LOCATION VARCHAR(32) NOT NULL,
    BUDGET VARCHAR(16) NOT NULL,
    PRIMARY KEY (PROJECT_NAME)
);

CREATE TABLE PEOPLE (
    NAME VARCHAR(32) NOT NULL,
    GENDER ENUM('Male', 'Female', 'Other'),
    DOB DATE NOT NULL,
    SALARY VARCHAR(16) NOT NULL,
    PROJECT VARCHAR(32) NOT NULL,
    BUSINESS_NAME VARCHAR(32) NOT NULL,
    PRIMARY KEY(NAME, PROJECT),
    FOREIGN KEY(PROJECT) REFERENCES PROJECTS(PROJECT_NAME)
);

CREATE TABLE PEOPLE_EMAILS (
    NAME_ID VARCHAR(32) NOT NULL,
    EMAIL VARCHAR(64) NOT NULL,
    PRIMARY KEY(EMAIL),
    FOREIGN KEY(NAME_ID) REFERENCES PEOPLE(NAME)
);

CREATE TABLE PEOPLE_PHONE (
    NAME_ID2 VARCHAR(32) NOT NULL,
    PHONE_NUMBER VARCHAR(32) NOT NULL,
    PRIMARY KEY (PHONE_NUMBER),
    FOREIGN KEY(NAME_ID2) REFERENCES PEOPLE(NAME)
);

DB Fiddle 上的演示

推荐阅读