CODEWITHSUNDEEP
postgresql
Stevie
Stevie

Reputation: 43

Problem with references, Primary and Foreign Keys

I'm really having trouble setting up my first Database for university. I will have to reference the keys from company and employee to works. In a relation that an employee can only work in one company.

I constantly get some error, one after the Primary Key and also the references doesn't really work.

FYI: I'm working on postgresql.

I hope somebody can help me. Thanks for your help 

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)
);

Upvotes: 0

Views: 96

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

You have several bugs in the works table (missing commas and a wrong number of referencing columns). Here is a working script

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
);

Upvotes: 2

Related Questions