問題描述
將外鍵添加到現有數據庫 (Adding foreign keys to an already existing database)
I'm trying to export data from an Excel spreadsheet into a fairly complex relational database. The spreadsheet indicates "foreign keys" by stating the names of other objects. (Luckily, I have some control over the spreadsheet, so I can guarantee these names are unique AND that the objects they reference actually exist).
I have a program that can recreate these tables in a MSSql database, but it can't automatically link them to each other. Besides, I don't want to use the actual names of the objects as the primary key since eventually the database will be large.
So, if I have many existing but unconnected tables which refer to each other by their "name" fields, how can I add a foreign key that links them by their IDs?
A simplified version of what I have:
Parent
ID: 1 (PK)
Name: Mary
Child
ID: 2 (PK)
Name: Jane
ParentName: Mary
And what I want to achieve:
Child
ID: 2 (PK)
Name: Jane
ParentID: 1 (FK)
Thanks for any help! I wasn't able to find an example of how to add a foreign key mapping after the fact, or on a different field.
‑‑‑‑‑
參考解法
方法 1:
See the ALTER TABLE syntax for MSSQL. You can come up with something like this to add the constraint to the table:
ALTER Child
ADD CONSTRAINT Child_Parent_FK FOREIGN KEY (ParentID) REFERENCES Parent(ID)
Then once the constraint is in, try something like:
UPDATE Child
SET ParentID = (SELECT ID FROM Parent WHERE Name = ParentName)
That should work if you can guarantee the Name of the Parent is unique. Otherwise you can add LIMIT 1 to the end of the query. But if there are multiple Parents with the same Name, you're going to need to add extra logic (which isn't specified in your original post).
方法 2:
Since you're going to be doing this regularly, I think you should import into a staging table. I like to isolate staging tables in their own schema.
Use the staging table to retrieve or generate the keys you need, then insert/update your OLTP tables based on the data in the staging table. Finally, truncate the staging table.
(by Lisa Wray、Dirk、Mike Sherrill 'Cat Recall')