Deleting record with a primary key

[ttkalec1]

Hi,
I dont understand how relationships and constraints in a database work...
Actually, I just wanna know if this is possible:

I have two tables: 1. contains a primary key, and the 2. contains foreign key that is linked to the first one.
It looks like this:

1.table
ID Name Year

2.table
ID Description

Is it possible to delete a record in first table (which contains primary key), and then, because of their relationship, rows in the second table containing ID (foreign key) be deleted?
Or do I need to do it all programatically

Btw. Im using Microsoft SQL Server 2005 Express

 

[Diesel]

A foreign key constraint says... for every entry in the child table, an entry in the parent table must exist.

You can delete child records in SQL Server 2005 using 2 different methods:

1. A trigger
2. The CASCADE option

As long as you have the authority to modify the table schema, I recommend using the CASCADE option. It is faster and does not require the maintenance of maintaining a trigger.

ALTER TABLE Table2
ADD CONSTRAINT fk_id
FOREIGN KEY (ID)
REFERENCES Table1 (ID) ON DELETE CASCADE


This will delete the corresponding child record whenever you delete a parent record. Make sure this is really what you want.

I would offer another tip, that you create a unique primary key for table 2. Having multiple objects in the db accessible with the same key violates a normalization rule.

 

[ttkalec1]

Thank you for your answer

 

[Nate Bross]

This will delete the corresponding child record whenever you delete a parent record. Make sure this is really what you want.

If this is NOT what you want, you should have a good reason for leaving orphaned child records in your database.

I would offer another tip, that you create a unique primary key for table 2. Having multiple objects in the db accessible with the same key violates a normalization rule.

This is true; every table should have its own primary key, in addition to any forign keys that are needed.