Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    73

    Question Unanswered: 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?
    Last edited by X-Centric; 10-12-04 at 11:22.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    May 2004
    Posts
    73
    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

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure I follow you....

    Code:
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh, I meant IDENTITY Column by the way...not Entity...sorry
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    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 Devil’s spawn (OK Not really)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •