distal-attribute
distal-attribute
distal-attribute
distal-attribute

How to create a foreign key - for referential integrity - in the foreign keys tab using the Add button

WaveLength posted 3 years ago in General
How do you create a foreign key in the foreign keys tab? There is an Add button there, but I don't know how to use it.

My tables use Innodb. The particular table in question has two columns. They both contain integers that are identical to the primary keys in two other tables. So referential integrity is established, but I want to enforce it.

Does anyone know the exact procedure for using the Add button in the foreign keys tab to create foreign keys? Can I apply foreign keys to the existing columns?

Thanks.
ansgar posted 3 years ago
Just set values in all fields:
- click "add"
- give it a name or use the default one ("FK1")
- select column name(s) in current table
- select reference table
- select columns from reference table
- set the "on update" and "on delete" behaviour
WaveLength posted 3 years ago
Thank you for your advice. I followed your instructions, but after "select column name in current table" Heidi asks me to save the table, and when doing so, I get an error message 1064. It is something like "right syntax to use near ') REFERENCES' '()'". I can't tell exactly what the error message is because the font is small, and I cannot select it with my mouse to paste it in a larger font.

Is there a bug in the software? Do you have any idea what the problem might be?

My system: XP SP3, XAMPP
ansgar posted 3 years ago
The font is small? That's a normal error dialog, man. please copy its contents via Ctrl+C and paste it here, so I can see what's exactly going wrong.
WaveLength posted 3 years ago
Okay, I didn't know you could do that. Here's the error:
---------------------------
Error
---------------------------
SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') REFERENCES `` ()' at line 2
---------------------------
OK
---------------------------
ansgar posted 3 years ago
Hm, looks like you did not follow the above recipe. "Columns", "Reference table" and "foreign columns" values are all missing.
WaveLength posted 3 years ago
To clarify, here are the steps I followed:

1. I clicked on a table in the left pane, then selected the Table tab and the Foreign Keys tab.
2. I clicked the green Add button.
3. I clicked to the left of column 6, which contains integers only. This turns the column blue (a horizontal blue bar).
4. I clicked the table containing the foreign key, and a Save Modified Table requester box appears.
5. I clicked Yes in the requester to save, and I get the error. The modified version of the table is not saved.

That is the exact procedure I followed every time.

Do you have any suggestions?

Thanks.
ansgar posted 3 years ago
Between 2. and 3. you should have entered something in the remaining columns, man. What's so complicated here, just look at the column headers please.
WaveLength posted 3 years ago
Okay, I got it. That's actually very cool. Thanks.

Please login to leave a reply, or register at first.