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

Foreign key cannot be used in the check constraint

    XMLWordPrintable

Details

    Description

      Hi

      I have following code snippet, where I use a foreign key in a check constraint. This code was working with version 10.10.2, but does not anymore with 10.10.3. I checked and it still works with the latest 10.3.38.

      DROP TABLE IF EXISTS `feedback`;
      		 
       
      		 
      CREATE TABLE IF NOT EXISTS `feedback` (
      		 
          `id` INT NOT NULL AUTO_INCREMENT,
      		 
          PRIMARY KEY (`id`));
      		 
         
      DROP TABLE IF EXISTS `message`;
      		 
       
      		 
      CREATE TABLE IF NOT EXISTS `message` (
      		 
      	`id` INT NOT NULL AUTO_INCREMENT,
      		 
      	`feedback_id` INT,
      		 
      	`value` INT,
      		 
      	PRIMARY KEY (`id`),
      		 
      	CONSTRAINT `fk_message_feedback`
      		 
      		FOREIGN KEY (`feedback_id`)
      		 
      			REFERENCES `feedback` (`id`)
      		 
      			ON UPDATE CASCADE,
      		 
      	CONSTRAINT `failing_constraint`
      		 
      		CHECK ((`value` >= 10 AND `feedback_id` IS NOT NULL)
      		 
      		OR (`value` <= 10 AND `feedback_id` IS NULL)));

      I get the error code 1901:

      Error Code: 1901. Function or expression 'feedback_id' cannot be used in the CHECK clause of `failing_constraint`

      Now my question is, if you have introduced a bug in your latest release or if that code snippet is something that should not be working, but was working so far unintentionally. I did not find an exact documentation if a foreign key should not be used in a check constraint.

      Attachments

        Issue Links

          duplicates

          New Feature - A new feature of the product, which has yet to be developed. MDEV-22880 Honor constraints on UPDATE CASCADE

          • Major - Major loss of function.
          • Open
          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22602 WITHOUT OVERLAPS constraint is ignored when update is performed via foreign key cascade

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-30674 Implement CHECK constraints validation on cascading updates

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31322 FKs with 'ON UPDATE CASCADE' can be set in some contexts but not others

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31769 Adding a check constraint in "create table" doesn't work if it uses a foreign key with "update cascade"

          • Major - Major loss of function.
          • Closed

          Activity

            People

              serg Sergei Golubchik
              Vulin Nikola
              Votes:
              2 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.