Resizing a column which is used as a foreign key

• d.rosen

  Grasshopper

  Points: 19

  More actions

  March 10, 2010 at 7:17 am

  #96902

  I have a large database which manages stocks and other securities. The main key is Ticker, defined as Varchar(16).

  There are about 20 tables which use that field as a foreign key.

  I found out today that there is a need to alter Ticker column all over the database to Varchar(32).

  is there an easy way to do it?

  David Rosen

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  March 10, 2010 at 8:00 am

  #1131426

  There is no easy way and you will need to generate script for drops and create of primary key, unique and foreign keys:

  ALTER TABLE [<owner_name>].[<table name>] drop <foreign key constraint name>;

  ALTER TABLE [<owner_name>].[<table name>] drop <primary key constraint name>;

  ALTER TABLE [<owner_name>].[<table name>] drop <unique constraint name>;

  ALTER TABLE ALTER COLUMN ... ;

  ALTER TABLE [<owner_name>].[<table name>] add constraint <primary key constraint name> ... ;

  ALTER TABLE [<owner_name>].[<table name>] add constraint <unique constraint name> ... ;

  ALTER TABLE [<owner_name>].[<table name>] add constraint <foreign key constraint name> ... ;

  SQL = Scarcely Qualifies as a Language

• d.rosen

  Grasshopper

  Points: 19

  More actions

  March 10, 2010 at 10:58 pm

  #1131789

  Thank you.

  So there is no easy way....