1

I checked the answer given in this SO question , to drop constraints whose names are not known.

  1. In SQL Server, can we assume they will always be in the same format given? (FK__TABLENAME__KEYPREFIX)?
  2. What does T(c) define?
  3. If two such constraints need to be dropped from the same table, is the following correct?

DECLARE @sql1 NVARCHAR(MAX);

DECLARE @sql2 NVARCHAR(MAX);

SELECT 
    @sql1 = c
FROM
    (SELECT 
         'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME + '; '
     FROM 
         INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     WHERE 
         TABLE_NAME = 'DOC_INVS_1'
         AND CONSTRAINT_NAME LIKE 'FK__DOC_INVS___kntr%') T(c);

SELECT 
    @sql2 = c
FROM
    (SELECT 
         'ALTER TABLE DOC_INVS_1 DROP CONSTRAINT ' + CONSTRAINT_NAME + '; '
     FROM 
         INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
     WHERE 
         TABLE_NAME = 'DOC_INVS_1'        
         AND CONSTRAINT_NAME LIKE 'FK__DOC_INVS___aaaa%') T(c);

EXEC(@sql1);
EXEC(@sql2);

Note

The actual end goal is to add a 'CASCADE ON DELETE' to a foreign key, which had been added to the wrong key. I learned that you can't just add the cascade option by altering, so the approach is to drop both and then create again.

Fmanin
  • 519
  • 1
  • 12
  • 25
S.Dan
  • 1,826
  • 5
  • 28
  • 55
  • 4
    No - never assume anything. If you want specific types of constraints you need to identify them via the correct columns in the system tables. "T(c)" is simply an alias for the derived table (T) and an alias for the single column it contains (c). Poorly chosen but many don't put much effort into writing readable, understandable code. – SMor Feb 24 '19 at 13:34
  • I will add that it appears you want drop just foreign keys. Don't assume a table will not have other constraints that you **do not** want to drop. And if you want to know if it is "correct", try it and see what happens in a test database. Make sure you have a backup whenever you attempt something that changes the state of a database. – SMor Feb 24 '19 at 13:35
  • Is it possible to drop all the constraints of a table, without knowing their names or 'guessing' their name patterns? – S.Dan Feb 24 '19 at 13:36
  • Yes - just search the internet. Which is the first thing you should do whenever you have a question. Look at the definition of the system tables you are using. – SMor Feb 24 '19 at 13:39
  • 2
    And, of course, it's best practice to always explicitly name your constraints, so you wouldn't find your self in this situation again. – Zohar Peled Feb 24 '19 at 13:43

0 Answers0