实现复杂的约束 [英] Implementing complicated constraints

问题描述

我在数据库上实施一些限制时遇到了麻烦。

等级。一个例子：


- 表A（AID，BID参考B.BID，ATXT）

- 表B（BID，CID参考C） .CID）

- 表C（CID）


与CID有关，即


SELECT COUNT（*）FROM A，B，C

WHERE A.BID = B.BID

AND B.CID = CID

AND CID = fn_get_cid_for_bid（：new.BID）

AND A.ATXT =：new.ATXT

AND A.AID<> ：new.AID


应为0.


这个我不能强制使用检查约束，因为它不允许

包含子查询。

如果我尝试编写一个检查上述条件并引发

应用程序错误的触发器，那么我总是偶然发现表正在改变，

触发器/功能可能看不到它 - 类型错误，因为它涉及当时正在更改的表格上的选择。


但是，最好是实现这样的对数据库的约束

级别，而不是整个应用程序中的散点检查。


是否有解决此类问题的标准方法？

Hi, I''m having trouble with implementing some constraints on the database
level. An example:

--Table A(AID, BID ref. B.BID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

upon insertion into or updating in A I would like to force that ATXT is
unique with respect to CID, i.e.

SELECT COUNT(*) FROM A,B,C
WHERE A.BID = B.BID
AND B.CID = CID
AND CID = fn_get_cid_for_bid(:new.BID)
AND A.ATXT = :new.ATXT
AND A.AID <> :new.AID

should be 0.

This I cannot force with a check constraint since it is not allowed to
contain subqueries.
If I try to write a trigger that checks the above condition and raises an
application error, then I always stumble upon the "table is mutating,
trigger/function may not see it" -type error, since it involves a select on
the table that is being changed at the time.

However, it would be better to implement such a constraint on the database
level, rather than scatter checks throughout the application.

Is there a standard way solve this type of problem?

推荐答案

" Agoston Bejo" <顾*** @ freemail.hu>在留言中写道

news：cj ********** @ news.caesar.elte.hu ...

|我在数据库上实现一些约束时遇到了麻烦

|水平。例如：

|

| - 表A（AID，BID参考B.BID，ATXT）

| - 表B（BID，CID参考C.CID）

| - 表C（CID）

|

|在插入或更新A时，我想强制ATXT是

|独特的CID，即

|

| SELECT COUNT（*）FROM A，B，C

|在哪里A.BID = B.BID

| AND B.CID = CID

| AND CID = fn_get_cid_for_bid（：new.BID）

| AND A.ATXT =：new.ATXT

| AND A.AID<> ：new.AID

|

|应该是0.

|

|这个我不能用检查约束强制，因为它不允许

|包含子查询。

|如果我尝试编写一个检查上述条件并触发

|的触发器应用程序错误，然后我总是偶然发现表正在变异，

|触发器/功能可能看不到它 - 类型错误，因为它涉及

|的选择

当时正在更改的表格。

|

|但是，最好在数据库上实现这样的约束

|整个应用程序中的水平，而不是分散检查。

|

|有没有一种标准方法可以解决这类问题？

|

|

|


希望我能正确理解这个问题....


没有标准的方法，因为你的唯一性约束基于

多个表（你目前设计它的方式）


但是，如果A.TXT不是NULL，那么你可以对表格进行非规范化

实现并包含CID A并对（A.CID，

A.TXT）施加一个独特的约束。如果A.TXT是NULL可能的，你可能想要对表格进行非规范化

的实现，并且有一个只包含A的PK表（AID？），CID，

和TXT，从表A中存储实际的TXT条目，这样就可以在该表中的CID和TXT上放置一个唯一的约束条件。原因是

NULL / NOT NULL发挥作用是oracle不会阻止单列唯一索引中的多个NULL

值，但会阻止

这种情况​​，因为CID值会重复


++ mcs

"Agoston Bejo" <gu***@freemail.hu> wrote in message
news:cj**********@news.caesar.elte.hu...
| Hi, I''m having trouble with implementing some constraints on the database
| level. An example:
|
| --Table A(AID, BID ref. B.BID, ATXT)
| --Table B(BID, CID ref. C.CID)
| --Table C(CID)
|
| upon insertion into or updating in A I would like to force that ATXT is
| unique with respect to CID, i.e.
|
| SELECT COUNT(*) FROM A,B,C
| WHERE A.BID = B.BID
| AND B.CID = CID
| AND CID = fn_get_cid_for_bid(:new.BID)
| AND A.ATXT = :new.ATXT
| AND A.AID <> :new.AID
|
| should be 0.
|
| This I cannot force with a check constraint since it is not allowed to
| contain subqueries.
| If I try to write a trigger that checks the above condition and raises an
| application error, then I always stumble upon the "table is mutating,
| trigger/function may not see it" -type error, since it involves a select
on
| the table that is being changed at the time.
|
| However, it would be better to implement such a constraint on the database
| level, rather than scatter checks throughout the application.
|
| Is there a standard way solve this type of problem?
|
|
|

hopefully i''m understanding the problem correctly....

there''s no standard way, since your uniqueness constraint is based on
multiple tables (the way you have it currently designed)

however, if A.TXT is NOT NULL, then you could denormalize the table
implementation and include CID in A and put a unique constraint on (A.CID,
A.TXT). if A.TXT is NULL-able, you may want to denormalize the table
implementation and have a table that includes just the PK of A (AID?), CID,
and TXT, storing actual TXT entries out-of-line from table A, so that a
unique constraint can be put on CID and TXT in that table. the reason
NULL/NOT NULL comes into play is that oracle would not prevent multiple NULL
values in a one-column unique index, but would prevent multiple TXT nulls in
this case, since the CID value would be repeated

++ mcs

- 表A（AID，BID参考B.BID，CID参考C.CID，ATXT）

- 表B（BID，CID参考C.CID）

- 表C（CID）


1.将CID键添加到表A.

2.在CID和ATXT上创建唯一索引。


Wario

--Table A(AID, BID ref. B.BID, CID ref. C.CID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

1. Add CID key to table A.
2. Create unique index on CID and ATXT.

Wario

" Agoston Bejo" <顾*** @ freemail.hu>在消息新闻中写道：< cj ********** @ news.caesar.elte.hu> ...

"Agoston Bejo" <gu***@freemail.hu> wrote in message news:<cj**********@news.caesar.elte.hu>...

我在实施一些限制方面遇到了麻烦在数据库
级别。一个例子：

- 表A（AID，BID参考B.BID，ATXT）
- 表B（BID，CID ref.C.CID）
- -Table C（CID）
在插入或更新AI时想强制ATXT对于CID是唯一的，即

SELECT COUNT（ *）从A，B，C
在哪里A.BID = B.BID
AND B.CID = CID
AND CID = fn_get_cid_for_bid（：new.BID）
AND A .ATXT =：new.ATXT
AND A.AID<> ：new.AID

应该是0.

这个我不能强制使用检查约束，因为它不允许包含子查询。
如果我尝试编写一个触发器来检查上述情况并引发一个
应用程序错误，然后我总是偶然发现表正在变异，
触发器/函数可能看不到它。 - 类型错误，因为它涉及当时正在更改的表的选择。

但是，最好在数据库上实现这样的约束
整个应用程序中的水平，而不是分散检查。

是否有解决此类问题的标准方法？

Hi, I''m having trouble with implementing some constraints on the database
level. An example:

--Table A(AID, BID ref. B.BID, ATXT)
--Table B(BID, CID ref. C.CID)
--Table C(CID)

upon insertion into or updating in A I would like to force that ATXT is
unique with respect to CID, i.e.

SELECT COUNT(*) FROM A,B,C
WHERE A.BID = B.BID
AND B.CID = CID
AND CID = fn_get_cid_for_bid(:new.BID)
AND A.ATXT = :new.ATXT
AND A.AID <> :new.AID

should be 0.

This I cannot force with a check constraint since it is not allowed to
contain subqueries.
If I try to write a trigger that checks the above condition and raises an
application error, then I always stumble upon the "table is mutating,
trigger/function may not see it" -type error, since it involves a select on
the table that is being changed at the time.

However, it would be better to implement such a constraint on the database
level, rather than scatter checks throughout the application.

Is there a standard way solve this type of problem?

ATXT的事实A列需要相对于
唯一，C中没有出现的CID值表示关系设计

表中的缺陷。


在不更改表格设计的情况下，有以下几种选择：

1-通过预先执行

验证的存储代码执行插入/更新，避免变异表错误


2-替换表的视图并使用而不是触发器

执行验证并重定向DML


3-使用before和after，statement和row级别的组合

触发器来工作aro和变异表错误。在metalink上有关于

这种技术的注释，但我从未设法成功使用

技术来让我们在案例中做我们需要的工作

类似于你想做的事情。


HTH - Mark D Powell -

The fact that the ATXT column of A needs to be unique in relation to
the CID value which does not appear in A indicates a relational design
flaw in your tables.

Without changing the table design there are several options:
1- Perform the inserts/update via stored code that performs the
validation up front avoiding the mutating table error

2- substitute a view for the table and use instead of triggers to
perform the validation and redirect the DML

3- Use a combination of before and after, statement and row level
triggers to work around the mutating table error. There are notes on
this technique on metalink, but I have never managed to use the
technique successfully to allow us to do what we needed in cases
similar to what you want to do.

HTH -- Mark D Powell --

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