0

i have an syntax error in my sql it has to do with the use of reserved names(user security). i cant seem to figure out how to put the quotes can someone please help me fix this. 

ALTER TABLE `user` ADD FOREIGN KEY `security_check_id` REFERENCES `security_check`(security_check_id)

#1064 - Er is iets fout in de gebruikte syntax bij 'REFERENCES security_check(security_check_id)' in regel 1

Translated sorry for the bad translation

#1064 - There is an mistake in the used syntax at 'REFERENCES security_check(security_check_id)' at line 1

james
  • 3
  • 2
  • 2
    The obvious solution would be to avoid using reserved names for any of your own objects. Then there's never any confusion. I'm sure you can think of a slightly different name fairly easily. – ADyson Jun 10 '20 at 11:36
  • 2
    P.S. Is this MySQL, or something else? The way you add quotes etc is different between different database engines. Please add the relevant tag. – ADyson Jun 10 '20 at 11:36
  • for use reserve names in sqlserver you can use [] .ex:[security] – Amin Golmahalleh Jun 10 '20 at 11:51

2 Answers2

1

Pease check this answer, it might be helpful -

Selecting a column that is also a keyword in MySQL

In your case try - 

ALTER TABLE `user` ADD FOREIGN KEY (`security_id`) REFERENCES `security`(`security_id`)
ManuAc
  • 506
  • 4
  • 11
  • It doesn't seem to work: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=946f293a4e54911ae0fb7b972f4442b3 - can you make a working one? The documentation for REFERENCES in MySQL shows specifically the brackets must be used, anyway. Plus we don't know 100% for certain that this is definitely MySQL, although it seems likely. – ADyson Jun 10 '20 at 11:47
  • sorry it is my sql – james Jun 10 '20 at 11:50
  • @james in that case please edit your question to show that (you can add a tag, and update the description). Also, it would help a lot if you tell us the exact error message you are seeing. – ADyson Jun 10 '20 at 11:51
  • hey @james, you were missing ADD. I have updated the SQL and it's working. Try with the updated query! – ManuAc Jun 10 '20 at 11:55
1

ADD FOREIGN KEY security_check_id is incorrect if security_check_id is a column you wish to reference then it must be enclosed in parenthesis ie ADD FOREIGN KEY (security_check_id) if security_check_id is a name you intend to give the FK then it should be followed by the column in parenthesis that you wish to check..

BTW none of the column or table names are reserved words so they don't need to be escaped

P.Salmon
  • 17,104
  • 2
  • 12
  • 19