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

Moose AlBoo
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)
);
David

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

I am trying to export an excel and make it password protected. My code is given below.But i am getting error

I am trying create a button in which boolean function should be performed. below is my view code. Can anyone help me

I am trying to validate my shopping list with this code a "..." spread but got a return value of "milk," and just the first value

I am trying to create a text box which allows only numbers using typescripts. See below example

i was trying to create a modal, please check my code

Why my page is not redirecting to Google if I am giving correct password also? Please see my code

I am new to using mysql - as i was trying to create a table i got the error below

Why am I getting this error? Please see the details

I am trying to upload the captured/ selected image from gallery to my server but it is giving below error

I am trying to create a table with only two foreign key

I am trying to loop an embeded mp3 file on my page. I got the sound working, however I am having issues getting it to loop. Please Assist?

Small error but I don't see where - Symfony Foreign keys

I am trying to connect to mysql database with php code but it gives me this errors (see below)

I got an error in my code and beautifulsoup

i am trying to create a simple api to recieve any format of json data but getting 400 bad request.. please have a look to my code and correct

Getting error 1822 in MySQL when trying to create a foreign key

i am trying to create foreign key constraint in laravel 5.8

I am trying to create the below header with the help of XWPFDocument and merging cells

SQL - I am trying to isolate or group each transaction by each cardholder and my code is below

can you identify the error? i am trying to debug the code but i cant see the error

I am not able to getdownloadurl() from firebase. Please anybody help here's my code and error. (I am using Fragment)

I am not able to delete items in my todo list made with react, please help me with error in my code

How can I solve the error message I am receiving in my below code in my policy documents

i got the error When i printing the inorder traversing in my C code which is given below

mysql error 1822 when trying to create new table with a FOREIGN KEY

I am trying to write a simple smart contract in remix ide and getting encountered by a generic error again and again. Below is my code and error

I am trying to create circle avatar in Flutter. I have tried this but not giving me circle please check the code bellow:

Why am I getting MySQL error 1822 when creating a new table with a FOREIGN KEY?

Unable to test my code because of the following error: this.instructionsService.instruction.subscribe is not a function , please see the code below: