Change foreign the key relationships to reference one master record? RRS feed

  • Question

  • I am trying eliminate redundancy in table.  How do I change the foreign the key relationships to reference one master record?

    This is my Table A:

    Now I want to update Table A to have one master record and have the foreign key relationships of records 9001-9008 point the one master record in the table, as seen below.  Please advise.

    • Edited by D Henson Saturday, August 8, 2020 9:14 PM
    Saturday, August 8, 2020 9:14 PM


All replies

  • It seems to be that you would need a second table with the child rows. I don't know the full story, but outlined from what you posted that would be:

    CREATE TABLE ChildIds (
        MasterID     int NOT NULL,
        ChildID      int NOT NULL,
        CONSTRAINT pk_ChildIds PRIMARY KEY (ChildID),
        CONSTRAINT fk_ChildId_MasterTtable FOREIGN KEY(MasterID)
             REFERENCES MasterTable(Insurance_ID)

    Then again, I am not entirely sure that this is the correct solution. Judging from the data, it might be better to move the columns right of Insurance_ID column to an InsuranceCompany table, and replace those columns with an InsuranceCompany_ID column.
    I need to add that it is impossible to make a database design with this small input, so this is quite a bit of guess work.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, August 8, 2020 10:13 PM
  • The the story is data in Insurance_Company_Name, Address 1, Address 2, City, State, Zip came in several formats.  Instead of a fuzzy match, I did a one time update on Insurance_Company_Name, Address 1, Address 2, City, State, Zip to the correct format.  Now (from example above) we do not need multiple records with same data.   We want to point all the previous foreign key relationships 9001-9008 to 9000, since this will be the only record in the table. 
    • Edited by D Henson Sunday, August 9, 2020 1:15 AM
    Sunday, August 9, 2020 1:15 AM
  • Do you want to simply remove all same rows from the table leaving the row with the smallest ID?

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    Sunday, August 9, 2020 3:19 AM
  • Hi D Henson,

    Please refer below links and check whether they are helpful.

    Deleting Related Rows in a Many-to-Many Relationship

    SQL Server: Foreign Keys with cascade delete



    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    • Proposed as answer by DougJ1022 Monday, August 10, 2020 8:57 AM
    • Unproposed as answer by DougJ1022 Monday, August 10, 2020 9:00 AM
    • Marked as answer by D Henson Monday, August 10, 2020 1:13 PM
    Monday, August 10, 2020 6:19 AM
  • Where is the DDL for this table? Why did you just post pictures? If you're working in a place where they make you program from pictures instead of DDL, it's time to update your resume and quit.

    You talk about having foreign key references, but since we only have pictures and no DDL, I don't see a references clause or any DRI actions. In a properly designed table, the primary key constraint would prevent duplicate rows, so by definition (this is not an option!!) You don't have a table at all. You got a spreadsheet! Would you like to try again, following netiquette in the forum?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, August 10, 2020 9:31 PM