Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When creating a primary/foreign key relationship there are the following options:

  1. Check Existing Data On Creation [Yes/No]
  2. Enforce for Replication [Yes/No]
  3. Enforce Foreign Key Constraint [Yes/No]

What are the uses for all these when you select 'Yes' or 'No' ?

Because when deleting the record with the primary key, I have a problem if there are any foreign key relationships, it doesn't let me delete. So how do you delete all related records to primary key at that same time? or how do you ignore other foreign records when primary is deleted and allow the delete?

I searched online but can't find the answer to my questions.

share|improve this question

migrated from stackoverflow.com Mar 21 '13 at 7:41

This question came from our site for professional and enthusiast programmers.

2 Answers 2

What you need to check are the referential integrity constraints.

If you want to delete the parent key there are around four options on how you want to deal with the children keys:

  1. NO ACTION which raises an error and does not delete the parent key (rolls back).
  2. CASCADE which will carry on and delete all the child keys referred by that parent key, leaving no orphans.
  3. SET NULL which will set all the child keys to null values.
  4. SET DEFAULT sets the child keys to the specified default value.
share|improve this answer

You need to use a different option for this. Please check the Delete Rule option. What you need, I believe, is a Cascade option, that according to MSDN:

Deletes all rows containing data involved in the foreign key relationship. Do not specify CASCADE if the table will be included in a merge publication that uses logical records.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.