Mariadb检查当前表上的subquerie约束

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (25)

是否可以在maria db表上使用子查询进行检查约束,如下所示:

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>start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我收到错误:ERROR 1146(42S02):表'test.tbl'不存在

提问于
用户回答回答于

您正在尝试访问不存在的对象(表'tbl'),您正在尝试创建它:

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

如果访问存在的对象:

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`

一个可能有用的选项:

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`;
+----+---------------------+---------------------+
| id | start               | end                 |
+----+---------------------+---------------------+
|  1 | 2000-01-01 00:00:01 | 2000-01-01 00:00:02 |
+----+---------------------+---------------------+
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`;
+----+---------------------+---------------------+
| id | start               | end                 |
+----+---------------------+---------------------+
|  1 | 2000-01-01 00:00:01 | 2000-01-01 00:00:02 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

扫码关注云+社区

领取腾讯云代金券