I need to create three following tables: Employee, Customer and Sales. The EER diagram is attached: eer diagram

CREATE TABLE IF NOT EXISTS Employees
(
    employee_name char(40),
    working_area char(35),
    commission int,
    phone_no char(15),
    country varchar(25),
    employee_code char(6) PRIMARY KEY
);
    
CREATE TABLE IF NOT EXISTS Customers
(
    cust_code varchar(40) PRIMARY KEY,
    cust_city char(35),
    working_area varchar(35),
    cust_country varchar(20),
    grade int,
    opening_amt int,
    receive_amt int,
    payment_amt int,
    outstanding_amt int,
    phone_no varchar(17),
    FOREIGN KEY (employee_code) REFERENCES Employees(employee_code)
);

CREATE TABLE IF NOT EXISTS Sales 
(
    sale_amount int,
    advance_amount int,
    sale_date date,
    FOREIGN KEY (cust_code) REFERENCES Customers(cust_code),
    FOREIGN KEY (employee_code) REFERENCES Employees(employee_code),
    sale_description varchar(60),
    sale_num int PRIMARY KEY
);

The Employees table has no problem, but my Foreign Keys in Customers and Sales don't seem to work. Can somebody explain to me what I'm doing wrong please?

CodePudding user response：

You need to add the column that are referencing the primary key of the other tables.

the foreign keys alone aren't enough

CREATE TABLE IF NOT EXISTS Employees
(
    employee_name char(40),
    working_area char(35),
    commission int,
    phone_no char(15),
    country varchar(25),
    employee_code char(6) PRIMARY KEY
);
    
CREATE TABLE IF NOT EXISTS Customers
(
    cust_code varchar(40) PRIMARY KEY,
    cust_city char(35),
    working_area varchar(35),
    cust_country varchar(20),
    grade int,
    opening_amt int,
    receive_amt int,
    payment_amt int,
    outstanding_amt int,
    phone_no varchar(17),
  employee_code char(6),
    FOREIGN KEY (employee_code) REFERENCES Employees(employee_code)
);

CREATE TABLE IF NOT EXISTS Sales 
(
    sale_amount int,
    advance_amount int,
    sale_date date,
  employee_code char(6),
  cust_code varchar(40),
    FOREIGN KEY (cust_code) REFERENCES Customers(cust_code),
    FOREIGN KEY (employee_code) REFERENCES Employees(employee_code),
    sale_description varchar(60),
    sale_num int PRIMARY KEY
);

fiddle

CodePudding user response：

Yup. Logically, you should have the columns created first, then refer to them later. Otherwise, the computer will get confused.