Click here to Skip to main content
13,055,047 members (60,842 online)
Rate this:
Please Sign up or sign in to vote.
See more:

i am searching for a solution for a few hours now. I created a table in sql_ce with the follwoing query:
([_id] int NOT NULL  IDENTITY (1,1),
[firstName] nvarchar(4000) NULL,
[lastName] nvarchar(4000) NULL,
[sex] nvarchar(4000) NULL,
[orderAddress_fk] int NULL,
[deliveryAddress_fk] int NULL,
[paymendMethod_fk] int NULL,
[emailAddress] nvarchar(4000) NULL,
[phone] nvarchar(4000) NULL,
[mobilePhone] nvarchar(4000) NULL,
[company] nvarchar(4000) NULL);

and added a foreignkeyconstraint. it was created succesfully, because i am not able to drop that table. i used the following query:
ADD CONSTRAINT orderAddress_fk
FOREIGN KEY (orderAddress_fk) REFERENCES Address(_id)

so i want to drop the constraint and tried the follwing commands:

ALTER TABLE [Customer]
ALTER COLUMN orderAddress_fk

ALTER TABLE [Customer]
DROP CONSTRAINT orderAddress_fk

the query executes with succes but after executing, i am still not able to drop the table, and the UI shows that the constraint still exists...

i dont know what to try else... :(
Posted 22-Dec-12 9:46am
Updated 22-Dec-12 10:04am
Zoltán Zörgő 22-Dec-12 16:33pm
And what happens if you delete the constraint from the UI - supposing SQL Management Studio?
Member 9489134 22-Dec-12 18:22pm
I am using SQLSM Express 2008 and there is no option in the propertys window, allowing to define any constrains exapt of the primary key. But i know that the pk exists. there is an FS mark in the UI description of the columns and i cant delete it.
Zoltán Zörgő 23-Dec-12 3:20am
You are wrong, there is. I am using the same. Start to "Design" the table. In the "Table Designer" menu you will find a "Relationships" menu item. Look here:, that stands for Express too.
Member 9489134 23-Dec-12 8:36am
okay there is. thank you for your reply. but my problem still exists. i have to delete the foreign key via query...
Zoltán Zörgő 23-Dec-12 12:29pm
I understand, but can you delete it in the UI? If you can't even from UI than it is something not right. Check if you have exclusive access to the file when issuing ddl statement - be sure, that you have only one connection open. Check if you have not opened it as read-only.
Member 9489134 23-Dec-12 15:05pm
Hey Zoltán. It might be a litlle more confusing than yout thought. I can delete the foreign key via the UI. There must be something wrong with my delete query... again: thanks for your help!!!
Zoltán Zörgő 23-Dec-12 15:25pm
I have issued the same command (in a compact db like you did, not sql express), and the FK flag disappeared as it should. Have you tried reissuing the drop constraint statement? You should get an error message (The foreign key constraint does not exist. [ orderAddress_fk ]). Be aware, that you have to refresh the view in order to see the changes.
Member 9489134 24-Dec-12 7:18am
Yes. The mesage i always get when i try to delete the constraint is:

> ALTER TABLE [Customer]
DROP CONSTRAINT orderAddress_fk
The foreign key constraint does not exist. [ orderAddress_fk ]

But in the UI its still shown and i still can delete it via ui...???

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I found the solution:

if you want to delete a foreignkey via query, make sure that you have named your constraint and delete it by this name. so i changed my create foreign key query to:
ALTER TABLE [Customer]
ADD CONSTRAINT FK_deliveryAddress FOREIGN KEY (deliveryAddress_fk)
REFERENCES [Address](_id)

And now i can delete it with this query:
DROP CONSTRAINT FK_deliveryAddress

Thanks to Zoltán Zörgő for his help...
tesnep 30-Sep-14 23:14pm
And this is in Sql Server CE?

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100