Dear SQL'ers:

How do I force a condition based on multiple rows? I want the
necessary rows to be entered in a batch. As a simple example, what
would be the coding of the constraint in this case following?

create table Symmetric
(
a int not null,
b int not null
)

Constraint: If (x,y) is in Symmetric, then (y,x) is also in
Symmetric.

Anyone adding rows would have to do it in a batch:
insert into Symmetric
(a,b)
values
(3,5),
(5,3)

I am hoping that there is a simple answer that I am overlooking.
Right now, all I can think of is to scan both Symmetric and the batch
(Inserted).

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
Generally, you need to use a trigger.


Assuming that (a, b) is the primary key, this can be enforced with a
constraint:

CREATE TABLE Symmetric (
a int NOT NULL,
b int NOT NULL,
CONSTRAINT pk PRIMARY KEY (a, b),
CONSTRAINT fk FOREIGN KEY (b, a) REFERENCES Symmetric (a, b)
)



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Tue, 12 Jul 2011 23:55:15 +0200, Erland Sommarskog
<esquel (AT) sommarskog (DOT) se> wrote:

I figured that was probably so, but wanted to check. And your
response to my example is a nice specific case where it is not needed.

That is elegant for the case I gave as an example. I am,
however, looking for the more general approach.

Giving another example, suppose it must be that there are an odd
number of rows of a type of entry.

create table EvenSteven
(
pk varchar(10) not null,
contrivance int not null
)

In this case, there must be an odd number of rows for each value
of contrivance. Suppose we have
pk contrivance
A 5
B 7
C 6
D 5
E 5
F 5

Adding
G 6
H 7
would be an error (2 6's in total), but adding
G 6
H 7
I 6
would be fine.

Must I scan through the table and Inserted for each contrivance
value, or is there a better way?

Sincerely,

Gene Wirchenko

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
In this case you will need a trigger. As for how to implement the trigger,
you could assume that the table is OK, and only count the values in inserted
and deleted. But this means that if someone disables the trigger, or
bypasses it with BULK INSERT, that you will never be able to correct the
data again, why it may be better to look at all instances of the values in
the table.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

A simple/simpler solution would be to always sort the values. In other
words, to enforce that A is always lower or equal to B, both in the
table and whenever you do inserts. That way you don't need two rows for
each pair. In the table, this can be enforced with CHECK (a <= b)

--
Gert-Jan



Gene Wirchenko wrote:

On Wed, 13 Jul 2011 19:39:44 +0200, Gert-Jan Strik
<sorrytoomuchspamalready (AT) xs4all (DOT) nl> wrote:

You are over-optimising. My example was an example of other rows
having to be checked, not the only such case.

Sincerely,

Gene Wirchenko