Mysql : expected error because of the coinstraint but it doesnt happen

Question

i have a project about constructing a database of a newspaper in mysql . newspaper_leaflet entity has a column "publisher" that references in the email of the entity of the publisher and the email of the publisher references in the email of the entity of employees. i expect when i insert values into newspaper_leaflet in which the email(email is the primary key for the entity of publisher and for the entity of employees) doesnt exist in publisher entity ,to throw me an error ,altough that doesnt happen , and i dont understand why. i could make a stored procedure to solve this problem ,i guess, but i dont understand how the constraint works if it doesnt protect the table from inserting different emails from that ,that exists in publisher entity.

CREATE TABLE Employee(
  email VARCHAR(30),
  employment_date DATE,
  name VARCHAR(20),
  surname VARCHAR(30),
  salary FLOAT,
  PRIMARY KEY(email)
);

CREATE TABLE Publisher(  email VARCHAR(30),
  PRIMARY KEY(email),
  CONSTRAINT publishertoemployee
  FOREIGN KEY (email) REFERENCES Employee (email)
  ON DELETE CASCADE
  ON UPDATE CASCADE
);


CREATE TABLE Leaflet(
      leaflet_number INT,
      newspaper_name VARCHAR(20),
      numberofpages INT Default 30,
      newspaper_leaflets INT,
      leaflets_notsold INT,
      publish_date DATE,
      leaflet_publisher VARCHAR(30),
      PRIMARY KEY (leaflet_number,newspaper_name),
      CONSTRAINT publisherofnp
      FOREIGN KEY (leaflet_publisher) REFERENCES Publisher (email),
      CONSTRAINT npleafetconnection
      FOREIGN KEY (newspaper_name) REFERENCES Newspaper (newspaper_name)
      ON DELETE CASCADE
      ON UPDATE CASCADE
    );

this is my git if you want to see all the db https://github.com/kwstasmel/SQL-Project PS: I am a newbie ,every polite advise will be appreciated.

Hi kwstasmei, welcome to Stack Overflow. We're glad to have you here. I think the most common explanation is that your tables were created as MyISAM tables, which ignore FOREIGN KEY constraints. They don't report any error, but they do not remember the constraint, and therefore they don't enforce it. I suggest you run `SHOW CREATE TABLE Publisher` in the MySQL client, and check that the constraint really exists in this table after you create it. Also that should show you the ENGINE used for the table, I guess it is MyISAM. Only InnoDB will enforce foreign key constraints. — Bill Karwin, Mar 01 '20 at 18:13
I wrote about this in more detail here: https://stackoverflow.com/a/380074/20860 — Bill Karwin, Mar 01 '20 at 18:14
Ohhh , that would explain a lot, because when i wrote insert codes in my university computers (that use innodb), i remember that i had that problem . Thank you very much! — kwstasmel, Mar 01 '20 at 18:26
I have always thought it was weird that MyISAM doesn't report an error when you try to use a feature that it doesn't support. It just happily creates the table _without_ foreign keys. This has caused confusion for a lot of people! — Bill Karwin, Mar 01 '20 at 18:38
This is a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Mar 01 '20 at 18:42

score 0 · Answer 1 · answered Mar 01 '20 at 18:29

0

It might be that you're using MyISAM https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html, which doesn't support foreign keys.

answered Mar 01 '20 at 18:29

guest

6,450
30
44

Mysql : expected error because of the coinstraint but it doesnt happen

1 Answers1