Microsoft SQL Server forum - alter table column, which is part of foreign key

Author

alter table column, which is part of foreign key

RamaKrishna Narla

2006-04-01, 3:25 am

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

2006-04-01, 7:26 am

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

RamaKrishna Narla

2006-04-03, 7:33 am

Thank you very much Dan. It worked for me.

Sponsored Links