约束在MYSQL表中? [英] Constraint in MYSQL table?

问题描述

我有一个名为 Groups 的表，其中主键= Pkey .在群组中，有一个递归关联 Parent_group 引用 Pkey .我在关系 Groups 中将Parent_Group定义为外键.我正在使用MYSQL.

I have a table named Groups with primary key = Pkey. In Group there is a recursive association Parent_group references Pkey . I defined a Parent_Group as foreign key in relation Groups. I am using MYSQL.

餐桌人数:组

+------+-----------+------------+----------+---------------+            
| PKey | GroupName | Region     |  Role    | Parent_Group  |
+------+-----------+------------+----------+---------------+            
| k1   | RootGroup | Jaipur     |  Admin   | NULL          | 
+------+-----------+------------+----------+---------------+            
| k2   | G2        | Alwar      |  Admin   | k1            |
+------+-----------+------------+----------+---------------+            
| k3   | G3        | Jaipur     |  Guest   | k3            | 
+------+-----------+------------+----------+---------------+            
| k4   | G4        | Alwar      |  Operator| k2            |
+------+-----------+------------+----------+---------------+            

查询创建表:

CREATE TABLE IF NOT EXISTS `groups` 
(
  `PKey` varchar(64) NOT NULL,
  `group_name` varchar(64) DEFAULT NULL,
  `Region` varchar(128) NOT NULL,
  `Role` varchar(128) NOT NULL,
  `parent_group` varchar(64) DEFAULT NULL, 
  PRIMARY KEY (`Pkey`),
  KEY `FK_ParentGroup_ChildGroup` (`parent_group`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Groups 表将随我的应用程序一起提供，其中只有一个 RootGroup 元组.

The Groups table will be shipped with my application with only one RootGroup tuple.

和我要在表上施加两个约束吗?如下:

1. 新插入的行的 Parent_group 列
不能为NULL值
2. 添加约束，以防止删除 RootGroup 行.

我想知道，SQL是否有可能在SQL中运行(如果是如何?)，或者我必须在后端系统中处理?

I wants to know, Weather it is possible within SQL (if yes how?) or I have to handle in back-end systems?

可以使用触发器吗?

编辑:我想对表施加额外的约束，以使新的插入元组不能指向自身.例如

EDIT: I wants to impose an extra constraint on table So that a new inserting tuple can not point to itself. e.g.

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");
Query OK, 1 row affected (0.04 sec)

应该失败吗?

推荐答案

关于您要施加的两个约束，有几件事:

There are a few things about the two constraints you wish to impose:

1. 新插入的行的Parent_group列的值不能为NULL.

1. New inserted row can not have NULL value for Parent_group column.

• 仅当列包含所有非空值时，才可以在该列上施加NOT NULL约束.根节点在此列中需要一个空值.
• 为此，您可以使用CHECK约束.阅读有关CHECK CONSTRAINT的更多信息

  检查((peky = AND parent_group IS NULL)
  或
  (peky！= AND parent_group不为空))

  CHECK ((peky= AND parent_group IS NULL)
  OR
  (peky!= AND parent_group IS NOT NULL))

  这将仅允许对根节点使用NULL值，并对表中的所有其他行强制使用NOT NULL值.

  This will allow a NULL value only for the root node and will enforce a NOT NULL value for every other row in the table.

  1. 添加一个约束，以便不能删除RootGroup行.

  1. Add a constrain so that RootGroup row can't be deleted.

  • 您已经在 parent_group 和 pkey 之间定义了一个外键，数据库将自动实施参照完整性，并禁止根节点(或就此而言，任何父节点))删除.如果在任何父节点或根节点上尝试执行DELETE操作，数据库将返回错误.
  • That you have already defined a foreign key between parent_group and pkey, the database will automatically enforce referential integrity and forbid the root node (or for that matter any parent node) from being deleted. The database will return an error if a DELETE is attempted on any parent or root node.

  对于 EDIT 部分中提到的要点，您可以在表上放置一个简单的检查约束，如
  CHECK(parent_group！= pkey).这应该为您完成工作.

  For the point mentioned in the EDIT section, you can put a simple check constraint on the table like
  CHECK (parent_group != pkey). This should do the job for you.

  阅读有关如何定义外键约束以及如何使用它们来强制引用完整性的信息.另外，通过我上面发布的链接或

  Read about how to define foreign key constraints and how to use them to enforce referential integrity. Also, go through the link I have posted above or here before you apply these suggestions.

  这篇关于约束在MYSQL表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！