如何在mysql加上一个约束 如何在mysql加上一个约束现在有一个表id pid1 12 13 null4 2这里pid只能是id的值或者为空如何在mysql用约束实现 (oracle sqlserver 是比较简单实现的 ) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 在标准的SQL语言中,我们可以在(CREATE TABLE a(a int, b int, check(b=a or b=null)) 如下即可。mysql> create table t_liyihongcug( -> id int not null primary key, -> pid int , -> CONSTRAINT fk_t_liyihongcug_t_liyihongcug FOREIGN KEY (pid) REFERENCES t_liyihongcug (id) -> );Query OK, 0 rows affected (0.11 sec)mysql> show create table t_liyihongcug;+---------------+---------------------------------------------------------------| Table | Create Table+---------------+---------------------------------------------------------------| t_liyihongcug | CREATE TABLE `t_liyihongcug` ( `id` int(11) NOT NULL, `pid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_t_liyihongcug_t_liyihongcug` (`pid`), CONSTRAINT `fk_t_liyihongcug_t_liyihongcug` FOREIGN KEY (`pid`) REFERENCES `t_liyihongcug` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 |+---------------+---------------------------------------------------------------1 row in set (0.00 sec)mysql> insert into t_liyihongcug values (1,null);Query OK, 1 row affected (0.06 sec)mysql> insert into t_liyihongcug values (2,3);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`t_liyihongcug`, CONSTRAINT `fk_t_liyihongcug_t_liyihongcug` FOREIGN KEY (`pid`) REFERENCES `t_liyihongcug` (`id`))mysql> insert into t_liyihongcug values (2,1);Query OK, 1 row affected (0.06 sec)mysql> select * from t_liyihongcug;+----+------+| id | pid |+----+------+| 1 | NULL || 2 | 1 |+----+------+2 rows in set (0.00 sec)mysql> 不行。执行上面的sql, 输入如下dataa b1 null2 13 1 是对的 但修改的时候 a b1 null2 13 31发现系统居然不报错, 难道只能用触发器?? 数据库缺少MYI和MYD文件,只有frm文件能修复吗? 为什么mysql没有innodb引擘? 用虚拟主机上传数据的时候报1064错 MySQL的语句中有没有类似for/while那样的循环语句或者类似的实现方法? check约束好像不起作用啊?! 请教如何在insert 时使用自定义变量 昏头了~~问个问题~~ 求助达人,如何查询最近的数据? mysql 的账号 root@::1是什么意思,不懂? 【求进】MySQL 5.7+如何Where查找JSON数组内对象的值 复杂分组语句--mysql 编写 如何才能找到这条记录 where enddate is null
SQL
语言中,我们可以在(
CREATE TABLE a(a int, b int, check(b=a or b=null))
-> id int not null primary key,
-> pid int ,
-> CONSTRAINT fk_t_liyihongcug_t_liyihongcug FOREIGN KEY (pid) REFERENCES t_liyihongcug (id)
-> );
Query OK, 0 rows affected (0.11 sec)mysql> show create table t_liyihongcug;
+---------------+---------------------------------------------------------------
| Table | Create Table
+---------------+---------------------------------------------------------------
| t_liyihongcug | CREATE TABLE `t_liyihongcug` (
`id` int(11) NOT NULL,
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_t_liyihongcug_t_liyihongcug` (`pid`),
CONSTRAINT `fk_t_liyihongcug_t_liyihongcug` FOREIGN KEY (`pid`) REFERENCES `t_
liyihongcug` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------------+---------------------------------------------------------------
1 row in set (0.00 sec)mysql> insert into t_liyihongcug values (1,null);
Query OK, 1 row affected (0.06 sec)mysql> insert into t_liyihongcug values (2,3);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`t_liyihongcug`, CONSTRAINT `fk_t_liyihongcug_t_liyihongcug` FOREIG
N KEY (`pid`) REFERENCES `t_liyihongcug` (`id`))
mysql> insert into t_liyihongcug values (2,1);
Query OK, 1 row affected (0.06 sec)mysql> select * from t_liyihongcug;
+----+------+
| id | pid |
+----+------+
| 1 | NULL |
| 2 | 1 |
+----+------+
2 rows in set (0.00 sec)mysql>
执行上面的sql, 输入如下data
a b
1 null
2 1
3 1 是对的
但修改的时候
a b
1 null
2 1
3 31
发现系统居然不报错, 难道只能用触发器??