April 23, 2009 at 8:35 pm
I have a main table books:
title
author
publisher
data published
category
isbn
price
owned
summary
I have other tables author, category, publisher. Each table has at least an id self incrementing column and a text column for the author, category or publisher. The publisher table has other columns for address, contact info, etc.
I use the books columns for the entry fields in the user interface. I would like to make sure that author, category and publisher are entered and that they exist in their respective tables. The storage value in the books (main) table is the id for the value in the ancillary table: ie. id value of 5 for author Bill Hamilton. That way I save storage space on books and the authors name only exists in one place.
The question I have is what is the best way to check the column to be verified? I know that there are things like foreign keys, but am not sure if this is what they are used for. I have written pop-up lookups for tables like state that I could do for this, but what is the best way to handle the SQL code?
Any help appreciated,
TH
April 28, 2009 at 12:01 pm
This is exactly what foreign key constraints are meant for. Foreign keys on the columns in books reference the corresponding primary key value in the ancillary tables. When a row is inserted into books, the values are checked against the referenced tables and the insert will fail if a value doesn't exist in the referenced table.
Greg
April 28, 2009 at 12:09 pm
Is this in the old "pubs" database? Sure sounds like it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 29, 2009 at 5:02 pm
Thanks for the feedback. As for the db being the old pubs version the answer is no, it is something that I am working on and starts with me.
Thanks
TH
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply