Axel Rau wrote:
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.
I'm still not understanding what you're trying to do, perhaps its a
language issue. :) Let me try again.
I built your schema and inserted some rows:
insert into network( id ) values( '10.1' );
insert into address( id, network ) values( '10.1.0.1', '10.1' );
insert into address( id, network ) values( '10.1.0.2', '10.1' );
insert into address( id, network ) values( '10.1.0.3', '10.1' );
I then select from network:
id
-----------
10.1.0.0/16
and from address:
id network
-------- -----------
10.1.0.1 10.1.0.0/16
10.1.0.2 10.1.0.0/16
10.1.0.3 10.1.0.0/16
Why do you now want to update address.network? They are already
pointing to the right network, aren't they?
I think if you provide some sample data we can figure this out.
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;
Ahh, ok. see above.
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
Daryl
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
