数据库约束问题
问题补充:
现在有个表
create table A(
Id int identity(100,1) primary key,
Name varchar(32) not null,
ParentId int
)
现在字段PARENTID想要加个约束 要求只能选择ID内已存在的值 问语句怎么写
[解决办法]
使用外键约束即可。
mysql> create table A( -> Id int auto_increment primary key, -> Name varchar(32) not null, -> ParentId int, -> FOREIGN KEY (ParentId) references a(id) -> ) ENGINE=innodb;Query OK, 0 rows affected (0.07 sec)mysql> insert into a values (null,'AAAA',null);Query OK, 1 row affected (0.07 sec)mysql> insert into a values (null,'BBBB',3);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`csdn`.`a`, CONSTRAINT `a_ibfk_1` FOREIGN KEY (`ParentId`) REFERENCES `a`(`Id`))mysql> insert into a values (null,'BBBB',1);Query OK, 1 row affected (0.11 sec)mysql>