constraints

SQL Server: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

You cannot truncate a table which has an FK constraint on it. As workaround, you could:
- Drop the constraints.
- Trunc the table.
- Recreate the constraints.

Here it is the associated T-SQL script, supposing you have 2 tables called MyTable and MyReferencedTable:

-- Remove constraint
IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable')
BEGIN
        ALTER TABLE dbo.MyReferencedTable
        DROP CONSTRAINT FK_MyReferencedTable_MyTable
END


-- Truncate table
TRUNCATE TABLE dbo.MyTable


-- Re-Add constraint
IF NOT EXISTS(SELECT 1 FROM sys.foreign_keys WHERE name = 'FK_MyReferencedTable_MyTable')
BEGIN
        ALTER TABLE dbo.MyReferencedTable
        WITH CHECK ADD CONSTRAINT [FK_MyReferencedTable_MyTable] FOREIGN KEY(ListingKey)
        REFERENCES dbo.MyTable (ListingKey)
END

Drop a list of stored procedures whose names begin with a certain string

DECLARE @SPName AS NVARCHAR(128)
       
-- For each SP...
DECLARE MyCursor CURSOR
FOR
SELECT OBJECT_NAME(M.object_id) as SPName
FROM
        sys.sql_modules M
        INNER JOIN sys.objects O ON M.object_id = O.object_id
WHERE
        O.type_desc = 'SQL_STORED_PROCEDURE'
        AND OBJECT_NAME(object_id) like '<Prefix>%'

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @SPName

WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT 'Deleting SP ' + @SPName
        EXEC ('DROP PROCEDURE ' + @SPName)  
               
        FETCH NEXT FROM MyCursor INTO @SPName
END
CLOSE MyCursor
DEALLOCATE MyCursor

How to drop a list of tables, including the associated foreign key constraints

DECLARE
     @TableName AS SYSNAME
   , @RefTableName AS SYSNAME
   , @ConstraintName AS SYSNAME

-- For each table...
WHILE 1 = 1
BEGIN
   SET @TableName = NULL
   
   SELECT TOP 1 @TableName = name FROM sys.tables WHERE name LIKE '<TablePrefix>%'
   IF @TableName IS NULL
      BREAK;
       
      -- For each FK referring this table
      WHILE 1 = 1
      BEGIN
         SET @ConstraintName = NULL
                 
         SELECT
            @ConstraintName = FK.name,
            @RefTableName = OBJECT_NAME(C.parent_object_id)
         FROM
            sys.foreign_keys AS FK
            INNER JOIN sys.foreign_key_columns AS C
                        ON FK.OBJECT_ID = C.constraint_object_id
            INNER JOIN sys.tables T ON t.OBJECT_ID = C.referenced_object_id
         WHERE
            OBJECT_NAME (FK.referenced_object_id) = @TableName

         IF @ConstraintName IS NULL
            BREAK;

         PRINT 'Dropping constraint ' + @ConstraintName
         EXEC ('ALTER TABLE [' + @RefTableName + '] DROP CONSTRAINT ' + @ConstraintName)
      END        
       
      PRINT 'Dropping table ' + @TableName
      EXEC ('DROP TABLE [' + @TableName + ']') 
END

GO

Get all foreign keys that refer a specific table and column

SELECT
        OBJECT_NAME(constraint_object_id) as FKName,
        OBJECT_NAME(parent_object_id) as TableName,
        COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
        OBJECT_NAME(referenced_object_id) AS ReferencedTableName,      
        COL_NAME(referenced_object_id, referenced_column_id) as ReferencedColumnName   
FROM
        sys.foreign_key_columns FKC
WHERE
        OBJECT_NAME(referenced_object_id) = '<ReferencedTableName>'
        AND COL_NAME(referenced_object_id, referenced_column_id) = '<ReferencedColumnName>'
ORDER BY 1

How to create a unique constraint?

ALTER TABLE <tablename>
ADD CONSTRAINT <constraintname> UNIQUE NONCLUSTERED (<columnname>)

Subscribe to RSS - constraints