問(wèn)題描述

我寫(xiě)了一句SQL如下：

create table if not exists Teachings( teacher varchar(255), classid varchar(32) references Class(id) check(teacher in (select username from Users where role = ’teacher’)));

但是在執(zhí)行的時(shí)候報(bào)錯(cuò)(MySQL版本5.6.30)：

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’check(teacher in (select username from Users where role = ’teacher’)))’ at line 4

是不是MySQL中的check約束不能包含子查詢(xún)呢？如果是，我的這句SQL應(yīng)該怎么實(shí)現(xiàn)呢？

PS:完整的SQL見(jiàn)這里

問(wèn)題解答

DDL漏了個(gè)逗號(hào)

MySQL并不支持CHECK約束，參見(jiàn)MySQL文檔

可以用觸發(fā)器來(lái)實(shí)現(xiàn)