Anakela Anakela - 2 months ago 8
MySQL Question

Can I use one table to update information in another table?

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!

Answer

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

Comments