mySQL CREATING FOREIGN KEY

Question

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|
+-----------+-----------------------------------------------------------------------------------------------------+-----------+------------+-----------+-----------+-----------+-------------+
+-----------+-----------------------------------------------------------------------------------------------------+-----------+------------+-----------+-----------+-----------+-------------+

What's the other table look like? Both tables.

johnny
– johnny

2016-07-30 16:37:55 +00:00
Commented Jul 30, 2016 at 16:37 — johnny
– johnny, Commented Jul 30, 2016 at 16:37

ScaisEdge · Accepted Answer · 2016-07-30 16:39:41Z

2

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);

answered Jul 30, 2016 at 16:39

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Igor Over a year ago

Thank you. Apparently I was missing the parentesis.

Collectives™ on Stack Overflow

mySQL CREATING FOREIGN KEY

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related