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

User, date Message
Written by WaveLength
3 years ago
Category: General
11 posts since Thu, 07 Jul 11
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.
Written by ansgar
3 years ago
4783 posts since Fri, 07 Apr 06
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
Written by WaveLength
3 years ago
11 posts since Thu, 07 Jul 11
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
Written by ansgar
3 years ago
4783 posts since Fri, 07 Apr 06
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.
Written by WaveLength
3 years ago
11 posts since Thu, 07 Jul 11
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
---------------------------
Written by ansgar
3 years ago
4783 posts since Fri, 07 Apr 06
Hm, looks like you did not follow the above recipe. "Columns", "Reference table" and "foreign columns" values are all missing.
Written by WaveLength
3 years ago
11 posts since Thu, 07 Jul 11
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.
Written by ansgar
3 years ago
4783 posts since Fri, 07 Apr 06
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.
Written by WaveLength
3 years ago
11 posts since Thu, 07 Jul 11
Okay, I got it. That's actually very cool. Thanks.
 

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