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:
Let's say they had the following columns and values:
gid would be foreign keys:
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
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
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.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.