Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-CE
Hi,
 
i am searching for a solution for a few hours now. I created a table in sql_ce with the follwoing query:
CREATE TABLE [Customer]
([_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:
ALTER TABLE Customer
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
DROP DEFAULT
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
Edited 22-Dec-12 10:04am
v3
Comments
Zoltán Zörgő at 22-Dec-12 16:33pm
   
And what happens if you delete the constraint from the UI - supposing SQL Management Studio?
Member 9489134 at 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ő at 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: http://dotnetslackers.com/Community/blogs/bmdayal/archive/2010/06/05/creating-a-table-relationship-using-sql-server-2008-management-studio.aspx, that stands for Express too.
Member 9489134 at 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ő at 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 at 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ő at 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 at 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
good
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)
GO
And now i can delete it with this query:
ALTER TABLE[Customer]
DROP CONSTRAINT FK_deliveryAddress
Thanks to Zoltán Zörgő for his help...
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 6,651
1 Sergey Alexandrovich Kryukov 6,400
2 CPallini 5,230
3 George Jonsson 3,574
4 Gihan Liyanage 2,542


Advertise | Privacy | Mobile
Web04 | 2.8.140921.1 | Last Updated 24 Dec 2012
Copyright © CodeProject, 1999-2014
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