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

[Dan Guzman]

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" <rk*****@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.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.

[RamaKrishna Narla]

Thank you very much Dan. It worked for me.