Share via

Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY constraint

Question

Friday, April 10, 2009 7:31 AM

Hi, am trying to delete a table from the NORTHWND database so i can personalise it, but it keeps giving me this msg:

 Could not drop object 'dbo.CustomerDemographics'
 because it is referenced by a FOREIGN KEY constraint

and also, when i try to delete a row/record from ant of the tables in the NORTHWND database, it gives this erro:

no rows were deleted.

the delete statement conflicted with the reference constraint "FK_Orders_Customers".

what shuld i do? how can i drop the constraint.

 

thanx.

All replies (6)

Sunday, April 12, 2009 3:28 PM ✅Answered

  1. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design (Modify in SP1 or earlier).

    The table opens in Table Designer.

  2. From the Table Designer menu, click Relationships.

  3. In the Foreign-key Relationships dialog box, click Add.

    The relationship appears in the Selected Relationship list with a system-provided name in the format FK_<tablename>_<tablename>, where tablename is the name of the foreign key table.

  4. Click the relationship in the Selected Relationship list.

  5. Click Tables and Columns Specification in the grid to the right and click the ellipses (…) to the right of the property.

  6. In the Tables and Columns dialog box, in the Primary Key drop-down list, choose the table that will be on the primary-key side of the relationship.

  7. In the grid beneath, choose the columns contributing to the table's primary key. In the adjacent grid cell to the left of each column, choose the corresponding foreign-key column of the foreign-key table.

    Table Designer suggests a name for the relationship. To change this name, edit the contents of the Relationship Name text box.

  8. Choose OK to create the relationship.

 Note:

<!--src=[../local/note.gif]-->
The columns you choose for the foreign key must have the same data type of the primary columns they correspond to. There must be an equal number of columns in each of the keys. For example, if the primary key of the table on the primary side of the relationship is made up of two columns, you will need to match each of those columns with a column in the table for the foreign key side of the relationship.

 

Second way of creating Foreign key

There are many ways, but the easiest is to make a diagram for your database (right click on Database Diagram under your MDF in the database explorer), add the tables you want to be part of the relationship. Then it is literally just a matter of clicking the field from one table, hold down the left mouse button, and -drag- the cursor unto the field you want in the second table, release. It will then pop the dialog to confirm and edit your foreign key. Thats it!

 

Please note that you need to create/post your questions once your initial Question is resolved. But I have answered your second Question(creating FK using Web Developer). You wont receive any replies in this thread until you create a new thread. It helps others as well.

Thanks,

Friday, April 10, 2009 7:44 AM

You can not delete a Table or Row when it is referenced by a Foreign key.

You need to delete the data / Table from Parent Table first in order to delete the Foreign key Table.

Let me know further Queries.

Thanks,

Friday, April 10, 2009 7:44 AM

HI, akanetsamson

This Is Because  you are trying to drop a table that is referenced by foreign key constraint.

U can refer these

http://forums.asp.net/t/1150913.aspx

http://forums.asp.net/t/1177592.aspx

Friday, April 10, 2009 9:58 AM

 hi, the 1st link doesnt work and the 2nd link is totaly different from my question.

i know about the table being referenced by a  foriegn key constant, i tried deleting the data from the parent table, it still shows me the same message.

my question is, is there a way i can remove all the foriegn keys and delete all the data in the tables? or at least create a similar 1 in the same database and then DELETE the old ones. cos i need that database cos it has all the exact tables and fields i need for my project, but i dont want the old data that was in it.

 

Please help.

thanx.

Sunday, April 12, 2009 8:44 AM

1)  Using below  SQL statement you can see all Constraints with Table name, Constraint name and other information.

 

SELECT * FROM Northwind.INFORMATION_SCHEMA.TABLE_CONSTRAINTS

 2) In order to Delete the data from both the Tables, you need to drop the Constraint first as shown in below statement

ALTER TABLE Northwind.dbo.orders drop constraint FK_Orders_Customers

 3)  Using below statement data can be deleted from the Table. Please note you need to use the same on both Tables.

TRUNCATE TABLE Northwind.dbo.CustomerDemographics

 

 

By following above step by step approach you can successfully meet your requirement. But droping Constraint may lead to data inconsistency.

Let me know further Queries.

Sunday, April 12, 2009 11:30 AM

ok thanx, i worked it out by deleting the associations links from the dml models. it allows me to delete rows and tables.

the problem now is how do i insert a foriegn key i.e from the properties not using codes.

for example, i have added more tables to the Database and created colunms with the names of the foriegn key. I also drew a link (i.e the relationship links) using the database model explorer in MS web developer, but when i open the table definition from the database explorer, and right click and select 'Relationships', it doesnt show anything.

from other tables in another ready made database, am supposed to see something like Fk_product_suppliers. but mine shows nothing, and even when i try to click the 'add' button, it shows something like Fk_product_product* and it doesnt let me edit.

 

can u help.

  • Last updated on