alter table column, which is part of foreign key

[RamaKrishna Narla]

In MS SQL Server, I have the following tables with some data in it.

create table table1 (
column1 varchar(32),
column2 int not null,
column10 varchar(255),
.....
primary key (column1, column2),
);

create table table2 (
column1 varchar(32),
column2 int not null,
column20 varchar(255) not null,
....
foreign key (column1, column2) references table1(column1, column2)
);

Now, I need to change the all column types from varchar to nvarchar to
support internationaliz ed character set.
I am able to do so, for columns column10 in table1 and column20 of
table2 without any problems by using command:

"alter table table1 alter column column10 nvarchar(255);"

But, when I try the similar thing for column1 of table1/table2, am
getting one error message saying like: "ALTER TABLE ALTER COLUMN failed
because one or more objects access this column". I guess, this is
coming because of foreign key relationship between the tables.

NOTE: While defining the table2, for foreign key I have not specified
anything like "on update cascase" ...etc.

How can I resolve this issue? Any suggestions/solutions are really
helpful to me. Thanks in advance.

[Dan Guzman]

Re: alter table column, which is part of foreign key

You'll need to remove the constraints on the column before the ALTER and add
back afterward like the example below. If you used system-generated
constraint names, you can determine the names with sp_help 'table name'

CREATE TABLE table1
(
column1 varchar(32) NOT NULL,
column2 int NOT NULL,
column10 varchar(255),
CONSTRAINT PK_table1 PRIMARY KEY (column1, column2)
);

CREATE TABLE table2
(
column1 varchar(32),
column2 int NOT NULL,
column20 varchar(255) NOT NULL,
CONSTRAINT FK_table2_table 1 FOREIGN KEY
(column1, column2)
REFERENCES table1(column1, column2)
);

ALTER TABLE table2
DROP CONSTRAINT FK_table2_table 1;
ALTER TABLE table1
DROP CONSTRAINT PK_table1;

ALTER TABLE table1
ALTER COLUMN column1 nvarchar(32) NOT NULL;
ALTER TABLE table2
ALTER COLUMN column1 nvarchar(32);

ALTER TABLE table1
ADD CONSTRAINT PK_table1
PRIMARY KEY (column1, column2)

ALTER TABLE table2
ADD CONSTRAINT FK_table1_table 2
FOREIGN KEY (column1, column2)
REFERENCES table1(column1, column2)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RamaKrishn a Narla" <rknarla@gmail. com> wrote in message
news:1143871217 .127737.223420@ j33g2000cwa.goo glegroups.com.. .[color=blue]
> In MS SQL Server, I have the following tables with some data in it.
>
> create table table1 (
> column1 varchar(32),
> column2 int not null,
> column10 varchar(255),
> .....
> primary key (column1, column2),
> );
>
> create table table2 (
> column1 varchar(32),
> column2 int not null,
> column20 varchar(255) not null,
> ....
> foreign key (column1, column2) references table1(column1, column2)
> );
>
> Now, I need to change the all column types from varchar to nvarchar to
> support internationaliz ed character set.
> I am able to do so, for columns column10 in table1 and column20 of
> table2 without any problems by using command:
>
> "alter table table1 alter column column10 nvarchar(255);"
>
> But, when I try the similar thing for column1 of table1/table2, am
> getting one error message saying like: "ALTER TABLE ALTER COLUMN failed
> because one or more objects access this column". I guess, this is
> coming because of foreign key relationship between the tables.
>
> NOTE: While defining the table2, for foreign key I have not specified
> anything like "on update cascase" ...etc.
>
> How can I resolve this issue? Any suggestions/solutions are really
> helpful to me. Thanks in advance.
>[/color]

[RamaKrishna Narla]

Re: alter table column, which is part of foreign key

Thank you very much Dan. It worked for me.