blocks|key|1245850|text|要做到这一点，即使当多个会话正在进行更新时，它也是健全和正确的，这并不容易。如果您尝试使用触发器执行此操作，您将陷入一片混乱，而Oracle的声明性约束不足以表达这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245851|具体做法如下：|1245852|1245853|在父表和子表上创建实体化视图日志|ordered-list-item|1245854|创建将它们联接在一起的实体化联接视图，并计算按父表分组的子表的数量。这必须在提交时快速刷新|1245855|对物化连接视图设置了一个约束，即子记录的计数必须等于"n“(数据库常量)|1245856|1245857|然后，您可以执行一系列insert/update/delete语句。提交时，实体化视图将刷新，如果不满足条件，此时将出现违反约束的错误。|1245858|一个额外的技巧是只将不符合约束的行包含到物化视图中(具有count(ChildId)+<>+5)，这样就不会浪费任何存储空间。|1245859|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|V|8|@]|9|@]|A|$]]|$1|H|3|I|5|G|7|W|8|@]|9|@]|A|$]]|$1|J|3|K|5|G|7|X|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

Doing this so that it is sound and correct even when multiple sessions are doing updates is not easy. You will get yourself in a mess if you try this with triggers, and Oracle's declarative constraints are not powerful enough to express this.

It can be done as follows:-

<ol>
<li>Create a materialized view log on both the parent and the child tables</li>
<li>Create a materialized join view that joins them together and counts the number of children grouped by the parent. This must be REFRESH FAST ON COMMIT</li>
<li>Put a constraint on the materialized join view that the count of child records must equal "n" (your database constant)</li>
</ol>

You can then do a series of insert/update/delete statements. When you commit, the materialized view will refresh and if the condition is not met you will get a constraint violation error at that point.

A bonus bit of trickery is to only include rows that fail the constraint into the materialized view (HAVING count(ChildId) &lt;> 5) so you do not waste any storage space.

blocks|key|4462727|text|在更早的“鸡%2B蛋”点的基础上，您可以创建可推迟的约束，这些约束在提交时才会生效……这些会有帮助吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4462728|例如：|4462729|ALTER+TABLE+AGREEMENTS+ADD+CONSTRAINT+name+FOREIGN+KEY+(column)+REFERENCES+table+(column)+DEFERRABLE+INITIALLY+DEFERRED;|code-block|syntax|javascript|4462730|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Building upon the earler "chicken + egg" points, you can create deferrable constraints which aren't validated until commit time... these might help?

e.g.

<pre><code>ALTER TABLE AGREEMENTS ADD CONSTRAINT name FOREIGN KEY (column) REFERENCES table (column) DEFERRABLE INITIALLY DEFERRED;
</code></pre>

blocks|key|4462651|text|我不明白是怎么回事。这是一个古老的问题：“先有鸡还是先有蛋？”在尚未添加子元素的情况下，如何约束父元素，以及如何在没有父元素的情况下添加子元素？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4462652|您可以创建一个名为"ValidParents“的新表，该表只包含具有N个子in的父in，并使其与触发器保持同步。|4462653|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

I don't see how. It is the old question "which came first, the chicken or the egg?". How can you constrain the parent when no children have been added yet, and how can you add children without a parent?

you could create a new table, called something like "ValidParents" that only has the parent IDs that have N children, and keep it in sync with triggers.

blocks|key|186637|text|有一种替代解决方案，可以强制每个父级恰好有0或n个子视图，而不使用仅使用check、foreign+key和唯一性约束的物化视图。为此，必须对子节点进行编号，并添加一个包含下一个兄弟节点编号的字段。下面是一个在PostgreSQL中工作的n=5示例，对于其他数据库，用户可能需要调整类型为serial|type|unstyled|depth|inlineStyleRanges|entityRanges|data|186638|create+table+Tree(
++id+serial,
++parent_id+integer+not+null+references+Tree(id),
++child_nr+integer+check(child_nr+between+1+and+5),
++next_sibling_nr+integer,
++unique+(parent_id,+child_nr),
++check(next_sibling_nr+in+(child_nr%2B1,+child_nr-4)),
++check(((parent_id+is+null)+and+(child_nr+is+null)+and
++++(next_sibling_nr+is+null))+or+((parent_id+is+not+null)
++++and+(child_nr+is+not+null)+and+(next_sibling_nr+is+not+null))),
++foreign+key+(parent_id,+next_sibling_nr)+references+Tree(parent_id,+child_nr),
++primary+key+(id)
);|code-block|syntax|javascript|186639|最后一个(长)检查约束确保字段parent_id、child_nr和next_sibling_nr都为null或都不为null。唯一性约束和对child_nr字段的检查会确保一个父级最多有5个子级。对(parent_id，next_sibling_nr)的另一个check约束和外键约束确保有不少于5个子项。|186640|在使用以下命令插入具有自动生成的id1的根目录之后|186641|insert+into+Tree+(parent_id)
++values+(null);|186642|可以添加5个一组的孩子：|186643|insert+into+Tree+(parent_id,+child_nr,+next_sibling_nr)
++values+(1,+1,+2),
+++++++++(1,+2,+3),
+++++++++(1,+3,+4),
+++++++++(1,+4,+5),
+++++++++(1,+5,+1);|186644|这个解决方案来自几周前我在ago上问过的一个类似问题的答案。|offset|length|186645|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1127228/database-design-for-constraint-enforcing-pairing^0|0|0|0|0|0|0|0|D|3|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|17|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|18|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|19|8|@]|9|@$S|1A|T|1B|1|1C]]|A|$]]|$1|U|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

