mysql - Creating A Join In SQL Xampp

Question

I am trying to create a join between my database tables customers and staff. However getting an error: Can't create table staff_db.customer_orders (errno: 121 "Duplicate key on write or update").

CREATE TABLE staff(
staff_id int(12) not null,
staff_name varchar(20),
staff_address varchar(32),
staff_department varchar(20),
PRIMARY KEY (staff_id)
);

CREATE TABLE customers(
customer_id int(12) not null,
customer_name varchar(20),
customer_address varchar(32),
customer_product varchar(25),
staff_id int(12) not null,
FOREIGN KEY staff_fk(staff_id) REFERENCES staff(staff_id),
PRIMARY KEY (customer_id)
);

CREATE TABLE customer_orders(

customer_id int(12) not null,
staff_id int(12) not null,
FOREIGN KEY customer_fk(customer_id) REFERENCES customers(customer_id),
FOREIGN KEY staff_fk(staff_id) REFERENCES staff(staff_id),
PRIMARY KEY (customer_id,staff_id)
);

question from:https://stackoverflow.com/questions/65871425/creating-a-join-in-sql-xampp

Answer 1

In your customer_orders table, you named the foreign key " FOREIGN KEY staff_fk (staff_id) REFERENCES staff (staff_id) " like the one in your customers table " FOREIGN KEY staff_fk (staff_id) REFERENCES staff (staff_id) ". Names must be unique. Change the name to one of the two and your problem will normally be solved.