Axel Rau wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Am 22.09.2005 um 22:26 schrieb Daryl Richter:
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):
[snip]
Networks change during time, being diveded or aggregated or you just
enter wrong data during insert.
With the UPDATE below, I want to correct the addresses to again point at
the right net. While writing this,
I learn that because of the pk in network, UPDATEs will be difficult to
accomplish (you may need a temporary net
to park all addresses of a network to be divided, make the change in
network and use the UPDATE below to
adjust A.network. I use net '0.0.0.0/32' as 'UNKNOWN' net or for parking.
I think if you provide some sample data we can figure this out.
Yes, this a goof idea. Playing with small tables let you find quickly
the right query. Lets start over with a slightly bigger
collection of data:
insert into network( id ) values( '10.1/16' );
insert into network( id ) values( '10.2/16' );
insert into network( id ) values( '10.3/16' );
insert into address( id, network ) values( '10.1.0.1', '10.1/16' );
insert into address( id, network ) values( '10.1.0.2', '10.1/16' );
insert into address( id, network ) values( '10.1.0.3', '10.1/16' );
insert into address( id, network ) values( '10.1.0.4', '10.2/16' );
insert into address( id, network ) values( '10.1.0.5', '10.2/16' );
insert into address( id, network ) values( '10.1.0.6', '10.3/16' );
insert into address( id, network ) values( '10.200.0.6', '10.3/16'
); -- address not in network
insert into address( id, network ) values( '10.200.0.7', '10.3/16'
); -- address not in network
But those are bad inserts, right?
I think that I now see what the problem is --> Why do you have a network
table at all? It's redundant. If you just insert the ids into your
address table, don't the new PostgreSQL operators give you all the
information you need?
So, for example, if I inserted the data above and then want to answer
the question, "What are all my 16-bit subnets?"
select distinct network( set_masklen( id, 16 ) ) from address;
network
-------------
10.1.0.0/16
10.2.0.0/16
10.200.0.0/16
[rest snipped]
--
Daryl
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
