Axel Rau wrote:
Hi SQLers,
I have a fk from address to network and try to update the foreign key
column to point at the network, "it belongs to":
CREATE TABLE network (
id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address'
)
CREATE TABLE address (
id inet PRIMARY KEY , -- 'PK of IPv4/6 host address'
network cidr NOT NULL -- 'FK to Network table'
REFERENCES network ON DELETE CASCADE ON UPDATE
CASCADE
)
I tried (using the WHERE clause to eliminate the addresses were no
corresponding net exists):
But you can't insert a row in address w/o a valid network.id? That's
what the fk ensures.
Perhaps you could elaborate more? Are you trying to *put* on the fk and
you currently have bad data?
UPDATE address
SET network = (SELECT N.id WHERE A.id << N.id)
FROM address A, network N
WHERE A.id << N.id;
This also makes no sense. For starters, << is "bitwise shift left" ...
But this ended up with all network columns pointing at the same net (-:).
Any help would be appreciated.
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Daryl
"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster