Re: Help with Delete query

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 08/30/04 

Date: Mon, 30 Aug 2004 18:52:06 +0200

I would not want to have orders for which I don't know which customer to ship them to. This is why I have a
foreign key, to avoid this particular situation. We can't really say what is your best solution as we don't
know what you want to achieve, what data you want to represent in your database and why you want this
scenario. If you give a description of your requirements, we might be able to suggest something.

One situation where you might want this is history reasons. Say you no more sell a particular product, but you
still want information in the database for sales of that product. There are various ways to handle this
situation... 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diego F." <diegofrNO@terra.es> wrote in message news:OUqYZ5qjEHA.3696@TK2MSFTNGP15.phx.gbl...
> OK. Is there any problem having a detail table (orders) without foreing key?
> Is then a better idea to create another key (identity or something)?
>
> Regards,
>
> Diego F.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> escribió
> en el mensaje news:O4ytqyqjEHA.3896@TK2MSFTNGP10.phx.gbl...
> > So as I understand it, you want to have orders for which the customer
> doesn't exist. then you should not have
> > created the foreign key between the tables in the first pace, as this is
> exactly what the foreign key is there
> > to prevent.
> >
> > You can create a "dummy" customer and update the order so they refer to
> that dummy customer. Or disable the
> > foreign key and delete the customer.
> > Or delete the foreign key if you don't want it to do the job it is
> designed to do. :-)
> >
> > -- 
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Diego F." <diegofrNO@terra.es> wrote in message
> news:%23eIDfRpjEHA.3696@TK2MSFTNGP15.phx.gbl...
> > > Yes, is what you say. I want to delete the customer, but mantain the
> entries
> > > in the orders table.
> > >
> > > -- 
> > >
> > > Saludos,
> > >
> > > Diego F.
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
> escribió
> > > en el mensaje news:OtVZjwojEHA.536@TK2MSFTNGP11.phx.gbl...
> > > > So, as an example, your Orders table has a foreign key to the
> Customers
> > > table. And you want to
> > > > delete a customer. This is what foreign key are there for. Or are you
> > > saying that it is the
> > > > opposite. Try to describe your problems using the terms "referencing"
> and
> > > "referenced" table. Makes
> > > > it easier to understand. In my example, Orders is the referencing
> table,
> > > and Customers is the
> > > > Referenced table. Or, if it is easier for you, you could post
> (simplified)
> > > DDL.
> > > >
> > > > -- 
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Diego F." <diegofrNO@terra.es> wrote in message
> > > news:eNp8ZpojEHA.384@TK2MSFTNGP10.phx.gbl...
> > > > > Hi. I'm trying to build the delete query from a database.
> > > > >
> > > > > The problem is that the item I try to delete has a column that is
> the
> > > > > foreign key of another table and it doesn't allow me to do that. Why
> > > not?
> > > > >
> > > > > I don't want to delete the items from the other table where it's the
> > > foreing
> > > > > key.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Diego F.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • RE: DB Design for a Service Company...
    ... Clicking the + shows all sites for each customer. ... Simple table detailing supplier names, addresses, product/service provided. ... which will contain the same number as the primary key (from ... you use the same name for the foreign key as you used for the primary key. ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Get data from combo box to popluate the next box
    ... corresponding table by linking the tables in a query by the common field. ... When the customer is entered on the form via the ... If data is entered into the control, ... the corresponding foreign key field in the underlying table. ...
    (microsoft.public.access.forms)
  • Re: Help with Delete query
    ... Is there any problem having a detail table without foreing key? ... > So as I understand it, you want to have orders for which the customer ... > created the foreign key between the tables in the first pace, ... >> Diego F. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with Delete query
    ... I want to delete the customer, ... This is what foreign key are there for. ... In my example, Orders is the referencing table, ... > "Diego F." ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with Delete query
    ... created the foreign key between the tables in the first pace, as this is exactly what the foreign key is there ... You can create a "dummy" customer and update the order so they refer to that dummy customer. ... In my example, Orders is the referencing table, ... >>> Diego F. ...
    (microsoft.public.sqlserver.programming)
Loading