blocks|key|4201974|text|您正在尝试访问一个不存在的对象(表'tbl')，您正在尝试创建它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4201975|MariaDB+[_]>+DROP+TABLE+IF+EXISTS+`tbl`;
Query+OK,+0+rows+affected+(0.01+sec)

MariaDB+[_]>+CREATE+TABLE+IF+NOT+EXISTS+`tbl`+(
++++->+++`id`+INT+NOT+NULL,
++++->+++`start`+DATETIME+NOT+NULL,
++++->+++`end`+DATETIME+NOT+NULL,
++++->+++CONSTRAINT+`C1`+CHECK+(`id`+NOT+IN
++++->+++++++++++++(SELECT+`id`
++++->++++++++++++++FROM+`tbl`+AS+`tt`
++++->++++++++++++++WHERE+`tt`.`start`+BETWEEN+`start`+AND+`end`+OR
++++->++++++++++++++++++++`tt`.`end`+BETWEEN+`start`+AND+`end`)
++++->+++),
++++->+++CONSTRAINT+`C2`+CHECK+(`end`+>+`start`)
++++->+);
ERROR+1146+(42S02):+Table+'_.tbl'+doesn't+exist|code-block|syntax|javascript|4201976|在访问确实存在的对象的情况下：|4201977|MariaDB+[_]>+DROP+TABLE+IF+EXISTS+`tbl_TEMP`,+`tbl`;
Query+OK,+0+rows+affected,+2+warnings+(0.00+sec)

MariaDB+[_]>+CREATE+TABLE+IF+NOT+EXISTS+`tbl_TEMP`+(
++++->+++`id`+INT+NOT+NULL,
++++->+++`start`+DATETIME+NOT+NULL,
++++->+++`end`+DATETIME+NOT+NULL
++++->+);
Query+OK,+0+rows+affected+(0.00+sec)

MariaDB+[_]>+CREATE+TABLE+IF+NOT+EXISTS+`tbl`+(
++++->+++`id`+INT+NOT+NULL,
++++->+++`start`+DATETIME+NOT+NULL,
++++->+++`end`+DATETIME+NOT+NULL,
++++->+++CONSTRAINT+`C1`+CHECK+(
++++->+++++`id`+NOT+IN+(SELECT+`id`
++++->++++++++++++++++++FROM+`tbl_TEMP`+AS+`tt`
++++->++++++++++++++++++WHERE+`tt`.`start`+BETWEEN+`start`+AND+`end`+OR
++++->++++++++++++++++++++++++`tt`.`end`+BETWEEN+`start`+AND+`end`)
++++->+++),
++++->+++CONSTRAINT+`C2`+CHECK+(`end`+>+`start`)
++++->+);
ERROR+1901+(HY000):+Function+or+expression+'select+...'
++++++++++++++++++++cannot+be+used+in+the+CHECK+clause+of+`C1`|4201978|一个可能有用的选项：|4201979|MariaDB+[_]>+DROP+TABLE+IF+EXISTS+`tbl`;
Query+OK,+0+rows+affected,+1+warning+(0.00+sec)

MariaDB+[_]>+CREATE+TABLE+IF+NOT+EXISTS+`tbl`+(
++++->+++`id`+INT+NOT+NULL,
++++->+++`start`+DATETIME+NOT+NULL,
++++->+++`end`+DATETIME+NOT+NULL,
++++->+++CONSTRAINT+`C2`+CHECK+(`end`+>+`start`)
++++->+);
Query+OK,+0+rows+affected+(0.00+sec)

MariaDB+[_]>+DELIMITER+//

MariaDB+[_]>+CREATE+TRIGGER+`trg_bi_idduplicate`+BEFORE+INSERT+ON+`tbl`
++++->+FOR+EACH+ROW+
++++->+BEGIN
++++->+++++++IF+EXISTS+(SELECT+NULL
++++->++++++++++++++++++FROM+`tbl`
++++->++++++++++++++++++WHERE+`id`+=+NEW.`id`)+THEN
++++->+++++++++SIGNAL+SQLSTATE+'45000'+SET
++++->+++++++++MYSQL_ERRNO+=+31001,
++++->+++++++++MESSAGE_TEXT+=+'`id`+DUPLICATE';
++++->+++++++END+IF;
++++->+END//
Query+OK,+0+rows+affected+(0.00+sec)

MariaDB+[_]>+DELIMITER+;

MariaDB+[_]>+INSERT+INTO+`tbl`
++++->+SELECT+1,+'2000-01-01+00:00:01',+'2000-01-01+00:00:01';
ERROR+4025+(23000):+CONSTRAINT+`C2`+failed+for+`_`.`tbl`

MariaDB+[_]>+INSERT+INTO+`tbl`
++++->+SELECT+1,+'2000-01-01+00:00:01',+'2000-01-01+00:00:02';
Query+OK,+1+row+affected+(0.00+sec)
Records:+1++Duplicates:+0++Warnings:+0

MariaDB+[_]>+SELECT
++++->+++`id`,
++++->+++`start`,
++++->+++`end`
++++->+FROM
++++->+++`tbl`;
%2B----%2B---------------------%2B---------------------%2B
%7C+id+%7C+start+++++++++++++++%7C+end+++++++++++++++++%7C
%2B----%2B---------------------%2B---------------------%2B
%7C++1+%7C+2000-01-01+00:00:01+%7C+2000-01-01+00:00:02+%7C
%2B----%2B---------------------%2B---------------------%2B
1+row+in+set+(0.00+sec)

MariaDB+[_]>+INSERT+INTO+`tbl`
++++->+SELECT+1,+'2000-01-01+00:00:01',+'2000-01-01+00:00:02';
ERROR+31001+(45000):+`id`+DUPLICATE

MariaDB+[_]>+SELECT
++++->+++`id`,
++++->+++`start`,
++++->+++`end`
++++->+FROM
++++->+++`tbl`;
%2B----%2B---------------------%2B---------------------%2B
%7C+id+%7C+start+++++++++++++++%7C+end+++++++++++++++++%7C
%2B----%2B---------------------%2B---------------------%2B
%7C++1+%7C+2000-01-01+00:00:01+%7C+2000-01-01+00:00:02+%7C
%2B----%2B---------------------%2B---------------------%2B
1+row+in+set+(0.00+sec)|4201980|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You're trying to access an object that doesn't exist (Table 'tbl'), you are trying to create it:

<pre class="lang-sql prettyprint-override"><code>MariaDB [_]&gt; DROP TABLE IF EXISTS `tbl`;
Query OK, 0 rows affected (0.01 sec)

MariaDB [_]&gt; CREATE TABLE IF NOT EXISTS `tbl` (
 -&gt; `id` INT NOT NULL,
 -&gt; `start` DATETIME NOT NULL,
 -&gt; `end` DATETIME NOT NULL,
 -&gt; CONSTRAINT `C1` CHECK (`id` NOT IN
 -&gt; (SELECT `id`
 -&gt; FROM `tbl` AS `tt`
 -&gt; WHERE `tt`.`start` BETWEEN `start` AND `end` OR
 -&gt; `tt`.`end` BETWEEN `start` AND `end`)
 -&gt; ),
 -&gt; CONSTRAINT `C2` CHECK (`end` &gt; `start`)
 -&gt; );
ERROR 1146 (42S02): Table '_.tbl' doesn't exist
</code></pre>

In case of access to an object that does exist:

<pre class="lang-sql prettyprint-override"><code>MariaDB [_]&gt; DROP TABLE IF EXISTS `tbl_TEMP`, `tbl`;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

MariaDB [_]&gt; CREATE TABLE IF NOT EXISTS `tbl_TEMP` (
 -&gt; `id` INT NOT NULL,
 -&gt; `start` DATETIME NOT NULL,
 -&gt; `end` DATETIME NOT NULL
 -&gt; );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]&gt; CREATE TABLE IF NOT EXISTS `tbl` (
 -&gt; `id` INT NOT NULL,
 -&gt; `start` DATETIME NOT NULL,
 -&gt; `end` DATETIME NOT NULL,
 -&gt; CONSTRAINT `C1` CHECK (
 -&gt; `id` NOT IN (SELECT `id`
 -&gt; FROM `tbl_TEMP` AS `tt`
 -&gt; WHERE `tt`.`start` BETWEEN `start` AND `end` OR
 -&gt; `tt`.`end` BETWEEN `start` AND `end`)
 -&gt; ),
 -&gt; CONSTRAINT `C2` CHECK (`end` &gt; `start`)
 -&gt; );
