Thank you for responding, Daryl,
Am 22.09.2005 um 16:45 schrieb Daryl Richter:
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?
The fk requires a corresponding row in network. But my update tries to
reference the right network, that one where the ip address belongs to.
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" ...
I'm using 8.0.3 and there are some new operators related to inet and
cidr data types.
On page 157, I found "<<" as address/network "is contained in" network.
Finding the net where an address belongs to works as:
SELECT id FROM network WHERE inet '$p_ipSource' << id;
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
