problem in show view when delete foreign key?

I have a student table and an education table, with the PK of the education table as a foreign key of the student table. Howeve开发者_高级运维r, when the education is deleted, the student no longer appears in the view. How do I solve this problem?

From the information you have given, my guess is that you have enforced referencial integrity on your database. This means that when you deleted a row in education, the students that were linked with it were also deleted.

I find that it is good practice to never delete data from tables due to other fields being dependent on them. Instead, you should have a boolean value in the table that is called 'IsDeleted' and just change that to True when you want to 'Delete' it, and when you pull data make sure you filter out anything that has the 'IsDeleted' set to 'True'

Based on what you are asking I think you should first rethink your database structure.

Answer the following questions:

1. Does it make sense to have a student with a non existant education?

   This would be the case if you deleted an education in your Educations table but students with a FK to that education row lived on in your database. This seems to be what you are asking for but it doesn't make much sense as it doesn't mantain data integrity.

2. Should you be allowed to delete an education if students are enlisted in said education?

   If it shouldn`t be allowed then you would only need to disable cascade deleting in your 1 to many relationship and your problem would be solved.

3. If an eduction is deleted, should all students assigned to said education remain in the data base?

   This is what you want but with the structure of your database it is not straightforward to achieve.

Easier solution?

One would be to create 3 tables instead of 2:

1. Educations
2. Students
3. StudentsEducationAssignments

In 1 you store eveything that has to do ONLY with your education entities. In 2 only what has to do with your student entities (note that what type of education they choose is not something that ONLY describes the student). In 3 you store what students are assigned to what educations.

This way, if you delete an education, the students assigned to it will not be deleted, only the information that ties students to that specific education. You keep database integrity easier this way.

Hope this helps.

Maybe an OUTER JOIN instead of an INNER JOIN in your view?

If you show us the view definition we might be able to help more, without it we're just guessing.