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: