adding foreign key to existing table

[jobs1109]

Hi everyone,

 

I am trying to add a foreign key (jointable) to an existing table called (Jobs) and I would like it to

reference the id in users table. Also I like to have it auto update when records added and deleted. ENGINE=InnoDB  When I run the following code I get an error message.

 

 

 

ALTER TABLE Jobs ADD FOREIGN KEY
  (jointable) REFERENCES users (id);

 

 

here is the error message

 

 

Error

 

SQL query:

 

ALTERTABLE Jobs ADDFOREIGNKEY ( jointable )REFERENCES users( id )

 

MySQL said:

#1072 - Key column 'jointable' doesn't exist in table

 

 

What am I doing wrong ? please help

 

[Adam]

Remove the brackets around "jointable". To have it auto update you need to add to the end:

 

ON DELETE CASCADE
ON UPDATE CASCADE

[jobs1109]

Hi when I remove the brackets I get the followin message.

 

 

Here is the code

 

ALTER TABLE Jobs ADD FOREIGN KEY  jointable REFERENCES users (id);

 

 

here is the error message

 

 

Error

 

SQL query:

 

ALTERTABLE Jobs ADDFOREIGNKEY jointable REFERENCES users( id )

 

MySQL said:

#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 users (id)' at line 1

 

[jobs1109]

both users TABLE and Jobs TABLE =InnoDB

[Adam]

Sorry, bit rusty. You need:

 

[...] jointable(id) REFERENCES users(id) [...]