Rectangle 27 0

mysql Add Foreign Key to existing table?


@frgtv10 the most likely the tables data conflicts with "on delete".

I created index for both. When I try to run i get the same error message as before. When I disclaim the "on delete on update" part it works but with the wrong "on delete" action ;)

When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

that was the problem. i used set null, but field is "null". thanks.

Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) references grades(id);
alter table users add grade_id smallint unsigned not null default 0;

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


@frgtv10 the most likely the tables data conflicts with "on delete".

I created index for both. When I try to run i get the same error message as before. When I disclaim the "on delete on update" part it works but with the wrong "on delete" action ;)

When you add a foreign key constraint to a table using ALTER TABLE, remember to create the required indexes first.

that was the problem. i used set null, but field is "null". thanks.

Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


ALTER TABLE users ADD grade_id SMALLINT UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE users ADD CONSTRAINT fk_grade_id FOREIGN KEY (grade_id) REFERENCES grades(id);

@PixMach, the answer is no. You can have signed integers as foreign keys. As N.B. noted on the question, the type and sign of the fields need to match. So if your primary key in the lookup table is UNSIGNED, then the foreign key field must also be UNSIGNED. If the primary key field is SIGNED, then foreign key field must also be signed. Think of it this way: whatever the column in the one table is defined as in a SHOW CREATE TABLE, it needs to have the same definition in the other table.

And do not forget to derive your index column on your refrence table, if you re using phpmyadmin mysql as MariaDb as database storege i guess it work for oracle mysql as well

I had to run "SET FOREIGN_KEY_CHECKS=0;" before running the ADD CONSTRAINT command or SQL would complain "Cannot add or update a child row: a foreign key constraint fails".

Note that this could also be done with a single query (might be better in case of failure etc)

Reasons help me understand and remember. This is because you can't add a foreign key to an non-unsigned field, correct?

To add a foreign key (grade_id) to an existing table (users), follow the following steps:

Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


ALTER TABLE katalog ADD FOREIGN KEY (`Sprache`) REFERENCES Sprache(`ID`);

Simply use this query, I have tried it as per my scenario and it works well

Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


ALTER TABLE t_name1 ADD FOREIGN KEY (column_name) REFERENCES t_name2(column_name)
Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


CREATE TABLE `katalog` (
`Sprache` int(11) NOT NULL,
FOREIGN KEY (`Sprache`)
REFERENCES `Sprache` (`ID`)
ON DELETE SET NULL
ON UPDATE SET NULL;

It cant set the column Sprache to NULL because it is defined as NOT NULL.

Note
Rectangle 27 0

mysql Add Foreign Key to existing table?


  • Are you trying to set the table PK to NULL on update (#5 in the list)?
  • Is your foreign key index a unique name in the whole database (#3 in the list)?

Edit: I missed the comments on your original post. Unsigned/not unsigned int columns maybe resolved your case. Hope my link helps someone in the future thought.

I'm guessing the problem is with the set NULL on update (if my brains aren't on backwards today as they so often are...).

On the top of my head two things come to mind.

Note