How to change database primary key field with a foreign key constraint

02 July 2012 Guus-Beltman SQL

Today I needed to increase a varchar database from 2 position to 50 positions. At first I thought this would be easy, just make a change script like this:

   1: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null

After running in SSMS I got two error messages back:

Msg 5074, Level 16, State 1, Line 1
The object 'FK_CategoryCode' is dependent on column 'Code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.

Oops.. The Code field is used as a primary key here and referenced by another table tb_Tips. Luckily the database protects data loss by checking the constraints on a table before allowing any changes. (Btw, my database is for storing user tips (tb_Tips) and are categorized within groups via tb_Categories.).

Since I want to increase from 2 to 50 characters, I don’t get any data loss, so I really want to change my field with keeping my data in the tables. After some thinking, I produced a rough plan:

  • Temporarily remove the foreign key constraint in tb_Tips
  • Temporarily remove the primary key constraint in tb_Categories
  • Now the tb_Categories.Code field is key constraints free: upgrade the Code field from 2 to 50 characters
  • Upgrade the reference field in tb_Tips
  • Add primary key constraint for tb_Categories
  • Add foreign key constraint for tb_Tips
  • Done! (and check the data in the tables)

So here is the script:

   1: DECLARE @constraintName varchar(255),@table nvarchar(50), @sql nvarchar(255), @constraintpattern nvarchar(10)
   2: set @table = 'tb_Tips' 
   3: set @constraintpattern = '%categ%'
   4:  
   5: --Remove foreign key constraint for tb_Tips
   6: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
   7: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'FOREIGN KEY'
   8: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
   9: exec sp_executesql @sql
  10:  
  11: --Remove primary key constraint for table tb_Categories
  12: set @table = 'tb_Categories' 
  13: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table
  14: select @constraintName = CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name = @table and CONSTRAINT_NAME like @constraintpattern and CONSTRAINT_TYPE = 'PRIMARY KEY'
  15: select @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @constraintName
  16: --DEBUG: select @sql
  17: exec sp_executesql @sql
  18:  
  19: -- Extend primary key field for table tb_Categories
  20: ALTER TABLE tb_Categories ALTER COLUMN Code varchar(50) not null
  21:  
  22: -- Extend foreign key field for table tb_Tips
  23: ALTER TABLE tb_Tips ALTER COLUMN Category_ref varchar(50)
  24:  
  25: -- Add primary key for table tb_Categories
  26: ALTER TABLE tb_Categories WITH NOCHECK 
  27: ADD CONSTRAINT PK_CategoeryCode PRIMARY KEY CLUSTERED (Code)
  28: WITH (FILLFACTOR = 75, PAD_INDEX = ON)
  29:  
  30: -- Add foreign key for table tb_Tips
  31: ALTER TABLE tb_Tips WITH NOCHECK 
  32: ADD CONSTRAINT FK_CategoryCode FOREIGN KEY (Category_ref)
  33:    REFERENCES tb_Categories (Code)

Note 1: I query the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system table to retrieve the EXACT name of the constraint. Since I need to run this script also on other database servers, the script should be figure out the name itself.

Note 2 / Note to self: next time always use a integer column with auto increment.

I came across the website of Jon Galloway, which has a brilliant T-SQL script to remove all constraints from a single table (a bit too much for me in the current situation):

   1: -- t-sql scriptlet to drop all constraints on a table
   2: DECLARE @database nvarchar(50)
   3: DECLARE @table nvarchar(50)
   4:  
   5: set @database = 'dotnetnuke'
   6: set @table = 'tabs'
   7:  
   8: DECLARE @sql nvarchar(255)
   9: WHILE EXISTS(select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where constraint_catalog = @database and table_name = @table)
  10: BEGIN
  11:     select    @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
  12:     from    INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
  13:     where    constraint_catalog = @database and 
  14:             table_name = @table
  15:     exec    sp_executesql @sql
  16: END

 

Comments are closed