There is an alternative solution to force each parent to have exactly either 0 or n children without materialized views using just check, foreign key and uniqueness constraints. For this, one has to number the children and add a field containing the number of the next sibling. Here an example for n=5 that works in PostgreSQL, for other DBS one has to adapt probably the type serial:

<pre><code>create table Tree(
 id serial,
 parent_id integer not null references Tree(id),
 child_nr integer check(child_nr between 1 and 5),
 next_sibling_nr integer,
 unique (parent_id, child_nr),
 check(next_sibling_nr in (child_nr+1, child_nr-4)),
 check(((parent_id is null) and (child_nr is null) and
 (next_sibling_nr is null)) or ((parent_id is not null)
 and (child_nr is not null) and (next_sibling_nr is not null))),
 foreign key (parent_id, next_sibling_nr) references Tree(parent_id, child_nr),
 primary key (id)
);
</code></pre>

The last (long) check constraint ensures that the fields parent_id, child_nr and next_sibling_nr are all null or all not null. The uniqueness constraint and the check for the child_nr field take care that a parent has at most 5 children. The other check constraint and the foreign key constraint on the pair (parent_id, next_sibling_nr) ensure that there are not less than 5 children.

After inserting a root with automatically generated id 1 with the command

<pre><code>insert into Tree (parent_id)
 values (null);
</code></pre>

one can add children always in packs of 5:

<pre><code>insert into Tree (parent_id, child_nr, next_sibling_nr)
 values (1, 1, 2),
 (1, 2, 3),
 (1, 3, 4),
 (1, 4, 5),
 (1, 5, 1);
</code></pre>

This solution is derived from the answers to a similar question I asked some weeks <a href="https://stackoverflow.com/questions/1127228/database-design-for-constraint-enforcing-pairing">ago</a>.

blocks|key|186555|text|这可能不是您想要的，但我确实有一个方法可以做类似的事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|186556|一对多的通常安排是这样的：|186557|Primary+Table:
primary_id+(PK)
primary_stuff

Secondary+Table:
secondary_id+(PK)
primary_id+(FK)
secondary_stuff|code-block|syntax|javascript|186558|另一种方法是，建立严格的一对一模型：|186559|Primary+Table:
primary_id+(PK)
secondary_id+(FK,+non-null)
primary_stuff

Secondary+Table:
secondary_id+(PK)
secondary_stuff|186560|这可能有点奇怪，但它是有效的。在存在一对多和一对一的情况下，这种变化可能很有用，比如一个客户有多个地址，但只有一个帐单地址。|186561|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|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This may not be what you want, but I do have one method that does something similar.

The usual arrangement for one-to-many is something like this:

<pre><code>Primary Table:
primary_id (PK)
primary_stuff

Secondary Table:
secondary_id (PK)
primary_id (FK)
secondary_stuff
</code></pre>

The alternative, to model a strict one-to-one would be:

<pre><code>Primary Table:
primary_id (PK)
secondary_id (FK, non-null)
primary_stuff

Secondary Table:
secondary_id (PK)
secondary_stuff
</code></pre>

It might be a bit strange, but it works. A variation of this may be useful where there's a one-to-many with a one-to-one in it, such as having multiple addresses for a customer, but exactly one billing address.

blocks|key|4462770|text|鸡和蛋问题的另一种解决方案是使用INSERT+ALL。因为它是单个语句，所以不需要可推迟的外键约束。它还提供了一种机制，用于插入确切数量的依赖行。附加约束会阻止插入附加行。但是我们需要一个带有外键的辅助表，以防止意外删除感兴趣的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4462771|在本例中，n=+3。|4462772|SQL>+create+table+parent
++2+++(+pk_col+number+not+null
++3+++++,+col1+varchar2(20)
++4+++++,+constraint+par_pk+primary+key+(pk_col)
++5++++)
++6++/

