Foreign Key constraints missing after phpmyadmin export

Question

I create a table in mysql using the following script:

  CREATE TABLE IF NOT EXISTS users_x_activities(
  id int NOT NULL auto_increment, 
  id_user int unsigned NOT NULL,
  id_attivita int unsigned NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (id_user) REFERENCES utente(id),
  FOREIGN KEY (id_attivita) REFERENCES attivita(id)
  ) ENGINE = INNODB;

When I export the created table from phpMyAdmin, I obtain the following script

CREATE TABLE IF NOT EXISTS `users_x_activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(10) unsigned NOT NULL,
  `id_attivita` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `id_user` (`id_user`),
  KEY `id_attivita` (`id_attivita`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

So the question are: where is my foreign key constraints? does KEY refer to FK? Seems that the two tables utente and attivita are no longer referenced in the new generated script. where am I doing wrong?

EDIT

In phpMyAdmin, configuring the export of the table I found the option "Display Foreign Key Relationship" If I flag this option I otain also this code in the script

--
-- RELATIONS FOR TABLE `users_x_activity`:
--   `id_user`
--       `utente` -> `id`
--   `id_attivita`
--       `attivita` -> `id`
--

--
-- Constraints for dumped tables
--

--
-- Constraints for table `users_x_activity`
--
ALTER TABLE `users_x_activity`
  ADD CONSTRAINT `users_x_activities_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `utente` (`id`),
  ADD CONSTRAINT `users_x_activities_ibfk_2` FOREIGN KEY (`id_attivita`) REFERENCES `attivita` (`id`);

This means that if I add the option "Display Foreign Key Relationship" I obtain also the FK constrains? in other case not?

Answer 1

So the question are: where is my foreign key constraints?

They are defined in the database. The output from SHOW CREATE TABLE users_x_activities will include the foreign key constraint definitions.

The definitions of the foreign key constraints likely appear in separate ALTER TABLE statements at the end of the generated script.

does KEY refer to FK?

No. KEY id_user (id_user) here refers to an index.

Seems that the two tables utente and attivita are no longer referenced in the new generated script.

Yes, you are correct. The foreign key constraints are not included in the CREATE TABLE statement.

where am I doing wrong?

A MySQL SHOW CREATE TABLE users_x_activities will include the foreign key constraints.

The foreign key constraints are likely included in the script generated by phpMyAdmin, but at the end of the script, in separate ALTER TABLE statements.

Answer 2

There are two type of constraints when you managing your tables with phpmyadmin:

• internal: when you set constraints with phpmyadmin designer for example the constraints stored as internal,that will not be included in export.
• innoDB: these constraints included in export check out linked video about it

Setting up a foreign key constraint