Delete ForeignKey Constraint in SQL Server Compact 3.5

Question

0.00/5 (No votes)

See more:

Hi,

i am searching for a solution for a few hours now. I created a table in sql_ce with the follwoing query:

SQL

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:

SQL

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:

SQL

ALTER TABLE [Customer]
ALTER COLUMN orderAddress_fk
DROP DEFAULT

SQL

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

ChrisTopherus

Updated 22-Dec-12 10:04am

v3

Add a Solution

Comments

Zoltán Zörgő 22-Dec-12 16:33pm

And what happens if you delete the constraint from the UI - supposing SQL Management Studio?

ChrisTopherus 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: 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.

ChrisTopherus 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.

ChrisTopherus 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.

ChrisTopherus 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

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)

ChrisTopherus · Accepted Answer · 2012-12-24T01:47:00

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:

SQL

ALTER TABLE [Customer]
ADD CONSTRAINT FK_deliveryAddress FOREIGN KEY (deliveryAddress_fk)
REFERENCES [Address](_id)
GO

And now i can delete it with this query:

SQL

ALTER TABLE[Customer]
DROP CONSTRAINT FK_deliveryAddress

Thanks to Zoltán Zörgő for his help...