Table+created.

SQL>+
SQL>+create+table+child
++2+++(+pk_col+number+not+null
++3+++++,+seqno+number(1,0)+not+null
++4+++++,+col2+date
++5+++++,+constraint+ch_pk+primary+key
++6++++++++++(pk_col,+seqno)
++7+++++,+constraint+ch_par_fk+foreign+key
++8++++++++++(pk_col)+references+parent+(pk_col)
++9+++++,+constraint+ch_ck+check+(seqno+between+1+and+3)
+10++++)
+11++/

Table+created.

SQL>+
SQL>+create+table+child_lock
++2+++(+pk_col_1+number+not+null
++3+++++,+seqno_1+number(1,0)+not+null
++4+++++,+pk_col_2+number+not+null
++5+++++,+seqno_2+number(1,0)+not+null
++6+++++,+pk_col_3+number+not+null
++7+++++,+seqno_3+number(1,0)+not+null
++8+++++,+constraint+chlk_pk+primary+key
++9++++++++++(pk_col_1,+seqno_1,+pk_col_2,+seqno_2,+pk_col_3,+seqno_3)
+10+++++,+constraint+chlk_par_1_fk+foreign+key
+11++++++++++(pk_col_1,+seqno_1)+references+child+(pk_col,+seqno)
+12+++++,+constraint+chlk_par_2_fk+foreign+key
+13++++++++++(pk_col_2,+seqno_2)+references+child+(pk_col,+seqno)
+14+++++,+constraint+chlk_par_3_fk+foreign+key
+15++++++++++(pk_col_3,+seqno_3)+references+child+(pk_col,+seqno)
+16++++)
+17++/

Table+created.

SQL>+
SQL>+insert+all
++2++++++into+parent+values+(pk_val,+val_1)
++3++++++into+child+values+(pk_val,+1,+sysdate)
++4++++++into+child+values+(pk_val,+2,+sysdate%2B1)
++5++++++into+child+values+(pk_val,+3,+sysdate%2B2)
++6++++++into+child_lock+values+(pk_val,+1,+pk_val,+2,+pk_val,+3)
++7++select+999+as+pk_val
++8+++++++++,+'APPLE+PIE'+as+val_1
++9++from+dual
+10++/

5+rows+created.

SQL>+
SQL>+insert+into+child+values+(999,+4,+sysdate%2B4)
++2++/
insert+into+child+values+(999,+4,+sysdate%2B4)
*
ERROR+at+line+1:
ORA-02290:+check+constraint+(APC.CH_CK)+violated+


SQL>+insert+into+child+values+(999,+3,+sysdate%2B4)
++2++/
insert+into+child+values+(999,+3,+sysdate%2B4)
*
ERROR+at+line+1:
ORA-00001:+unique+constraint+(APC.CH_PK)+violated+


SQL>+insert+into+child+values+(999,+2.5,+sysdate%2B4)
++2++/
insert+into+child+values+(999,+2.5,+sysdate%2B4)
*
ERROR+at+line+1:
ORA-00001:+unique+constraint+(APC.CH_PK)+violated+


SQL>+delete+from+child
++2++/
delete+from+child
*
ERROR+at+line+1:
ORA-02292:+integrity+constraint+(APC.CHLK_PAR_1_FK)+violated+-+child+record+found+


SQL>|code-block|syntax|javascript|4462773|我承认这个解决方案是人为设计的，也是僵化的，但最初的需求也是如此。这也不是万无一失的-从CHILD_LOCK中删除行，您可以删除一个或多个子记录。|4462774|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

An alternative solutionb to the chicken and egg problem is to use INSERT ALL. Because it is a single statement it obviates the need for deferrable foreign key constraints. It also provides a mechanism for inserting an exact number of dependent rows. Additional constraints prevent the insertion of additional rows. But we need a subsidiary table with foreign keys to prevent the accidental deletion of the rows of interest. 

In this example, n = 3.

<pre><code>SQL&gt; create table parent
 2 ( pk_col number not null
 3 , col1 varchar2(20)
 4 , constraint par_pk primary key (pk_col)
 5 )
 6 /

Table created.

SQL&gt; 
SQL&gt; create table child
 2 ( pk_col number not null
 3 , seqno number(1,0) not null
 4 , col2 date
 5 , constraint ch_pk primary key
 6 (pk_col, seqno)
 7 , constraint ch_par_fk foreign key
 8 (pk_col) references parent (pk_col)
 9 , constraint ch_ck check (seqno between 1 and 3)
 10 )
 11 /

Table created.

