0

I have a zipcode table and a shelter table in my database. I would like the ShelterCity, ShelterState and ShelterZip columns in the shelter table to reference the City, State, and ZipCode columns in the zipcode table. I have the ZipCode column (the PK in the zipcode table) as a foreign key in the shelter table, but cannot seem to figure out how (or if it's even possible) to add the other zipcode columns so that they update when I type a zipcode into the shelter table.

When I tried to add the ShelterCity, ShelterState and ShelterZip as foreign keys that reference the City, State, and ZipCode columns, I get an error message that looks like this: Error Code 1215. Cannot add foreign key constraint. The code that I used to attempt to add these foreign keys is as follows:

ALTER TABLE shelter ADD FOREIGN KEY (ShelterCity) REFERENCES zipcode(City);

What am I doing wrong? Thank you in advance!

Anakela
  • 65
  • 7
  • Take a look at [JOIN](https://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join/6188334#6188334). You'll need some sort of id field in each table that acts as a merge point when you perform the join, but that should at least get you moving in the right direction, and should keep your data concise, rather than duplicating data in two different places. – depwl9992 Sep 23 '16 at 23:54
  • As for update with a join pattern see http://stackoverflow.com/q/15209414/1816093 – Drew Sep 24 '16 at 00:01
  • You question has a few parts to it I think but it is vague. Not sure if you want an auto update like a trigger / how to update join / how to establish an FK and avoid a 1215 error. In any case maybe this answer here of mine about the 1215: http://stackoverflow.com/a/39332173 – Drew Sep 24 '16 at 00:25
  • You don't need foreign keys to do this. If you want to introduce those, then your data must be clean and match perfectly. – tadman Sep 24 '16 at 00:27
  • So, in order to join the data in the two tables, I'll have to enter the city, state, and zipcode into both tables, and then just make sure that the corresponding columns match perfectly so that when I `JOIN` them, they will match? Wouldn't that just be me entering data twice? Sorry, I'm new at this! – Anakela Sep 24 '16 at 01:10

1 Answers1

3

For starters, the structure of your tables would be...

zipcode table
-------------
City
State
Zip

shelter table
-------------
ShelterZip
Other Fields

You do NOT need ShelterCity and ShelterState fields. ShelterZip would reference Zip.

A simple query would be

SELECT City, State, Zip 
FROM shelter 
INNER JOIN zipcode ON shelter.ShelterZip = zipcode.Zip 
WHERE ShelterZip = 12345

or

SELECT z.City, z.State, z.Zip 
FROM shelter s
INNER JOIN zipcode z 
ON s.ShelterZip = z.Zip 
WHERE s.ShelterZip = 12345

I also would suggest you read up on relational database design. One source is http://www.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html

Drew
  • 24,851
  • 10
  • 43
  • 78
Tim Dearborn
  • 1,178
  • 7
  • 18
  • 1
    For readability and debugging, refer to the tables in the `ON` clause – Drew Sep 24 '16 at 02:23
  • 1
    When the first line gets long and you have multiple join tables (some with long names) and common column names, the table aliases (short ones like letters) make it pretty apparent and no uber long lines – Drew Sep 24 '16 at 02:28
  • Thanks for your help, Tim, and for the link. Unfortunately, I'm still not getting the results I was looking for, but perhaps I am not asking the right questions. I'll keep trying to figure this out. :) **EDIT**: Oh, I think I'm getting it now. That link helped, for sure. Thanks again! – Anakela Sep 24 '16 at 06:22