当表A在表B上延迟时，如何向表A插入新记录，反之亦然

I'm not sure if this is well designed, if it's not please, advice me on how to do this.

我不确定这是否设计得很好,如果不满意,请告诉我如何做到这一点。

I'm using Sql Server 2008

我正在使用Sql Server 2008

I have:

TableA (TableA_ID int identity PK, Value varchar(10), TableB_ID PK not null)
TableB (TableB_ID int identity PK, Value varchar(10), TableA_ID PK not null)

The goal is simple:

目标很简单:

TableA can have rows only if there is at least 1 row in TableB associated with TableA;

只有TableB中至少有一行与TableA关联时,TableA才能有行;

And for each row in TableB, there must be a row associated with it in TableA);

对于TableB中的每一行,TableA中必须有一行与之关联;

TableA is the "Parent Table", and TableB is the "Children's table", it's something like, a parent should have 1 or more children, and each child can have only 1 parent.

TableA是“父表”,TableB是“儿童表”,它就像是,父母应该有一个或多个孩子,每个孩子只能有一个父母。

Is this right?

这是正确的吗?

The problem I'm having is when I try to do an INSERT statement, if this is correct, how should I make the INSERT? temporary disable the constraints?

我遇到的问题是当我尝试执行INSERT语句时,如果这是正确的,我应该如何进行INSERT?临时禁用约束?

Thanks!

The problem I'm having is when I try to insert

我遇到的问题是当我尝试插入时

5 个解决方案

#1

TableA (TableA_ID int identity PK, Value varchar(10))
TableB (TableB_ID int identity PK, Value varchar(10), TableA_ID not null)

as a parent, table a does not need to reference table b, since table be requires there be a field in table a. this is called a one to many relationship.

作为父表,表a不需要引用表b,因为表要求表a中有一个字段。这被称为一对多的关系。

so in table a you might have these values:

所以在表a中你可能有这些值:

1 a
2 b
3 c

and in table b you could have these:

在表b中你可以有这些:

1 asdf 1
2 sdfg 1
3 pof 2
4 dfgbsd 3

now you can make a query to show the data from table a with this:

现在,您可以进行查询以显示表a中的数据:

select b.TableB_ID, b.Value, a.TableA_ID, a.Value
from TableB b
inner join TableA
on b.TableA_ID=a.TableA_ID

#2

The parents don't depend on the children. You need to remove your reference to Table B in Table A.

父母不依赖孩子。您需要删除表A中对表B的引用。

#3

You have a circular dependency. These don't really work well for declarative enforcement, you would have to disable the constraints every time you wanted to insert.

你有循环依赖。这些对于声明性强制执行并不是很有效,每次要插入时都必须禁用约束。

#4

That's an unusual requirement. If I was stuck with it (and I would really push back to make sure it was indeed a requirement) I would design it this way:

这是一个不寻常的要求。如果我坚持下去(我会真的回过头来确保它确实是一个要求)我会这样设计:

Make a regular foreign key from table a to table b with a the parent and b the child.

使用父项和b表示从表a到表b的常规外键。

Add a trigger to table a that inserts a record to table b if one does not exist when a table a record is inserted. Add another trigger to table b that deletes the table a record if the last related record in table b is deleted.

向表a添加触发器,如果在插入表记录时不存在记录,则将记录插入表b。向表b添加另一个触发器,如果删除表b中的最后一个相关记录,则删除该表。

ALternatively, you could put the inserts to both tables ina stored proc. Remove all insert rights to the table except through the proc. YOu would still need the foreign key relationship from tablea to table b and the trigger on table b to ensure that if the last record is deleted the table a record is deleted. But you could do away with the trigger on table a in this case.

或者,您可以将插入放在存储过程中的两个表中。除了proc之外,删除表的所有插入权限。你仍然需要从tablea到表b的外键关系和表b上的触发器,以确保如果删除了最后一条记录,则删除一条记录。但在这种情况下,您可以取消表a上的触发器。

I would use the first scenario unless there is information in table b that cannot be found from the trigger on table a, say one or more required fields that don't have a value you can figur eout form table a.

我将使用第一个场景,除非表b中的信息无法从表a上的触发器中找到,比如一个或多个必需字段没有值,您可以形成表格a。

#5

I would put the inserts into a proc: disable the constraints, insert the data, enable the constraints. You may need to make sure that this is the only transaction going on whilst the constraints are disabled though.

我会将插入到proc中:禁用约束,插入数据,启用约束。您可能需要确保这是在禁用约束时进行的唯一事务。

That could be acheived by making the isolation level SERIALIZABLE, but that in turn could massace your concurrency.

这可以通过使隔离级别SERIALIZABLE来实现,但这反过来可能会影响你的并发性。

Kev

#1

TableA (TableA_ID int identity PK, Value varchar(10))
TableB (TableB_ID int identity PK, Value varchar(10), TableA_ID not null)