Deleting record with a primary key

[ttkalec1]

Hi,

I don't 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. I'm 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.