SQL Server Question 
Author Message
 SQL Server Question

Hi! I've some question about the foreign keys relations...
I've established relations between 2 tables by a foreign key
say t1.primary and t2.foreign.
Now I want to set it in the way that when I remove a row from t1 all
rows from t2 associated with t1.primary rows by a t2.foreign->t1.primary
relation will be removed automatically. In Access and some other
databases there are options for relations like restrict, cascade (that I
need) etc. How do I set it in SQL-Server?.
Well, I tried to write a delete trigger which will delete all the rows
from t2, but I still get a error since it foreign key relation is
checked first....
Do you have any idea of solving such a problem?



Sat, 01 Jan 2000 03:00:00 GMT
 SQL Server Question

Quote:

> You really need to do it in a case tool like erwin or s-designer. You will create cardinality between entities. When you generate the DDL you will see all

the triggers in it. It will be a tough job to write all the triggers.
--



Sun, 02 Jan 2000 03:00:00 GMT
 SQL Server Question

Nick,

If your foreign table has a declared RI constraint it is basically
enforcing a 'restrict' rule for all cases (U,I,D). When you write the
DeleteCascade (or somesuch) trigger you are basically violating the
restrict constraint and the trigger will not be aloowed to do its thing.

This is a point of pain for the CASE tools as they do not let you
combine the two.

The fix is that if you need non-restrictive RI you either need to write
triggers for it and not declare it as a constraint. Or convert to a DB
that supports the other RI modes; SQL Anywhere is a good one for small
DB applications.

Hope this explains.

Matt Townsend

Quote:

> Hi! I've some question about the foreign keys relations...
> I've established relations between 2 tables by a foreign key
> say t1.primary and t2.foreign.
> Now I want to set it in the way that when I remove a row from t1 all
> rows from t2 associated with t1.primary rows by a
> t2.foreign->t1.primary
> relation will be removed automatically. In Access and some other
> databases there are options for relations like restrict, cascade (that
> I
> need) etc. How do I set it in SQL-Server?.
> Well, I tried to write a delete trigger which will delete all the rows

> from t2, but I still get a error since it foreign key relation is
> checked first....
> Do you have any idea of solving such a problem?



Mon, 03 Jan 2000 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. SQL Server Question from Access SQL User

2. SQL Server Question from someone with no SQL expierence

3. SQL SERVER QUESTION

4. Access -> SQL Server question

5. Newbie to SQL Server question - select w/ order by

6. Large volumes on SQL Server - Questions

7. SQL Server Question

8. SQL Server questions

9. SQL Server Question

10. Access - SQL Server questions

11. SQL Server, Question

12. SQL Server Question


 
Powered by phpBB® Forum Software