0

I have a table that I want to drop and create again, because I want to add the IDENTITY property to a column, wich is not possible directly. The problem is that I can't drop it, because my column is being referenced by many foreign keys in other tables. So, what are the strategies adopted for droping a table referenced by many foreign keys from other tables ?

Thank you for help in advance :)

  • Do the FKs reference this column that you want changed? Does the table have data? If yes, don't you care about them? And what should happen to the relevant data in other tables (that reference this table)? – ypercubeᵀᴹ Aug 27 '14 at 15:21
  • @ypercube I'm migrating from Oracle to SQL Server :) So at first, I'm migrating the schema with the whole database structure, there is still no data :) Yes, The FKs I'm speaking about are referencing this column. I've edited my question :) – mounaim Aug 27 '14 at 15:28
4

Assuming the column you want to change is the one the FKs point to, you need to drop all of the foreign key constraints - it's not enough to simply disable them. You can't drop the table, anyway; and you can't drop the column if it's actually referenced, and the only way to add the IDENTITY property is to drop/re-create the column, or drop/re-create the table.

Here is a script that generates the commands to drop and later re-create all of the foreign keys that point at dbo.yourtable. It is based on this answer. Drop is easy, just build a simple concatenated list from sys.foreign_keys:

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.yourtable');

PRINT @drop;

Create is a little more complex. We need to generate the list of columns on both sides of the constraint, even though in most cases there is only one column:

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0
AND rt.[object_id] = OBJECT_ID(N'dbo.yourtable');

PRINT @create;

When you're happy with the output (keep in mind that PRINT is limited to 8K, so it may look like the command is truncated), add this to the end and run it again:

EXEC sp_executesql @drop

-- drop and re-create the table here, whatever that needs to entail

EXEC sp_executesql @create;

This should be a pretty simple exercise because, based on information you've added later, these tables are empty.

If this is not the column the FKs point to, you may be able to drop just the column and re-add it with the IDENTITY property. But if you had data, this would mean you would have to accept whatever arbitrary row numbers SQL Server would assign, or be prepared to completely gut the table (and all the FK tables, or at least the FK column(s) pointing to this table) and re-populate. You would also have to accept that the column may now be in a different "physical" location within the table (you shouldn't care about this at all, but I mention it because many do for reasons unknown).

In SQL Server 2012, as a workaround, you could implement a SEQUENCE instead of using the IDENTITY property. Since you're only on SQL Server 2008, you could instead generate the increment using your own serializable MAX+1 construct. But that's a different question altogether. (I just ran a search, I have not looked at how good those answers are; also see some ideas here.) Or move to a more modern version with more options.

  • Thank you very much @Aaron ! I will try this and give a feedback. – mounaim Aug 27 '14 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.