This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes Regarding Foreign Key Restricts when Alter data type in sql table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Regarding Foreign Key Restricts when Alter data type in sql table" Watch "Regarding Foreign Key Restricts when Alter data type in sql table" New topic
Author

Regarding Foreign Key Restricts when Alter data type in sql table

prakasham selamban
Greenhorn

Joined: Apr 16, 2009
Posts: 15
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.


prakasham
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

What database are you using?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
prakasham selamban
Greenhorn

Joined: Apr 16, 2009
Posts: 15
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

Joined: Apr 14, 2004
Posts: 10336

This will do it:

prakasham selamban
Greenhorn

Joined: Apr 16, 2009
Posts: 15
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

Joined: Apr 16, 2009
Posts: 15
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Apr 16, 2009
Posts: 15
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

Joined: Apr 14, 2004
Posts: 10336

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

Joined: Apr 16, 2009
Posts: 15
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

Joined: Apr 16, 2009
Posts: 15
hello

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

Joined: Dec 20, 2006
Posts: 2497
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
prakasham selamban
Greenhorn

Joined: Apr 16, 2009
Posts: 15


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

Joined: Dec 20, 2006
Posts: 2497
    
    8

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

Joined: Apr 16, 2009
Posts: 15
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

Joined: Apr 16, 2009
Posts: 15
hello paul

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

Joined: Apr 16, 2009
Posts: 15
hello

I hope you will help me.

thanks in advance
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30392
    
150

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Regarding Foreign Key Restricts when Alter data type in sql table