[jcao75]

I have the below schema for defining a foreign key relationship. When
using DB Explorer I do not see the foreign key in Table Info or in the
generated ER Diagram. I am using MySQL.

CREATE TABLE security_user_to_role (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
role_id INT UNSIGNED NOT NULL,
CONSTRAINT security_userrole_pk
PRIMARY KEY NONCLUSTERED (id),
CONSTRAINT security_userrole_uk
UNIQUE KEY NONCLUSTERED (user_id,role_id),
CONSTRAINT security_userrole_user_fk
FOREIGN KEY (user_id)
REFERENCES security_user(id),
CONSTRAINT security_userrole_role_fk
FOREIGN KEY (role_id)
REFERENCES role(id)
);

[Brian Fernandes]

Jcao,

For the FK to be stored in MySQL, you must specify a storage engine.

Try appending “type = InnoDB” to your DDL.

i.e. change

REFERENCES role(id)
);

to

REFERENCES role(id)
) type = InnoDB;

Best,
Brian.

[Author]

Posts