i am trying to create foreign keys but i got error 1822 .. please see my code below

CREATE TABLE employee(
  empid int auto_increment primary key,
  empfirstname varchar(200) not null,
  emplastname varchar(200) not null,
  email varchar(200) not null,
  officenumber int not null
);
CREATE TABLE customer(
  custid int auto_increment primary key,
  firstname varchar(200) not null,
  lastname varchar(200) not null,
  address varchar(200) not null,
  contact varchar(200)
);
CREATE TABLE product(
  productid int auto_increment primary key,
  productdesc varchar(500) not null,
  weight int not null,
  unit_cost int not null
);
CREATE TABLE productorder(
  productid int,
  orderid int,
  primary key(productid,orderid),
  constraint fk3 foreign key (productid) references product(productid),
  constraint fk4 foreign key (orderid) references productorder(orderid)
);
CREATE TABLE salesorder(
  salesorderid int auto_increment primary key,
  empid int not null,
  custid int not null,
  orderdate date not null,
  shippingmethod varchar (200) not null,
  constraint a_fk1 foreign key (empid) references employee(empid),
  constraint a_fk2 foreign key (custid) references customer(custid)
);

Answer

What is this meant to do?:

constraint fk4 foreign key (orderid) references productorder(orderid)

It’s not uncommon for a table to have a foreign key back to its own primary key, such as for records which have a parent/child relationship. But that doesn’t seem to be the case here.

More to the point of the error though, this isn’t referencing the entire primary key for the target table. That key has two fields:

primary key(productid,orderid)

So the DBMS can’t create the foreign key because its structure doesn’t match the target primary key.

If you want to create that foreign key, it would need to match. Probably something like this:

constraint fk4 foreign key (productid,orderid) references productorder(productid,orderid)

But it doesn’t appear that you need that foreign key at all, because it doesn’t seem to make sense in your data model. Instead I suspect orderid might need to be autoincrement and just use the productid foreign key. Something like this:

CREATE TABLE productorder(
  orderid int auto_increment primary key,
  productid int,
  constraint fk3 foreign key (productid) references product(productid)
);

(Note that there could be more changes you’d want to make to your data model. This answer doesn’t purport to provide you with a complete production-ready data model, just to correct the error. Your data model is likely to change/evolve as you develop your system.)