Igor Igor - 4 months ago 18
MySQL Question

mySQL CREATING FOREIGN KEY

ALL,

igor@IgorDellGentoo ~ $ isql myodbc-5.2-test root wasqra
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> use draft;
SQLRowCount returns 0
SQL> ALTER TABLE owners ADD FOREIGN KEY id REFERENCES leagues(id);
[ISQL]ERROR: Could not SQLExecute
SQL>


What am I doing wrong?

Also, for some reason I can't create a foreign key thru the mySQL-Workbench when creating the table.

There is no "Add" button or "+" sign to add this constraint. And there is no reaction on the right click.

Could someone please point me to the right direction?
I have Workbench version 6.3.4.0 on Gentoo Linux.

SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_draft |
+-----------------------------------------------------------------+
| leagues |
| owners |
+-----------------------------------------------------------------+
SQLRowCount returns 2
2 rows fetched
SQL> SELECT * FROM leagues;
+-----------+-----------------------------------------------------------------------------------------------------+-----------+------------+-----------+-----------+-----------+-------------+
| id | name | drafttype | scoringtype| roundvalue| leaguetype| salary | benchplayers|
+-----------+-----------------------------------------------------------------------------------------------------+-----------+------------+-----------+-----------+-----------+-------------+
+-----------+-----------------------------------------------------------------------------------------------------+-----------+------------+-----------+-----------+-----------+-------------+

Answer

seems you are using id as a foreign key use proper column instead

ALTER TABLE owners 
ADD COLUMN FOREIGNID INT NOT NULL;

 ALTER TABLE owners 
 ADD FOREIGN KEY (FOREIGNID) REFERENCES leagues(ID);
Comments