SQL&gt; 
SQL&gt; create table child_lock
 2 ( pk_col_1 number not null
 3 , seqno_1 number(1,0) not null
 4 , pk_col_2 number not null
 5 , seqno_2 number(1,0) not null
 6 , pk_col_3 number not null
 7 , seqno_3 number(1,0) not null
 8 , constraint chlk_pk primary key
 9 (pk_col_1, seqno_1, pk_col_2, seqno_2, pk_col_3, seqno_3)
 10 , constraint chlk_par_1_fk foreign key
 11 (pk_col_1, seqno_1) references child (pk_col, seqno)
 12 , constraint chlk_par_2_fk foreign key
 13 (pk_col_2, seqno_2) references child (pk_col, seqno)
 14 , constraint chlk_par_3_fk foreign key
 15 (pk_col_3, seqno_3) references child (pk_col, seqno)
 16 )
 17 /

Table created.

SQL&gt; 
SQL&gt; insert all
 2 into parent values (pk_val, val_1)
 3 into child values (pk_val, 1, sysdate)
 4 into child values (pk_val, 2, sysdate+1)
 5 into child values (pk_val, 3, sysdate+2)
 6 into child_lock values (pk_val, 1, pk_val, 2, pk_val, 3)
 7 select 999 as pk_val
 8 , 'APPLE PIE' as val_1
 9 from dual
 10 /

5 rows created.

SQL&gt; 
SQL&gt; insert into child values (999, 4, sysdate+4)
 2 /
insert into child values (999, 4, sysdate+4)
*
ERROR at line 1:
ORA-02290: check constraint (APC.CH_CK) violated 


SQL&gt; insert into child values (999, 3, sysdate+4)
 2 /
insert into child values (999, 3, sysdate+4)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.CH_PK) violated 


SQL&gt; insert into child values (999, 2.5, sysdate+4)
 2 /
insert into child values (999, 2.5, sysdate+4)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.CH_PK) violated 


SQL&gt; delete from child
 2 /
delete from child
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.CHLK_PAR_1_FK) violated - child record found 


SQL&gt;
</code></pre>

I accept the solution is a trifle contrived and also inflexible, but then so is the original requirement. It is also far from bulletproof - delete the row from CHILD_LOCK and you can delete one or more CHILD records.

blocks|key|1245921|text|您可以像往常一样使用1:M关系创建表，然后在子表上有一个count列，该列带有一个检查约束，该约束确定一个父表可以存在多少个子项，以及对parent+ID+%2B+count列的唯一约束。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1245922|CREATE+TABLE+Parent+(PID+NUMBER+PRIMARY+KEY);

CREATE+TABLE+Child+(
++++PID+NUMBER+NOT+NULL,
++++Count+NUMBER(1)+NOT+NULL,
++++CONSTRAINT+count_check+CHECK+(Count+BETWEEN+1+AND+5),
++++CONSTRAINT+parent_child_fk+FOREIGN+KEY+(PID)+REFERENCES+Parent+(PID),
++++CONSTRAINT+count_unique+UNIQUE+(PID,+Count));|code-block|syntax|javascript|1245923|唯一不能保证的是，每个父对象至少有五个子对象；要解决这个问题，您可以像WW建议的那样创建一个带约束的物化视图，或者在应用程序中构建一些额外的东西(例如，“错误”报告)。|1245924|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can create your tables as normal with a 1:M relationship, then on the child table have a count column with a check constraint that determines how many children can exist for a parent, as well as a unique constraint over the Parent ID + count column. e.g.:

<pre><code>CREATE TABLE Parent (PID NUMBER PRIMARY KEY);

CREATE TABLE Child (
 PID NUMBER NOT NULL,
 Count NUMBER(1) NOT NULL,
 CONSTRAINT count_check CHECK (Count BETWEEN 1 AND 5),
 CONSTRAINT parent_child_fk FOREIGN KEY (PID) REFERENCES Parent (PID),
 CONSTRAINT count_unique UNIQUE (PID, Count));
</code></pre>

The only thing this doesn't guarantee is that for each parent there are AT LEAST five children; to get around this you might create a materialized view with a constraint as WW suggests, or build something extra in the application (e.g. an "error" report).

Can a One-To-Constant Relationship be completely modeled in Oracle with constraints? In other words, the PARENT entity ALWAYS has EXACTLY n-CHILDREN of the child entity, and each child only has one parent.

Consider n to be a database constant.

Modeling One-to-Constant Relationship

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

一对一的关系是否可以在Oracle中使用约束完全建模？换句话说，父实体始终恰好具有子实体的n个子实体，并且每个子实体只有一个父实体。考虑n是数据库常量。

问对一对一关系进行建模
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问对一对一关系进行建模EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问对一对一关系进行建模
EN