SQL Check for indexes and constraints before dropping

Are you talking about the SQL Server, Oracle, MySQL, PostgreSQL, or some other RDBMS? The correct syntax may differ.

Anyway, you should not drop PK index but rather do the update in a correct way, i.e.. no duplicate keys creation during the update. And this is twice as valid when the PK is created as a clustered index. You may also create temp table containing the updated data (if necessary) and then delete all rows from the original table and then insert new rows (in a transaction). Number of ICD9 codes isn't so high so the whole batch should not be very slow..

It is SQL, do you have a rough example of the syntax for that?

Sorry, SQL server

Why you cannot leave indexes and constraints in place while updating the data?

You may do it the following way:

BEGIN TRY
  SELECT * INTO #LUICD9temp FROM LUICD9

  -- now update the #LUICD9temp table (it does not contain any index or constraint)

  -- and then you may copy the rows into original table:
  BEGIN TRANSACTION
    DELETE FROM LUICD9
    INSERT INTO LUICD9
      SELECT * FROM #LUICD9temp
  COMMIT TRANSACTION
  DROP TABLE #LUICD9temp
END TRY
BEGIN CATCH
  -- process errors here (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql?view=sql-server-ver15)
  IF @@trancount > 0
    ROLLBACK TRANSACTION
END CATCH

Select allOpen in new window

We have a distributed system and there are not supposed to be differences in the tables.  Some sites have errors with our update indicating that indexes have been added without our knowledge.  If this was local it would no be such a nightmare.  We are updating tables for hospitals that have to have new COVID19 codes.  Some sites work fine others don't with index errors.

This part

  -- and then you may copy the rows into original table:

If the constraints are still on the LUICD9 table, won't it still give errors on unknown indexes?

What we are doing is changing the field length of a table to allow for the new COVID19 codes, going from 6 to 8

Thanks!