Regarding Foreign Key Restricts when Alter data type in sql table

[prakasham selamban]

Dear friends

I have a table named as students. It has a foreign key relation ship with some other table. When i alter the data type from integer to BigInt Foreign_Relationship not allowed to alter data type and throws a error like "ALTER TABLE ALTER COLUMN studentID failed because one or more objects access this column". I need help, how can i disable the constraints while alter data type in sql table.

[Paul Sturrock]

What database are you using?

[prakasham selamban]

Hello friend

i am using Sql server 2005 Management Studio. can you give me the suggestion for disble constraint while change data type . I need sql query or stored procedure.

[Paul Sturrock]

This will do it:

alter table foo nocheck constraint all 

[prakasham selamban]

I have put it this query when i run this query in query analyzer
"alter table students alter column student bigint nocheck constraint all"


it throws error like Incorrect syntax near the keyword 'nocheck'.

[prakasham selamban]

student id data type is int

but i want change the data type into big int . i want disable foreign key constraint while changing data type

[Paul Sturrock]

prakasham selamban wrote:I have put it this query when i run this query in query analyzer
"alter table students alter column student bigint nocheck constraint all"


it throws error like Incorrect syntax near the keyword 'nocheck'.

You need to disable constraints first, alter your column then re-enable the constraints, you can't shoehorn the two statements together.

[prakasham selamban]

helo friend

Disable Constraints
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL


Enable Constraints
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL

this stored procedure enable and disable constraints in sql. but only its used to delete or insert the records. This is not used to alter data type in sql. Is there any other query to disable or enable constraint. let me know

[Paul Sturrock]

prakasham selamban wrote:helo friend

Disable Constraints
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL


Enable Constraints
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL

this stored procedure enable and disable constraints in sql.

These two procedures disable then reenable all constraints for all tables in the database. Is this what you want to do?

but only its used to delete or insert the records. This is not used to alter data type in sql. Is there any other query to disable or enable constraint. let me know

Not sure what you are asking here. You already had the SQL to alter the column type; can't you just run it after you've disabled the constraints?

[prakasham selamban]

i am asking how to alter data type for a column in sql. First up all i have disable and do it . how can i do it

Table Name: Student


Column st_id int,
i want to chang data type st_id to BigInt. Foreign key not allowed to change the data type

[prakasham selamban]

hello

yesterday i asked lot of times . but you did nt answer right now. please answer my question.

[Jan Cumps]

Hi Prakasham, please be patient. We are doing this for free.

Reread Paul's 2nd and 3rd mail. The answers is there.
Don't mix the statements, but use one at a time:
disable constraint
alter table
re enable constraint.

Regards, Jan

[prakasham selamban]

I execute that queries seperately which is given by you.

alter table student nocheck constraint all

alter table id student column ID bigint

execute the above query first and then execute next query but it throw error .

Msg 5074, Level 16, State 1, Line 1
The object 'PK__Idis dependent on column 'ID'.
Msg 5074, Level 16, State 1, Line 1
The object 'FK__SID__2A164134' is dependent on column 'ID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column.

thankx for your reply

[Jan Cumps]

Seems that you have other constraints in place that refer to this column.

Is this statement corect:

alter table id student column ID bigint

[prakasham selamban]

hello friens

still now i did not get answer for this issue. Any one tell me how to disable foreign key constraing while changing the data type of the column.

[prakasham selamban]

hello paul

Have you checked this query. i could not run as you told me. But i could not run that .

[prakasham selamban]

hello

I hope you will help me.

thanks in advance

[Jeanne Boyarsky]

Prakasham ,
Keep in mind that everyone here is a volunteer and it can take up to 24 hours to get a reply. Posting "please help me" sooner than that doesn't make people more likely to help.

The best way to move forward with a problem is to directly address the last suggestion made. I'm not clear on what the reply to Jan is.