BO
70 2
Created
Updated
Viewed
8.3k times

I have an issue where I have foreign keys set on tables, but it seems that these tables will let me add any values, even if the foreign keys (FK) do not exist. Thus it seems like the foreign key constraints are not being enforced.

To explain my issue, let's say I have 3 tables in my database:

  1. user
  2. group
  3. ugroup

Let's say they had the following columns and values:

Table user:

uid
1000
1001

Table group:

gid
1
2

Table ugroup:

uidgid
10001
10012

In table ugroup, the uid and gid would be foreign keys: uid referencing user.uid and gid referencing group.gid

I have a similar setup in a few of my tables and I still don't understand the foreign keys fully. I somewhat understand the theory behind them (which is why I started using them) but I can't see how it affects the database and how it helps me use the database.

I read it was supposed to be for accuracy, so I tried to add uid and gid to ugroup that didn't exist in either user table or group and it added no problem, which got me confused.

All of my databases are InnoDB, both the user.uid and group.gid are primary keys and they are indexed, all of them are int type of 11 character length.

Should I also index the ugroup.uid and ugroup.gid? Or have I completely missed the point of foreign keys? Or are foreign keys obsolete now?

The following quote doesn't seem to happen to me:

Thus, because an aircraft type with identifier 616 doesn't exist in the aircrafttype, MySQL rejects the record with that value for the aircraft table. In this manner, foreign key constraints can significantly help in enforcing the data integrity of the tables in a database and reducing the occurrences of "bad" or inconsistent field values.

To the best of my knowledge, all of the following are met:

  • All the tables in the relationship must be InnoDB tables. In non-InnoDB tables, the FOREIGN KEY…REFERENCES modifier is simply ignored by MySQL.
    • The fields used in the foreign key relationship must be indexed in all referenced tables (InnoDB will automatically create these indexes for you if you don't specify any).
    • The data types of the fields named in the foreign key relationship should be similar. This is especially true of integer types, which must match in both size and sign.
add a comment
0

3 Replies

  • Votes
  • Oldest
  • Latest
BO
70 2
Replied
Updated

Ok, never mind. For some reason, my PHPMyAdmin didn't do the relation correctly. I had to remove the table and hard-code the foreign key in there.

  • 0
    Yeah there are typically two reasons why the foreign key constraint is not working. First the constraint actually isn't in place as was in your case, or two you are using a database type like MyISAM that does not enforce foreign key constraints. — Brian Wozeniak
add a comment
0
BO
70 2
Replied

I wonder how dangerous it really is to set the records to all delete on delete of the source

  • 0
    Its not dangerous, you just have to plan things out. For example if you have a users table, and then tables that depend on the user, for example lets say a settings table then it would make sense that if the user is deleted, then all of their settings should be deleted. So if the settings table had a foreign key of user_id that associates with the id of the users table, and then that id was deleted from the users table, it would make no sense to have the settings remain, so an ON DELETE CASCADE is perfect for this. — Brian Wozeniak
  • 0
    Ok, thanks. Reading all the "precautions" from the tutorials made me weary of using such things. — Bogey
add a comment
0
Replied
Updated

Remember that InnoDB is the only engine that enforces the FK. MyISAM will not enforce the FK.

  • 0
    Will the other DB types simply ignore the FK constraint? Or will I be getting errors if I transfer this database to a different DB type? — Bogey
  • 0
    InnoDB and MyISAM are the engine types in MySQL. I have only picked up this behavior in MySQL. Postgres, SQL server, and Oracle all seem to enforce the link out of the box. — Rabid Dog
  • 0
    If you use MyISAM the foreign key constraints will simply not exist any longer. These days I use InnoDB for everything, plus with the addition of row level locking compared to MyISAM which only has table level locking, its much better performance wise for my situation anyway. — Brian Wozeniak
add a comment
0