CODEWITHSUNDEEP
mysqlforeign-keys
Igor
Igor

Reputation: 6285

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

Upvotes: 1

Views: 36

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

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

Upvotes: 2

Related Questions