Older SQL servers and referential integrity thru foreign keys

0
votes

I am trying to gain an understanding of referential integrity they the use of foreign keys between an entity and relation table.

The relational schema consist of 3 tables:

  • Suppliers (S),
  • Parts (P), and
  • the quantity each has purchased (SP).

The primary keys in each would be SNum in S, PNum in P and a composite primary and foreign keys of SNum and PNum in SP, referencing their respective tables.

If my understanding of referential integrity is correct, if I were to input an illegal entry into the SP table( one that doesn’t exist in the primary key in S or P) such as “S7”, shouldn’t their be an error occurring because there is no “S7” in the S table? Is it because the server type may be outdated and not picking up the integrity or am I not understanding correctly?

1
mysqldatabasereferential-integrity

1 Answers

0
votes

The MyISAM storage engine doesn't support foreign key constraints. They'll be parsed, then ignored.

See https://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html

You can run show create table tablename to see which storage engine you're using. You should almost certainly be using the InnoDB engine.