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:
- user
- group
- ugroup
Let's say they had the following columns and values:
Table user:
| uid |
|---|
| 1000 |
| 1001 |
Table group:
| gid |
|---|
| 1 |
| 2 |
Table ugroup:
| uid | gid |
|---|---|
| 1000 | 1 |
| 1001 | 2 |
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.
