Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24282

Foreign key check should be more detailed

    XMLWordPrintable

    Details

      Description

      Hello!

      Sometimes working with Foreign Key could be complex to starters. The most common case that I see is when you try to FK a column with a different type.

      CREATE TABLE IF NOT EXISTS `countries` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
      );

      CREATE TABLE IF NOT EXISTS `country_states` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `country_id` int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`)
      );

      It is a good example. `id_country` is a INT, but `countries.id` is BIGINT. And when try to create a FK for it we will got:

      SQL Error (1005): Can't create table `country_states` (errno: 150 "Foreign key constraint is incorrectly formed")

      The error is correct, but is not clear about "what is wrong". My suggestion is update the description to some more... descriptive:

      SQL Error (1005): Can't create table `country_states` (errno: 150 "Foreign key constraint is incorrectly formed: country_states.country_id must be a BIGINT")

      However, I don't know if it is the role of the server to report something in more detail like this.

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            rentalhost David Rodrigues
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration