Auto Increment FK how does it update?

Question

Ok so i am currently developing in Oracle 11G express edition for a college assignment. I have ran into an issue on how to auto increment and update in the following parent table. So i have an address table and then a city table for instance. Here is the address SQL code

create table address(
addressid int primary key,
cityid int,
countyid int,
streetnameid int,
postcodeid int,
doornumid int,
natid int,
foreign key (doornumid) references doornum,
foreign key (postcodeid) references postcode,
foreign key (streetnameid) references streetname,
foreign key (countyid) references county,
foreign key (cityid) references city,
foreign key (natid) references nat);

So as you can see I am referencing the city table as a foreign key and this is the city SQL table code below:

create table city(
cityid int primary key,
city varchar(45));

The city code is using a sequence to auto increment when stuff is inserted it:

create SEQUENCE seq_cityID
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 11;

So simply I am auto incrementing all inputs by a cityid like this:

INSERT INTO city (cityid, city)
values(seq_cityID.nextval, Oxford);
INSERT INTO city (cityid, city)
values(seq_cityID.nextval, Oxford);

But my issue is that when I am referencing this in its parent table e.g. the address table how do i reference the ID for that row of data and make sure that the correct ID is pulled without having to manually type it in?

INSERT INTO address (addressid, cityid, countyid, streetnameid, postcodeid, doornumid, natid)
values(seq_addressID.nextval, seq_cityID.nextval, seq_countyID.nextval, seq_streetnameID.nextval, seq_postcodeID.nextval, seq_doornumID.nextval, seq_natID.nextval);
INSERT INTO address (addressid, cityid, countyid, streetnameid, postcodeid, doornumid, natid)
values(seq_addressID.nextval, seq_cityID.nextval, seq_countyID.nextval, seq_streetnameID.nextval, seq_postcodeID.nextval, seq_doornumID.nextval, seq_natID.nextval);

This is the simple insert into address which is only currently referencing nextval but i do not believe it will pull that row of data from that ID. How do i effectively pull through that ID from the child table and have it correctly into the parent automatically?

You would have to do a lookup on the city to get the ID, so use a select to obtain the values to insert into your table going to the city table to get the ID — Brad, Mar 14 '19 at 18:56
you would usually have a user interface that gets that data for you, and you would just input the appropriate values for the new record. if you're doing the SQL by hand, you do what @brad — thatjeffsmith, Mar 14 '19 at 18:58

score 0 · Answer 1 · answered Mar 14 '19 at 19:29

0

You have a misconception about tables and their relations.

Your datamodel has one table for all city names, one for all street names, one for all door numbers, etc. But why would you have a table that contains door numbers? What does it tell you? Door numbers are no entity by themselves; they belong to a street. And streets belong to cities. It would make no sense to find all people who live in some number 12 or in some Main Street.

One possible data model:

country (country_id, country_name, country_code)
  pk country_id

county (county_id, county_name)
  pk county_id
  fk country_id -> country

city (city_id, city_name, postcode, county_id)
  pk city_id
  fk county_id -> county

street (street_id, street name, city_id)
  pk street_id
  fk city_id -> city

address (address_id, street_id, door_number
  pk address_id
  fk street_id -> street

With this data model we can check for consistency. If we want to enter the address 111 Millstreet, Oxford, Italy, the database will tell us there is no Oxford in Italy. We can also easily find addresses in the same street (and not like "there are five million addresses in Main Street", but "800 addresses in Main Street, Ohio").

If you want to insert a new address, you look up the country, then the county, etc. until you get to the street ID.

answered Mar 14 '19 at 19:29

Thorsten Kettner

89,309
7
49
73

Yes this makes sense but you see the task is based on normalisation maybe i should have made this more clear - we have to eliminate all repeating groups of data that could happen, but i want to know if it possible to complete the original query. – Joseph Schuller Mar 14 '19 at 21:12
So it seems, you haven't understood yet what normalization is about. The tables I am showing are normalized. There is no redundant data. The tables you are showing are somewhat over-normalized. You separate what belongs together. It's kind of like saying "I don't store strings, because I want my data to be atomic, so I'll rather store the single characters". I've added another answer, though, to show you how to do parent / child inserts. – Thorsten Kettner Mar 15 '19 at 14:55
Yes i understand this and this is 3NF what we are looking at and completely agree this is stupid but I want to know simply how you can reference an auto incremented number as a foreign key instead of writing Nextval – Joseph Schuller Mar 15 '19 at 16:25
Well, with your tables, the idea would be that when you want to enter a New York address, you select the ID for New York from the city table and use this ID in your address insert. If New York is missing from the city table, you insert it first, and then you select its ID for your address insert (or use PL/SQL with the returning clause as shown in my other answer). – Thorsten Kettner Mar 15 '19 at 17:26
After all, you would normally not enter an address with a database tool like Toad or SQL Developer, but write an app (or Website) instead. So the person using your app would normally select the city from a combobox, and the app would know the city ID then of course. Your app can also allow to add a city and would then probably load the city list anew or add the one city to the already loaded list. – Thorsten Kettner Mar 15 '19 at 17:31

score 0 · Answer 2 · answered Mar 15 '19 at 14:53

With your original tables:

declare
  v_countyid     integer;
  v_cityid       integer;
  v_streetnameid integer;
begin
  insert into city (city_name) values ('Saint Petersburg') returning cityid into v_city_d;
  insert into streetname (street_name) values ('Park Drive') returning streetnameid into v_streetnameid;
  ...

  insert into address (cityid, streetnameid, ...) values (v_cityid, v_streetnameid, ...);

  commit;
end;

But then, there would be duplicate cities, street names, etc. in the tables. So we'd rather:

declare
  v_countyid     integer;
  v_cityid       integer;
  v_streetnameid integer;
begin
  select cityid into v_cityid from city where city_name = 'Saint Petersburg';

  if cityid is null then
    insert into city (city_name) values ('Saint Petersburg') returning cityid into v_city_d;
  end if;

  select streetnameid into v_streetnameid from streetname where street_name = 'Park Drive';

  if v_streetnameid is null then
    insert into streetname (street_name) values ('Park Drive') returning streetnameid into v_streetnameid;
  end if;
  ...

  insert into address (cityid, streetnameid, ...) values (v_cityid, v_streetnameid, ...);

  commit;
end;

But as mentioned in my other answer. This data model doesn't make much sense anyway.

Auto Increment FK how does it update?

2 Answers2