[quoted text, click to view] > Do i need to first delete all records with same IDCustomer from the child
> table CustomerOrders?
Yes.
[quoted text, click to view] > Or is it done automatically when i remove the record from the parent table
> CustomerInfo?
In SQL 2000, you can declare the foreign key constraint with the DELETE
CASCADE option like the example below. Related rows will be automatically
removed. See the Books Online for more information.
CREATE TABLE CustomerInfo
(
IDCustomer int NOT NULL
CONSTRAINT PK_CustomerInfo PRIMARY KEY
)
CREATE TABLE CustomerOrders
(
OrderNumber int NOT NULL
CONSTRAINT PK_CustomerOrders PRIMARY KEY,
IDCustomer int NOT NULL
CONSTRAINT FK_CustomerOrdersCustomerInfo
FOREIGN KEY REFERENCES CustomerInfo(IDCustomer)
ON DELETE CASCADE
)
INSERT INTO CustomerInfo VALUES(1)
INSERT INTO CustomerOrders VALUES(1, 1)
INSERT INTO CustomerOrders VALUES(2, 1)
DELETE FROM CustomerInfo
SELECT * FROM CustomerInfo
SELECT * FROM CustomerOrders
--
Hope this helps.
Dan Guzman
SQL Server MVP
[quoted text, click to view] "MuZZy" <leyandrew@yahoo.com> wrote in message
news:urmdnbvLMNoECabfRVn-pg@comcast.com...
> Hi,
>
> I'm kinda new to using foreign keys, so don't kill me for the stupid
> question:
> Say, i have two tables:
> 1. CustomerInfo - with primary key column IDCustomer
> 2. CustomerOrders - also has IDCustomer column, but not unique
>
> Now I set a foreign key relation between these tables on IDCustomer field
> and CustomerInfo becomes a parent table, and CustomerOrders become a child
> table.
>
> So now i can oly add a record to CustomerOrders if such an IDCustomer
> value exists in CustomerInfo.
>
> But what if i want to delete a record from the parent table CustomerInfo?
> Do i need to first delete all records with same IDCustomer from the child
> table CustomerOrders?
> Or is it done automatically when i remove the record from the parent table
> CustomerInfo?
>
> Any comments (even mean :)) are highly appreciated!
>
> Thank you,
> Andrey
