Solved

Alter Table fails to add FK Constraint to table.

Posted on 2008-06-20
5
1,751 Views
Last Modified: 2008-06-23
I'm logged in to SSMS via SQL Authentication. I can create tables, and create FK constraints at the time of table creation, but not via the Alter command on existing table. What do I need to do to make sure I have enough permission or is there a setting I need to change somewhere?
ALTER Table COMPANY 

ADD CONSTRAINT fk_Comp_Div FOREIGN KEY (DIV_ID) REFERENCES DIVISION (DIV_ID)

Open in new window

0
Comment
Question by:DustinSpears
  • 2
  • 2
5 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 21834096
you need the "ALTER TABLE" permission
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834163
you may want to consider adding NOCHECK To your constraint:

ALTER Table COMPANY WITH NOCHECK
ADD CONSTRAINT fk_Comp_Div FOREIGN KEY (DIV_ID) REFERENCES DIVISION (DIV_ID)
0
 

Author Comment

by:DustinSpears
ID: 21834195
When I go to the properties of my database, then "permissions" and look at the available permissions listed there for my login, I do not see an "Alter Table", I see an "alter" and alot of other alter etc., but not that one. Any idea why? And the Nocheck did not help, thanks though.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21834274
make sure you execute the statement in the context of the database that the table resides....your error message tells me that you executed it in another db by mistake.
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 21834374
grant alter on <your table> to [<your database user>]
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now