Referential Integrity In Mutiple Tables. Need Help.

I have a lookup table called States, I have multiple other tables that use this lookup table, but I can only relate to one other table. My question is if I change a state name how do I enforce that change to the other tables that CANT be related? I know it is a design flaw and need some kind of joining table. I am having difficulty understanding and incorporating first normal form in my database. Here is what i have so far.

Example:

Table 1
========|============|=============|========|
CustomerID BillingAddressID ShippingAddressID OtherFields
========|============|=============|========|

Table 2
===========|======|========|
BillingAddressID StateID OtherFields
===========|======|========|

Table 3
=============|======|========|
ShippingAddressID StateID OtherFields
=============|======|========|

Table 4
======|====|
StateID Name
======|====|

How do I relate table 4 with table 2 & 3 for referential integrity? Or create a joiner table?You have it already...you just need to enforce the relationship with a FOREIGN KEY CONSTRAINT

And please don't tell me that StateID is an entity column...|||I can only relate table 4 with either table 2 or 3 but not both. what do you mean by entity table? Excuse my ignorance.

Thanks|||I'm not sure I follow you...

USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE Table4 (
StateID int PRIMARY KEY
, [Name] varchar(50)
)

CREATE TABLE Table2 (
BillingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table2 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)

CREATE TABLE Table3 (
ShippingAddressID int PRIMARY KEY
, StateID int
, OtherFields int
, CONSTRAINT FK_Table3 FOREIGN KEY (StateID) REFERENCES Table4(StateId)
)
GO

INSERT INTO Table3(ShippingAddressID, StateID) SELECT 1,1
INSERT INTO Table2(BillingAddressID, StateID) SELECT 1,1
GO
INSERT INTO Table4(StateID, [Name]) SELECT 1, 'New Jersey'
GO
INSERT INTO Table3(ShippingAddressID, StateID, OtherFields) SELECT 1,1, 1
INSERT INTO Table2(BillingAddressID , StateID, OtherFields) SELECT 1,1, 1
GO

SELECT 'Table3' AS Source, l.ShippingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table3 l
JOIN Table4 r
ON l.StateID = r.StateID
UNION ALL
SELECT 'Table2' AS Source, l.BillingAddressID, l.StateID, l.OtherFields, r.[Name]
FROM Table2 l
JOIN Table4 r
ON l.StateID = r.StateID
GO

SET NOCOUNT OFF
DROP TABLE Table2
DROP TABLE Table3
DROP TABLE Table4
GO|||Oh, I meant IDENTITY Column by the way...not Entity...sorry|||why wouldn't StateID be an identity column?

are you perhaps suggesting that a natural key like NJ would be more suitable?

better hide, because all the surrogate key fanatics are gonna come gunnin' for ya...|||Hey...it's their perogative...let them join till the cows come home...

And there are times for it...but not as many as it's abused...

Here's a question. Would you use an identity column for a phone number? SSN? Stock Market symbol?|||Would you use an identity column for a phone number? SSN? Stock Market symbol?me? never

you must have me confused with those surrogate key fanatics, who blindly require an identity column in every table...

i know a guy on, ahem, another forum web site, who once took a poll on the subject

see Surrogate Keys.The Devils spawn (OK Not really) (http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx)