alter table column, which is part of foreign key

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 internationalized 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.

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_table1 FOREIGN KEY
(column1, column2)
REFERENCES table1(column1, column2)
);

ALTER TABLE table2
DROP CONSTRAINT FK_table2_table1;
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_table2
FOREIGN KEY (column1, column2)
REFERENCES table1(column1, column2)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"RamaKrishna Narla" <rknarla@gmail.com> wrote in message
news:1143871217.127737.223420@j33g2000cwa.googlegroups.com...
> 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 internationalized 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.
>

Thank you very much Dan. It worked for me.