Foreign Key Constraint

[Skywalker83]

Hi to all,

I have a scenario like this:

School
(SchoolName, etc, etc...)

Teacher
(SchoolName,TeacherName, etc, etc...)
ForeignKey: [School.SchoolName]

Student
(SchoolName,StudentName, etc, etc...)
ForeignKey: [School.SchoolName]

Lesson
(SchoolName, TeacherName, StudentName, etc, etc...)
ForeignKey: [Teacher.SchoolName,Teacher.TeacherName] + [Student.SchoolName,Student.StudentName]

I would like to change the name of the school by updating the School table which updates all other tables using foreign key 'On UPDATE CASCADE', however, a foreign key constraints was violated at Lesson table. I'm currently using MySQL. I have heard that some database have "defered" checks for constraints which might solve this problem.

Or can this problem be solved by changing the foreign key structure?

Thanks for the advice.

[pritaeas]

I think the problem you are facing arises from the fact that you use names (varchar) as foreign keys. In most situations you would have a PK integer field, which links all the tables together. The name would be just a column, which could then be easily changed.

[Skywalker83]

Thanks for the reply.

I have considered using integer for PK for the tables. But if the School table are to be access very frequently, would there be a difference in speed if integer PK (required to join multiple tables) are to be used?

[pritaeas]

Yes, an integer is much easier to match than a varchar, and needs smaller index tables (you would have a speed gain).

If you are searching for school names regularly, you can always consider using an index on that column.