Syntax error when adding foreign key constraint

Question

I am trying to set a DEFAULT value and NOT NULL for type_id column which is a foreign key to instance table but it is complaining about MySQL syntax.

CREATE TABLE `type` (
  `id` int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `city` varchar(256)
); 

ALTER TABLE instance ADD COLUMN type_id int(10) unsigned NOT NULL;

ALTER TABLE instance ADD CONSTRAINT instance_xbf1 DEFAULT 1
FOREIGN KEY (type_id)
REFERENCES type(id) int(10) unsigned NOT NULL;

It is complaining about the syntax of the last statement:

Error: \"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 'DEFAULT 1\nFOREIGN KEY (type_id)\nREFERENCES type(id) int(10) unsi' at line 1\")", "stdout": "", "stdout_lines": [], "warnings": []}

it is complaining about mysql syntax Those complaints contain very specific messages that tell you exactly what the error is. — Matt Clark
– Matt Clark, Commented Apr 13, 2017 at 18:43

davmos · Accepted Answer · 2017-04-14 08:46:18Z

You're trying to do too many things in your ADD CONSTRAINT statement for the foreign key.

You can add the default value in two ways. Either as part of the column definition:

ALTER TABLE instance ADD COLUMN type_id int(10) unsigned NOT NULL DEFAULT 1;

Or later in it's own statement:

ALTER TABLE instance ALTER type_id SET DEFAULT 1;

In the foreign key you cannot define the properties of the column such as data type, nullability, default values etc...

The error message is telling you it's not expecting the DEFAULT keyword as part of an ADD CONSTRAINT. If you remove that, it will then complain about the data type & nullability part: int(10) unsigned NOT NULL because it's not expecting those either. But you have already defined them anyway.

So, your ADD CONSTRAINT can be simplified to:

ALTER TABLE instance ADD CONSTRAINT instance_xbf1
FOREIGN KEY (type_id) REFERENCES type(id);

Collectives™ on Stack Overflow

Syntax error when adding foreign key constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related