By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,523 Members | 1,938 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,523 IT Pros & Developers. It's quick & easy.

alter table column, which is part of foreign key

P: n/a
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.

Apr 1 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Apr 1 '06 #2

P: n/a
Thank you very much Dan. It worked for me.

Apr 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.