我可以添加一个检查约束到一个孩子在MySQL检查其父母的价值？ [英] Can I add a check constraint to a child checking the value of its parent in MySQL?

问题描述

  + ----------- ----------- + + ------------------------ + 
 |人物| |电话| 
 + ---------------------- + + --------------------- --- + 
 | id int + ----- + | id int | 
 | name varchar（100）| + ----> + person_id int | 
 |允许tinyint（1）| |数字int | 
 + ---------------------- + + --------------------- --- + 
  

一个人可以拥有多少个他想要的电话，但是他必须被允许它（允许> 0）。

因此，我使用

 <$ c创建了两个表codeREATE TABLE`phones`（
` id` int（11）NOT NULL AUTO_INCREMENT，
`persons_id` int（11）DEFAULT NULL，$ b $``phonenumber` int（5）DEFAULT NULL，
 PRIMARY KEY（`id`），
 KEY`phones_ibfk_1`（`persons_id`），
 CONSTRAINT`phones_ibfk_1` FOREIGN KEY（`persons_id`）REFERENCES`persons`（`id `）在DELETE CASCADE 
）ENGINE = InnoDB DEFAULT CHARSET = utf8`
  

但是这不检查我想要的。我想知道是否有可能使查询像 ALTER TABLE phones添加约束chk_person CHECK（persons.allowed> 0）。这可能吗？还有另外一种方法吗？
解决方案

检查约束不起作用一个解决方案是在
插入前创建一个触发器你检查值，如果你发现一个插入到同一个表中，会导致一个erreur

  DELIMITER || 
在插入手机之前创建触发器trigger_check FOR EACH ROW 
 begin 
 DECLARE is_allowed boolean; 
选择允许进入@is_allowed来自其中id = new.person_id; 
 if @is_allowed< 1 then 
 insert into phones values（''，''，''，''）; #make错误不metter 
结束如果; 
结束|| 
 DELIMITER; 
  

这就是我们现在要做的，希望检查约束在新版本中工作。 b $ b

I have a similar database with two tables as follow:

+----------------------+           +------------------------+
|Persons               |           |phones                  |
+----------------------+           +------------------------+
|id       int          +-----+     |id          int         |
|name     varchar(100) |     +---->+person_id   int         |
|allowed  tinyint(1)   |           |number      int         |
+----------------------+           +------------------------+

One person could have as many phones as he wants but he has to be allowed to it (allowed > 0).

So, I created both tables using

CREATE TABLE `phones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `persons_id` int(11) DEFAULT NULL,
  `phonenumber` int(5) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `phones_ibfk_1` (`persons_id`),
  CONSTRAINT `phones_ibfk_1` FOREIGN KEY (`persons_id`) REFERENCES `persons` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8` 

But this doesn't check what I want. What I want to know if it is possible make a query like ALTER TABLE phones ADD CONSTRAINT chk_person CHECK (persons.allowed > 0). Is this possible? There's another alternative?

解决方案

the check constraint doesn't work a solution is to create a trigger before insert so you check the values if you find something wron do an insert into the same table that will cause a erreur in

DELIMITER ||
CREATE TRIGGER  trigger_check  before insert ON phones  FOR EACH ROW 
begin 
DECLARE is_allowed boolean;
select allowed into @is_allowed from Persons where id = new.person_id;
if @is_allowed <1 then 
  insert into phones values ('','','','');  #make erreur doesn't metter
  end if ;
end ||
DELIMITER ;

that's how we do it for now hope that check constraint work in the new versions

这篇关于我可以添加一个检查约束到一个孩子在MySQL检查其父母的价值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！