ERROR 1901 (HY000): Function or expression 'select ...'
 cannot be used in the CHECK clause of `C1`
</code></pre>

An option that may be useful:

<pre class="lang-sql prettyprint-override"><code>MariaDB [_]&gt; DROP TABLE IF EXISTS `tbl`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

MariaDB [_]&gt; CREATE TABLE IF NOT EXISTS `tbl` (
 -&gt; `id` INT NOT NULL,
 -&gt; `start` DATETIME NOT NULL,
 -&gt; `end` DATETIME NOT NULL,
 -&gt; CONSTRAINT `C2` CHECK (`end` &gt; `start`)
 -&gt; );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]&gt; DELIMITER //

MariaDB [_]&gt; CREATE TRIGGER `trg_bi_idduplicate` BEFORE INSERT ON `tbl`
 -&gt; FOR EACH ROW 
 -&gt; BEGIN
 -&gt; IF EXISTS (SELECT NULL
 -&gt; FROM `tbl`
 -&gt; WHERE `id` = NEW.`id`) THEN
 -&gt; SIGNAL SQLSTATE '45000' SET
 -&gt; MYSQL_ERRNO = 31001,
 -&gt; MESSAGE_TEXT = '`id` DUPLICATE';
 -&gt; END IF;
 -&gt; END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]&gt; DELIMITER ;

MariaDB [_]&gt; INSERT INTO `tbl`
 -&gt; SELECT 1, '2000-01-01 00:00:01', '2000-01-01 00:00:01';
ERROR 4025 (23000): CONSTRAINT `C2` failed for `_`.`tbl`

MariaDB [_]&gt; INSERT INTO `tbl`
 -&gt; SELECT 1, '2000-01-01 00:00:01', '2000-01-01 00:00:02';
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [_]&gt; SELECT
 -&gt; `id`,
 -&gt; `start`,
 -&gt; `end`
 -&gt; FROM
 -&gt; `tbl`;
+----+---------------------+---------------------+
| id | start | end |
+----+---------------------+---------------------+
| 1 | 2000-01-01 00:00:01 | 2000-01-01 00:00:02 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

MariaDB [_]&gt; INSERT INTO `tbl`
 -&gt; SELECT 1, '2000-01-01 00:00:01', '2000-01-01 00:00:02';
ERROR 31001 (45000): `id` DUPLICATE

MariaDB [_]&gt; SELECT
 -&gt; `id`,
 -&gt; `start`,
 -&gt; `end`
 -&gt; FROM
 -&gt; `tbl`;
+----+---------------------+---------------------+
| id | start | end |
+----+---------------------+---------------------+
| 1 | 2000-01-01 00:00:01 | 2000-01-01 00:00:02 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
</code></pre>

Is it possible to do check constraint with subquery on maria db table like this:

<pre><code>CREATE TABLE `tbl` (
 `id` int NOT NULL,
 `start` datetime NOT NULL,
 `end` datetime NOT NULL,
 CONSTRAINT C1 CHECK (id not in (select id from tbl as tt where tt.start between start and end or tt.end between start and end)),
 CONSTRAINT C2 CHECK (end&gt;start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code></pre>

I got error: ERROR 1146 (42S02): Table 'test.tbl' doesn't exist

Mariadb check constraint with subquerie on current table

是否可以这样对子查询maria db表进行检查约束：CREATE TABLE `tbl` (  `id` int NOT NULL,  `start` datetime NOT NULL,  `end` datetime NOT NULL,   CONSTRAINT C1 CHECK (id not in (selec...

问在当前表上使用子查询的Mariadb检查约束
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在当前表上使用子查询的Mariadb检查约束EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在当前表上使用子查询的Mariadb检查约束
EN