Post reply

having problem in deleting record from customer table in northwind databse

  • February 18, 2010 at 7:21 am

    #98543

    i am having problems in deleting customer. the customerID is fk in order table and orderID is fk in order detail table

    plzzz tell me the query to delete customer which will delete the customer as well as delete records related to that customerID in the order and order detail table.

    is there a way that i delete customer without effecting the order and order detail tables.

  • February 18, 2010 at 9:00 am

    #1120982

    I suggest that you read the following:

    http://msdn.microsoft.com/en-us/library/ms175464(SQL.90).aspx

    Amongst other text it contains:

    Referential Integrity

    Although the main purpose of a FOREIGN KEY constraint is to control the data that can be stored in the foreign key table, it also controls changes to data in the primary key table. For example, if the row for a salesperson is deleted from the Sales.SalesPerson table, and the salesperson's ID is used for sales orders in the Sales.SalesOrderHeader table, the relational integrity between the two tables is broken; the deleted salesperson's sales orders are orphaned in the SalesOrderHeader table without a link to the data in the SalesPerson table.

    A FOREIGN KEY constraint prevents this situation. The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

    Once you read the reference page you should know/understand why you can not delete the customerI. And then you should understand why you should not

    i delete customer without effecting the order and order detail tables

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply