the delete statement conflicted with the reference constraint "FK_Orders_Customers".
------------------------------------------------------------------------------------------------------------------------
what shuld i do? how can i drop the constraint.
thanx.
Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY constraint
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.
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.
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.
From the Table Designer menu, click Relationships.
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.
Click the relationship in the Selected Relationship list.
Click Tables and Columns Specification in the grid to the right and click the ellipses (…) to the right of the property.
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.
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.
Choose OK to create the relationship.
Note:
<div class=alert>
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.
</div>
Second way of creating Foreign key
<div class=ForumPostContentText>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!
</div>
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.
akanetsamson
Member
119 Points
136 Posts
Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY cons...
Apr 10, 2009 11:31 AM|LINK
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.
Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY constraint
sukumarraju
All-Star
16985 Points
3001 Posts
Re: Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY c...
Apr 10, 2009 11:44 AM|LINK
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,
Application Architecture Guide 2.0
My Blog
Twitter
raghav_khung...
All-Star
32835 Points
5563 Posts
MVP
Re: Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY c...
Apr 10, 2009 11:44 AM|LINK
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
akanetsamson
Member
119 Points
136 Posts
Re: Could not drop object 'dbo.CustomerDemographics' because it is referenced by a FOREIGN KEY c...
Apr 10, 2009 01:58 PM|LINK
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.
sukumarraju
All-Star
16985 Points
3001 Posts
Here is the solution
Apr 12, 2009 12:44 PM|LINK
1) Using below SQL statement you can see all Constraints with Table name, Constraint name and other information.
2) In order to Delete the data from both the Tables, you need to drop the Constraint first as shown in below statement
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.CustomerDemographicsBy 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.
Foreignkey disable
Application Architecture Guide 2.0
My Blog
Twitter
akanetsamson
Member
119 Points
136 Posts
Re: Here is the solution
Apr 12, 2009 03:30 PM|LINK
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.
sukumarraju
All-Star
16985 Points
3001 Posts
Re: Here is the solution
Apr 12, 2009 07:28 PM|LINK
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.
From the Table Designer menu, click Relationships.
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.
Click the relationship in the Selected Relationship list.
Click Tables and Columns Specification in the grid to the right and click the ellipses (…) to the right of the property.
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.
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.
Choose OK to create the relationship.
Note:
<div class=alert>Second way of creating Foreign key
<div class=ForumPostContentText>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!</div>
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,
foreiGN KEYS using web developer
Application Architecture Guide 2.0
My Blog
Twitter