How to alter length of the column for a table with pk constraints and fk constraints in SQL server script wise?

Question

0.00/5 (No votes)

See more:

How to alter length of the column for a table with pk constraints and fk constraints in Sql server script wise?

SQL

ALTER TABLE NON_MOL_Corporate
ALTER COLUMN [CorporateId] varchar(50)

Getting below error

C#

The object 'PK_Non-MOL_Corporate' is dependent on column 'CorporateId'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CorporateId failed because one or more objects access this column.

What I have tried:

I tried as below:

SQL

ALTER TABLE NON_MOL_Corporate
ALTER COLUMN [CorporateId] varchar(50)

Getting below error

C#

The object 'PK_Non-MOL_Corporate' is dependent on column 'CorporateId'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CorporateId failed because one or more objects access this column.

Posted 15-Mar-16 18:52pm

ranio

Updated 15-Mar-16 19:20pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Animesh Datta · Answer 1 · 2016-03-15T19:20:00

Solution 1

Hello ,
This error comes if there exists some foreign key constraints . To avoid this issue , you first drop the foreign key constraints then alter the column and then create the foreign key constraint again .

Try this

ALTER TABLE [dbo.childtable] DROP CONSTRAINT [FK_Non-MOL_Corporate];

---do the other stuff 
ALTER TABLE NON_MOL_Corporate
ALTER COLUMN [CorporateId] varchar(50)

ALTER TABLE [dbo.childtable] ADD FOREIGN KEY (FK_Non-MOL_Corporate) 
    REFERENCES NON_MOL_Corporate(CorporateId);

Thanks

Posted 15-Mar-16 19:20pm

Animesh Datta

Comments

ranio 16-Mar-16 1:52am

how to get the child table for the referenced Foreign Key Constraint?

ranio 16-Mar-16 3:31am

when i tried to remove the constraint it shows it doesn't exists.

Animesh Datta 16-Mar-16 4:24am

to get list of all foreign keys referencing a given table in SQL Server you can do this
EXEC sp_fkeys 'TableName'
and then check what it defines .