postgresql - 引用、主键和外键的问题

Question

我在设置我的第一个大学数据库时遇到了麻烦。我将不得不引用公司和员工的 key 来工作。在一个员工只能在一个公司工作的关系。

我经常遇到一些错误，一个在主键之后，而且引用实际上不起作用。

仅供引用:我正在研究 postgresql。

我希望有人能帮助我。感谢您的帮助

CREATE SCHEMA p10_1_employee;
Set search_path to p10_1_employee;

CREATE TABLE employee(
    firstname varchar(20) NOT NULL,
    lastname varchar(20) NOT NULL,
    CONSTRAINT cPKfirstnamelastname PRIMARY KEY(firstname, lastname)
);

CREATE TABLE company(
    companyname varchar(20) NOT NULL,
    CONSTRAINT cPKcompanyname PRIMARY KEY (companyname)
);

CREATE TABLE works (
    personenname varchar(40),
    companyname varchar(20)
    CONSTRAINT cPKpersonennamecompanyname PRIMARY KEY (personenname, companyname)
    CONSTRAINT cFKfirstnamelastname FOREIGN KEY (personenname) REFERENCES employee(firstname, lastname),
    CONSTRAINT cFKcompanyname FOREIGN KEY (companyname) REFERENCES company(companyname)
);

Answer 1

您在 works 表中有几个错误(缺少逗号和错误的引用列数)。这是一个工作脚本

CREATE TABLE works (
    personenname_firstname varchar(20),
    personenname_lastname varchar(40),
    companyname varchar(20),
    CONSTRAINT cPKpersonennamecompanyname PRIMARY KEY (personenname_firstname, personenname_lastname, companyname),
    CONSTRAINT cFKfirstnamelastname FOREIGN KEY (personenname_firstname, personenname_lastname) REFERENCES employee,
    CONSTRAINT cFKcompanyname FOREIGN KEY (companyname) REFERENCES company
);