[MDEV-30606] Foreign key cannot be used in the check constraint Created: 2023-02-07  Updated: 2023-11-06  Resolved: 2023-02-17

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.6.12, 10.10.3
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Nikola Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 2
Labels: bug, error, innodb
Environment:

Using your docker image

Issue Links:
Duplicate
duplicates MDEV-22880 Honor constraints on UPDATE CASCADE Open
Problem/Incident
is caused by MDEV-22602 WITHOUT OVERLAPS constraint is ignore... Closed
Relates
relates to MDEV-30674 Implement CHECK constraints validatio... Closed
relates to MDEV-31322 FKs with 'ON UPDATE CASCADE' can be s... Closed
relates to MDEV-31769 Adding a check constraint in "create ... Closed

 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.

 Comments   
Comment by Pieter Nys [ 2023-02-17 ]

We are experiencing the same bug in our test-pipeline. When using version 10.6.11, we are able to load in our test-database. Using 10.6.12, this is no longer possible.

The specific section of our database-load-script:

DROP TABLE IF EXISTS `userfield_values`;
 
/*!40101 SET @saved_cs_client     = @@character_set_client */;
 
/*!40101 SET character_set_client = utf8 */;
 
CREATE TABLE `userfield_values` (
 
  `value_id` int(11) NOT NULL AUTO_INCREMENT,
 
  `field_id` int(11) NOT NULL,
 
  `party_id` int(11) DEFAULT NULL,
 
  `case_id` int(11) DEFAULT NULL,
 
  `case_template_id` int(11) DEFAULT NULL,
 
  `office_id` int(11) DEFAULT NULL,
 
  `user_id` int(11) DEFAULT NULL,
 
  `text` text DEFAULT NULL,
 
  `number` int(11) DEFAULT NULL,
 
  `dateandtime` datetime DEFAULT NULL,
 
  PRIMARY KEY (`value_id`),
 
  KEY `field_id` (`field_id`),
 
  KEY `case_id` (`case_id`),
 
  KEY `party_id` (`party_id`),
 
  KEY `FK_USERFIELD_VALUES_OFFICE_ID` (`office_id`),
 
  KEY `FK_USERFIELD_VALUES_USER_ID` (`user_id`),
 
  KEY `FK_USERFIELD_VALUES_CASE_TEMPLATE_ID` (`case_template_id`),
 
  CONSTRAINT `FK_USERFIELD_VALUES_CASE_TEMPLATE_ID` FOREIGN KEY (`case_template_id`) REFERENCES `case_templates` (`case_template_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `FK_USERFIELD_VALUES_OFFICE_ID` FOREIGN KEY (`office_id`) REFERENCES `offices` (`office_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `FK_USERFIELD_VALUES_USER_ID` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `userfield_values_ibfk_1` FOREIGN KEY (`field_id`) REFERENCES `userfield_names` (`field_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `userfield_values_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`party_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `userfield_values_ibfk_3` FOREIGN KEY (`case_id`) REFERENCES `cases` (`case_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 
  CONSTRAINT `only_one_link` CHECK (`party_id` is not null and `case_id` is null and `case_template_id` is null and `user_id` is null and `office_id` is null or `case_id` is not null and `party_id` is null and `case_template_id` is null and `user_id` is null and `office_id` is null or `case_template_id` is not null and `party_id` is null and `case_id` is null and `user_id` is null and `office_id` is null or `user_id` is not null and `party_id` is null and `case_id` is null and `case_template_id` is null and `office_id` is null or `office_id` is not null and `party_id` is null and `case_id` is null and `case_template_id` is null and `user_id` is null)
 
) ENGINE=InnoDB AUTO_INCREMENT=819 DEFAULT CHARSET=utf8mb4;

Our error-message: 

General error: 1901 Function or expression 'party_id' cannot be used in the CHECK clause of `only_one_link`

Comment by Sergei Golubchik [ 2023-02-17 ]

This was an intentional change. If a field is a part of a foreign key with a ON UPDATE CASCADE action, it can be updated internally by InnoDB without informing the server and without validating the CHECK constraint. In other words, such cascading updates can invalidate the constraint and that's why they are not supported.

It is possible to implement CHECK constrain validation on cascading updates, but this would've been too intrusive and risky change in a 10.6 GA branch

Comment by Valerii Kravchuk [ 2023-02-17 ]

I wonder what MDEV introduced this change and why it is not mentioned in the release notes in any visible way?

Comment by Sergei Golubchik [ 2023-02-17 ]

it's linked as "is caused by", but here it is again: MDEV-22602

Not listed in release notes because they only list major changes and this one was considered a minor one. Perhaps incorrectly. I can add it if you think it's worth it

Comment by Valerii Kravchuk [ 2023-02-17 ]

But MDEV-22602 says it's fixed years ago, in 10.5.4. How it could cause a change of behaviour in 10.6.12 vs 10.6.11? Do I miss something here?

I'd appreciate more detailed information for the release notes, now that we have several users complaining.

Comment by Sergei Golubchik [ 2023-05-22 ]

This change in behavior was caused by the commit https://github.com/MariaDB/server/commit/a5eff044cb85 (in 10.6.12, indeed)

When working on something else I've noticed that MDEV-22602 didn't cover the case of named CHECK constraints and casually fixed that.

Comment by Tim Koop [ 2023-07-25 ]

We were bitten by this change too. We would have appreciated reading about it in the release notes.

Comment by Sergei Golubchik [ 2023-11-06 ]

This limitation will be removed in MDEV-22880

Generated at Thu Feb 08 10:17:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.