Alter Table Error 1064
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or error 1064 mysql 42000 create table posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join error 1064 mysqldump the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes sql error 1064 a minute: Sign up Alter Table gives error #1064 [duplicate] up vote 0 down vote favorite This question already has an answer here: Syntax error due to using a reserved word as a table or column name in MySQL 1 answer error 1064 sqlstate 42000 I am trying to create two tables in phpmyadmin: Users and Keys, the schema is as follows: Users: id int auto_incerement primary key name varchar(50) not null Keys: user_id int keys varchar(50) not null Now I am running the following query to make the user_id in Keys a foreign key referencing the id in Users ALTER TABLE Keys FOREIGN KEY(user_id) REFERENCES Users(id) ON UPDATE CASCADE ON DELETE CASCADE But after executing, i am getting the following error: #1064 - You have
Error 1064 Sqlstate 42000 (er_parse_error)
an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Keys FOREIGN KEY(user_id) REFERENCES Users(id) ON UPDATE CASCADE ON DELETE CA' at line 1 Can anyone tell me what am I doing wrong? Thanks in advance. php mysql phpmyadmin share|improve this question asked Oct 8 '13 at 14:12 R4chi7 458420 marked as duplicate by Amal Murali, hakre, Alma Do, Sergiu Paraschiv, Todd Ditchendorf May 5 '14 at 13:52 This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question. Just throwing it out there...you say you're using phpMyAdmin, but you're working with SQL statements. Everything you're doing above can be done graphically from phpMyAdmin (which does properly escape table names and reserved words and so on. There's nothing wrong with typing the SQL statement, but it's also easy to do these things graphically. phpMyAdmin has several tools to work with relations (Relation View, Designer, etc). –Isaac Bennetch Oct 13 '13 at 5:05 i know it is easy to use the GUI, but where's the fun in that? ;) –R4chi7 Oct 20 '13 at 6:18 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote accepted Keys is a reserved word and must be encased in backticks: ALTER TABLE `Keys` ADD FOREIGN KEY (user_id) REFERENCES `Users` (`id`) ON UPDATE
here for a quick overview of the site Help Center Detailed answers to any questions you might
Error 1064 You Have An Error In Your Sql Syntax
have Meta Discuss the workings and policies of this site About error 1064 mysql workbench Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads error 1064 mysql 42000 insert with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, http://stackoverflow.com/questions/19250116/alter-table-gives-error-1064 just like you, helping each other. Join them; it only takes a minute: Sign up SYNTAX HELP!! SQL Alter Table Alter Column varchar(30)->varcahar(100) up vote 3 down vote favorite 2 I am getting a super n00b error and need syntax help. I have a table that describes like this: mysql> describe easy_table; +---------------------+--------------+------+-----+---------+----------------+ | Field http://stackoverflow.com/questions/6133889/syntax-help-sql-alter-table-alter-column-varchar30-varcahar100 | Type | Null | Key | Default | Extra | +---------------------+--------------+------+-----+---------+----------------+ | id | bigint(20) | NO | PRI | NULL | auto_increment | | version | bigint(20) | NO | | NULL | | | account_id | bigint(20) | NO | MUL | NULL | | | city | varchar(30) | NO | | NULL | | ... | name | varchar(255) | YES | | NULL | | | name_two | varchar(255) | YES | | NULL | | +---------------------+--------------+------+-----+---------+----------------+ 13 rows in set (0.03 sec) I'm trying to make the city varchar bigger to varchar(100) and this line doesn't work alter table easy_table alter column city varchar(100); this also doesn't work alter table easy_table alter column city varchar(100) not null; I get this error: ERROR 1064 (42000): 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 'varchar(100)' at line 1 mysql sql mysql-error-1064 share|improve th
help? Post your question and get tips & solutions from a community of 418,418 IT Pros & Developers. It's quick & easy. https://bytes.com/topic/mysql/answers/646181-how-alter-change-column-alter-table How to alter or change column with ALTER TABLE P: 3 terenceyuen I would like to change the type of Null to NO ,what is the command for this? I've tried this one, but no luck to get it work. ALTER TABLE claims_summary ALTER COLUMN advance_call_details SET NOT NULL; Thanks. Terence +----------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra error 1064 | +----------------------+--------------+------+-----+---------+-------+ | client_id | int(11) | | | 0 | | | client_code | varchar(30) | | PRI | | | | club_id | int(11) | | | 0 | | | club_code | varchar(30) | | PRI | | | | yearclaim | int(10) | | PRI | 0 | | | advance_call | double(16,4) | YES | | 0.0000 | | error 1064 mysql | additional_call | double(16,4) | YES | | 0.0000 | | | supplementary_call | double(16,4) | YES | | 0.0000 | | | release_call | double(16,4) | YES | | 0.0000 | | | advance_call_details | varchar(240) | YES | | | | +----------------------+--------------+------+-----+---------+-------+ May 14 '07 #1 Post Reply Share this Question 6 Replies P: 10 tashy Try this ALTER TABLE table name ALTER COLUMN column name data type NOT NULL By data type ,it could either be int,bit,etc May 14 '07 #2 reply Expert 5K+ P: 5,821 pbmods When you change or alter a column, you are basically redefining it, so you have to provide a full definition of the column, not just the attributes you want to change (otherwise, how would MySQL know if you wanted to remove an attribute from a column?). P.S., Please surround your mysql output with CODE tags to make it easier to read next time. Thanks. May 15 '07 #3 reply P: 3 terenceyuen Thanks a lot , I'v tried, but it return the following errors at the end. Is it possible to define the datetype : varchar(240) ? mysql> ALTER TABLE