• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Regarding Foreign Key Restricts when Alter data type in sql table

 
prakasham selamban
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database are you using?
 
prakasham selamban
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This will do it:

 
prakasham selamban
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello

yesterday i asked lot of times . but you did nt answer right now. please answer my question.
 
Jan Cumps
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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
Bartender
Posts: 2586
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello paul

Have you checked this query. i could not run as you told me. But i could not run that .
 
prakasham selamban
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hello

I hope you will help me.

thanks in advance
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34218
341
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic