使用PostgreSQL 9.2.4,我有一个表users
,它与表user_roles
的1:多关系。users
表存储员工和其他类型的用户。
Table "public.users"
Column | Type | Modifiers
-----------------+-------------------+-----------------------------------------------------
uid | integer | not null default nextval('users_uid_seq'::regclass)
employee_number | character varying |
name | character varying |
Indexes:
"users_pkey" PRIMARY KEY, btree (uid)
Referenced by:
TABLE "user_roles" CONSTRAINT "user_roles_uid_fkey" FOREIGN KEY (uid) REFERENCES users(uid)
Table "public.user_roles"
Column | Type | Modifiers
-----------+-------------------+------------------------------------------------------------------
id | integer | not null default nextval('user_roles_id_seq'::regclass)
uid | integer |
role | character varying | not null
Indexes:
"user_roles_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"user_roles_uid_fkey" FOREIGN KEY (uid) REFERENCES users(uid)
字符串
如果user_roles.role_name
包含员工角色名称的相关行,我想确保users.employee_number
列不能是NULL
。也就是说,我希望数据库强制执行这样的约束,即对于某些角色,users.employee_number
必须有一个值,而对于其他角色则没有。
我如何才能完成这一点,最好不使用用户定义的函数或触发器?我发现(blog post,SO Answer)SQL Server支持索引视图,这听起来像是可以满足我的目的。但是,我假设物化视图在我的情况下不会起作用,因为它们不是动态更新的。
3条答案
按热度按时间hrysbysz1#
澄清说明
这一要求的表述留下了解释的余地:
user_roles.role_name
包含员工角色名称。"*我的解释是:
user_roles
中的条目具有role_name = 'employee'
。"*问题是
FOREIGN KEY
和CHECK
约束是经过验证的、用于强制关系完整性的气密工具。触发器功能强大,用途广泛,但更复杂,不那么严格,并且有更多的空间来处理设计错误和极端情况。FK约束在您的情况下起初似乎是不可能的:它需要引用
PRIMARY KEY
或UNIQUE
约束-这两个约束都不允许使用NULL值。不存在部分FK约束。唯一脱离严格参照完整性的是 referencing 列中的null
值--使用FK约束的默认**MATCH SIMPLE
**行为。The manual:MATCH SIMPLE
允许任何外键列为空;如果它们中的任何一个为空,则不要求该行在被引用的表中具有匹配项。请参阅:
解决方法是引入一个布尔标志
is_employee
来标记两边的雇员,在users
中定义为NOT NULL
,但在user_roles
中允许为NULL
:溶液
这就 * 完全 * 地满足了您的需求,同时将噪音和开销降至最低:
字符串
就这些了
可选触发器,方便使用
以下触发器是可选的,但为了方便自动设置添加的标签
is_employee
,建议您不要执行任何额外的操作:型
简洁,优化,仅在需要时调用。
(在Posterre 11之前,请使用(误导性的)key word
PROCEDURE
.,)fiddle
老家伙。(也适用于古代的Posterre 9.1。)
主要观点
现在,如果我们要设置
user_roles.role_name = 'employee'
,那么必须首先有一个匹配的user.employee_nr
。您仍然可以将
employee_nr
添加到 any 用户,并且(然后)仍然可以使用is_employee
标记任何user_roles
,而不管实际的role_name
是什么。如果需要,很容易禁用,但此实现不会引入任何超出所需的限制。**
users.is_employee
**只能是true
或false
,并通过CHECK
约束强制反映employee_nr
的存在。触发器会自动使列保持同步。您可以允许false
用于其他目的,但仅需对设计进行少量更新。user_roles.is_employee
**的规则稍有不同:如果是role_name = 'employee'
,则必须是true
。由CHECK
约束强制执行,并再次由触发器自动设置。但允许将role_name
更改为其他值,并仍保留is_employee
。没有人说一个employee_nr
的用户是 * 必需的 * 在user_roles
中有一个相应的条目,正好相反!同样,如果需要,还可以方便地执行。如果存在其他可能干扰的触发因素,请考虑:
但是,我们不必担心触发器可能违反规则。使用
CHECK
和FK约束强制执行请求的规则,不允许出现任何例外。旁白:我把列
is_employee
放在约束UNIQUE (is_employee, users_id)
* 的前面是有原因的 *。users_id
已包含在PK中,因此在此处可以排在第二位:u1ehiz5o2#
首先,您可以使用触发器解决此问题。
但是,我认为你可以使用约束来解决这个问题,只是有点奇怪:
字符串
这应该是可行的,但这是一个尴尬的解决方案:
EmployeeNumber
。toiithl63#
新答案:
这(SQL Sub queries in check constraint)似乎回答了您的问题,并且该语言仍然在9.4文档中(http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html)。
旧答案:
字符串
上述查询选择
User
中的所有字段和UserRole
中的所有字段(别名为UserRole1)。我假设这两个字段之间的键字段是User.id和UserRole1.UserId
,请将它们更改为真实的值。在查询的
JOIN
部分中,左侧有一个OR,要求用户表中的雇员编号不是NULL
,并且 *UserRole1.role_name位于列表中,您必须将 * 提供给IN ()
操作员。JOIN
的右边部分是相反的,它要求User.employee_number
是NULL
(这应该是你的非雇员集)。如果您需要更精确的解决方案,请提供有关表结构的更多详细信息,以及必须为员工选择哪些角色。