how to update table with foreign key

Question

Actually I have two databases, one is for staging one is in production. one table, for example called Class(class_id,descr,faculty_id) and the faculty_id reference to another table called Faculty(faculty_id, name,comment).

my story is that i do so much updates on the staging database, Faculty table, change lots of faculty comment and name without changing Class table. I want to copy the staging Faculty table to Production. My original way is to generate script from staging and delete the data table, Faculty in production and run the script in order to copy the staging data table Faculty to production. However i found i cannot delete the Faculty table because the foreign key. but i do not want to write down hundreds of update statement, and also cannot delete the Class data in production, how can i do if i want to copy the Faculty table to production?

What version of SQL Server, please.

RBarryYoung
– RBarryYoung

2012-10-19 20:17:32 +00:00
Commented Oct 19, 2012 at 20:17 — RBarryYoung
– RBarryYoung, Commented Oct 19, 2012 at 20:17

techBeginner · Accepted Answer · 2012-10-19 20:42:36Z

1

Temporarily delete the relation between class and faculty and do whatever and then again establish the relationship..

answered Oct 19, 2012 at 20:42

techBeginner

3,86813 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris · Accepted Answer · 2012-10-19 20:13:15Z

0

Try this:

Backup your class table that is holding foreign keys.

Truncate the class table.

Make your updates to Faculty.

Finally restore your class table.

answered Oct 19, 2012 at 20:13

Chris

4,1035 gold badges36 silver badges51 bronze badges

2 Comments

Rahul Tripathi Over a year ago

Is this a question or an answer? If its a question then plz write it as Comment!!

techBeginner Over a year ago

You can't truncate a table which has a foreign key.. In this case faculty_id in class table..

8kb · Accepted Answer · 2012-10-19 23:12:05Z

Let's assume that all the surrogate keys in staging Faculty are the same as those in production.

Copy Faculty table to Production as Faculty_Staging
Alter Class table and drop foreign key reference to Faculty
Rename Faculty to Faculty_backup
Rename Faculty_Staging to Faculty
Alter Class table and restore foreign key constraint

If the attempt to rebuild the foreign key reference fails then there are problems with your surrogate keys in your staging Faculty table.

Rename Faculty to Faculty_Staging
Rename Faculty_Backup to Faculty
Alter Class table and restore foreign key constraint.

Things to consider:

Do the surrogate keys in Faculty and Faculty_Staging match exactly?
Do the alternate keys in Faculty and Faculty_Staging match exactly?
Have you inadvertently deleted any production rows in Faculty staging?

Oxydel · Accepted Answer · 2012-10-20 05:26:24Z

Did you know you can Generate a SQL Script with SQL? (This only works if Faculty has same faculty_id in staging and production)

This will gen script to insert the new Faculties:

SELECT 'INSERT INTO production.Faculty (faculty_id, name, comment) 
        SELECT TOP 1 '+LTRIM(faculty_id)+','''+name+''','''+comment+'''
          FROM production.Faculty
         WHERE NOT EXISTS (SELECT 1 FROM production.Faculty WHERE faculty_id = '+LTRIM(faculty_id)+');' AS runMeOnProduction
  FROM staging.Faculty

This will gen script to update the Existing Faculties:

SELECT 'UPDATE production.Faculty SET name = '''+name+''', comment = '''+comment+'''
         WHERE faculty_id = '+LTRIM(faculty_id)+';' AS runMeOnProduction
  FROM staging.Faculty

if you get quotes in you name or comment fields you might want to replace name and comment in the SQL statements above with:

REPLACE(name,'''','''''')
REPLACE(comment,'''','''''')

It's not the best way especially if you get inserts and updates on both tables in both environments. But if the Data for Faculty is only growing in staging this works.

Also some querying software will show a blob or some hex when you run these SQL Statements because it thinks the string is to long to show, if you copy paste it, it might still come out correct. Otherwise try another SQL Query tool. I use DBVisualiser for this kind of thing and it works. I'm not adverting for any particular DB Query tool, it's just I have to query mySQL and MSSQL often so I find myself jumping from App to app in order to get the job done.

If there's Data entry in both environments you should consider using a proper Data Comparing tool : http://en.wikipedia.org/wiki/Comparison_of_database_tools And once you start using these you're question will probably be different.

This is probably wacky thinking but I've found it convenient before...

Collectives™ on Stack Overflow

how to update table with foreign key

4 Answers 4

Comments

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Related