ORACLE: Verification that a related record exists

[cjard]

hello.
#
Suppose i have a record describing someone's personal details and much of it is kept in several database tables:

Name
Age
Bank Account Number
Bank ID
Home Store ID
Language Spoken
Customer Privilege Level

the ones in italics are per individual basis, but it might make sense to normalise the database and split others into separate tables, with ID numbers..
Bank ID
1 = NatWest Bank
2= HSBC
3 = Lloyds TSB

and so on for banks, home store ID, languages etc

so a particular record might look like:

john smith,30,12345678,1,HS7,826,gold


i dont want to insert bad data, with unrelated records, so i must check that the following exists:

there is a Bank with ID 1,
a home store with ID HS7,
a language with ID 826,
a privilege level with ID gold


currently it looks like this in PL/SQL:

SELECT count(*) INTO numRec FROM tbl_bank_details WHERE bank_id_number = :some variable:
IF numRec <> 1 THEN
--some error message output code here
ENDIF

somevariable is altered in the plsql, so if i have 100 counts to add, this sql is run 100 times. so are the other 57 sqls that are used for validation.


this is slow, and im looking for a way to make it faster. all the data to be inserted is in a table already, so how can i easily crosscheck it and report meaningful error messages, in a fast way?

[exterminator]

If the database is normalized then is it that the referential integrity constraints were not applied? It's not clear to me if you want to normalize or had a normalized database but with no relationships built between the tables (the constraints) and that now you want to put those integrity constraints.

If the database has got the relationships between the tables well defined with primary key - foriegn key constraints then you would not need to check for the existence of data in a bank table to find out if there is a valid record for a bank when you are doing an insert in the original table with the (name, age, bank id etc, i don't find the name of this table). They would be automatically handled by the database system and will throw excepetions on invalid insert statements. Regards.