On Wed, 9 Feb 2011 18:12:32 +0000, "Black, Michael (IS)"
<[email protected]> wrote:
>I have a need to create a unique bi-directional relationship.
>You can think of it as pairings of people who eat dinner together.
>
>create table t(i int, j int);
>
>insert into t(1,2);
>insert into t(2,1); << should give an error because the pairing of 1-2 already
>exists.
>insert into t(3,2); << OK
>insert into t(3,1); << OK
>insert into t(1,3); << should be error
>
>You can't guarantee that one column is less than the other so there's no win
>there.
There are just three cases:
- i < j
- i > j
- i and j are the same.
If j < i just swap the values.
>Speed is of the utmost concern here so fast is really important (how many ways
>can I say that???).
>
>Is there anything clever here that can be done with indexes or such?
This is Darren Duncan's solution in SQL:
CREATE TABLE t (
i INTEGER
, j INTEGER
, CONSTRAINT key_order CHECK (i <= j)
, PRIMARY KEY (i,j)
);
CREATE VIEW v AS
SELECT i,j FROM t;
CREATE TRIGGER i
INSTEAD OF INSERT ON v
FOR EACH ROW
BEGIN
INSERT INTO t (i,j)
VALUES (
CASE WHEN NEW.i > NEW.j THEN NEW.j ELSE NEW.i END
, CASE WHEN NEW.i > NEW.j THEN NEW.i ELSE NEW.j END
);
END;
-- update triggers left to your imagination.
INSERT INTO v (i,j) VALUES (1,2);
INSERT INTO v (i,j) VALUES (2,1);
-- Error: near line xx: columns i, j are not unique
INSERT INTO v (i,j) VALUES (3,2);
INSERT INTO v (i,j) VALUES (3,1);
INSERT INTO v (i,j) VALUES (1,3);
-- Error: near line yy: columns i, j are not unique
INSERT INTO v (i,j) VALUES (4,4);
SELECT i,j FROM t ORDER BY i,j;
1|2
1|3
2|3
4|4
I know you're not fond of triggers because of speed.
I don't think this construct will hurt much in this case.
You'll have to benchmark it.
HTH
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
