CODEWITHSUNDEEP
mysqlforeign-keys
Griff
Griff

Reputation: 1747

Foreign keys with mysql is it possible to have a foreign key based on value of column?

I have this database structure,

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

CREATE TABLE IF NOT EXISTS `announces` (
   `tracker` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `failCount` smallint(5) unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (`tracker`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `hashes` (
   `hash` binary(20) NOT NULL,
   `indexed` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `trackers` (
   `hash` binary(20) NOT NULL,
   `tracker` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
   `seeders` mediumint(8) unsigned NOT NULL DEFAULT '0',
   `leechers` mediumint(8) unsigned NOT NULL DEFAULT '0',
   KEY `hashFK` (`hash`),
   KEY `trackerFK` (`tracker`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `trackers`
   ADD CONSTRAINT `hashFK` FOREIGN KEY (`hash`) REFERENCES `hashes` (`hash`)
   ON DELETE CASCADE,
   ADD CONSTRAINT `trackerFK` FOREIGN KEY (`tracker`) REFERENCES `announces` (`tracker`) 
   ON DELETE CASCADE;

The table trackers has 2 foreign keys one for trackers, and one for hashes.

This I think makes sure that a hash cannot be in trackers without first being in hashes. And if a hash is deleted from hashes then it also gets deleted from trackers.

Same for trackers, a tracker must exist in announces before it can exist in trackers, and if it gets deleted from announces it also gets deleted from trackers.

The problem I have is that a hash should only exist in trackers if the corresponding value of indexed in the hashes row is == 1. Is this possible?

Upvotes: 1

Views: 654

Answers (1)

Sebas
Sebas

Reputation: 21542

If what you wish is to raise an error whenever a tracker is inserted with a fk to a hash with indexed != 1, then it has to be implemented at the applicative layer.

Various options:

  • favorite coding language checks (subsequent queries to check values before insertions)
  • using triggers, before insert into tracker, check that the corresponding hash is indexed, and if not cancel/raise error

cheers

Upvotes: 1

Related Questions