Re: Are redundant fields ever appropriate?

From: <paul.vernon_at_ukk.ibmm.comm>
Date: Wed, 19 Dec 2001 18:34:51 +0000
Message-ID: <9vqmo4$t26$1@sp15at20.hursley.ibm.com>

Way to go Joe!

And if we're talking relations rather than tables it gets worse. E.g.
with RelVar having four columns (i, a, b, c) where i is an identity column
then

INSERT INTO RelVar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO RelVar (a, b, c) VALUES ('a1', 'b1', 'c1');
INSERT INTO RelVar (a, b, c) VALUES ('a2', 'b2', 'c2');

is not the same as

INSERT INTO RelVar (a, b, c)
VALUES ('a1', 'b1', 'c1')
, ('a1', 'b1', 'c1')
, ('a3', 'b3', 'c3')

The latter is trying to insert something that is not a relation as it has
duplicate rows, so in a proper relational system would fail.

And doing

INSERT INTO RelVar (a, b, c)
SELECT e, f,g FROM
TABLE(
VALUES (1, 'a1', 'b1', 'c1')
, (2, 'a1', 'b1', 'c1')
, (3, 'a3', 'b3', 'c3')
) AS (i, e, f, g)

would only insert two rows if SQL acted relationally.

I've never seen what's wrong with using
RANK() OVER(ORDER BY incomming_key) + (SELECT MAX(i) FROM RelVar)
to generate surrogate keys

Regards
Paul Vernon

Received on Wed Dec 19 2001 - 12:34:51 CST