0

I have a number of SQL tables that I need to update with new information. I am using Python and ODBC to make a script that automatically updates all of the tables. My original idea was to just truncate each table and reload them using the updated source file, but due to the foreign key constraints that they have I am unable to truncate them. I tried turning of foreign key constraints but it still won't allow me to truncate.

I am looking to find what the best way to update these tables would be now. I have the source data in SQL files that I am loading into a pandas dataframe, and I have no idea which data will change (if any changes at all) on each update of the source file, it could add lines, remove lines, or just change a few quantities. Does anyone have any advice or recommendations for this scenario?

Zoe
  • 27,060
  • 21
  • 118
  • 148
Jadon Latta
  • 147
  • 1
  • 11
  • 1
    You can't truncate because of the foreign keys. You would either have first drop the foreign keys, then upload your data, recreate the foreign keys(fixing any rows that violate the foreign key). Or you could delete all the rows in the tables, you will have to figure out the order of those because the foreign keys will prevent deletes, and then insert all the data (same issue on the order of inserts). – Sean Lange Aug 08 '22 at 19:45
  • @SeanLange Thanks, I didn't realize delete would work, I was kind of assuming that it would also be prevented by the FKs like Truncate is. I will just use that, I believe that my data is organized in such a way I can just order the deletes and inserts correctly. – Jadon Latta Aug 08 '22 at 19:56
  • 3
    `Truncate` can potentially be quicker than `Delete` for large tables - so depends on your table sizes whether or not it would be worth going through the extra hassle of dropping the FKs and recreating them afterwards. – Martin Smith Aug 08 '22 at 19:59

1 Answers1

0

As noted in this answer, we can turn off FK checking with

ALTER TABLE tableName NOCHECK CONSTRAINT ALL

and re-enable it with

ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL

Testing shows that this does not allow us to do TRUNCATE tableName or DROP TABLE tableName, presumably because the code for those operations simply checks whether an FK exists, and not whether it is currently being enforced. However, it does allow us to do DELETE FROM tableName

# fmt:off
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
from sqlalchemy.exc import IntegrityError
# fmt:on

engine = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql_199")
meta = MetaData()

# example environment
parent = Table(
    "parent",
    meta,
    Column("id", Integer, primary_key=True, autoincrement=False),
    Column("name", String(50)),
)
child = Table(
    "child",
    meta,
    Column("id", Integer, primary_key=True, autoincrement=False),
    Column("name", String(50)),
    Column("parent_id", Integer, ForeignKey("parent.id")),
)
meta.drop_all(engine, checkfirst=True)
meta.create_all(engine)

# example data
with engine.begin() as conn:
    conn.execute(parent.insert(), dict(id=1, name="parent_1"))
    conn.execute(child.insert(), dict(id=11, name="child_1", parent_id=1))

# try delete without disabling FK check
with engine.begin() as conn:
    try:
        conn.exec_driver_sql("DELETE FROM parent")
    except IntegrityError as e:
        print(e)
        # (pyodbc.IntegrityError) ('23000', '[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "FK__child__parent_id__7B5B524B". The conflict occurred in database "test", table "dbo.child", column \'parent_id\'. (547) (SQLExecDirectW); [23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)')

# try disabling FK check
with engine.begin() as conn:
    conn.exec_driver_sql("ALTER TABLE child NOCHECK CONSTRAINT ALL")
    conn.exec_driver_sql("DELETE FROM parent")
    conn.exec_driver_sql(
        "INSERT INTO parent (id, name) VALUES (1, 'new_parent_1')"
    )
    conn.exec_driver_sql("ALTER TABLE child WITH CHECK CHECK CONSTRAINT ALL")
    # (no errors)

# verify results
with engine.begin() as conn:
    print(conn.exec_driver_sql("SELECT * FROM parent").all())
    # [(1, 'new_parent_1')]
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418