Member Avatar for 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.

  • 2 Contributors
  • 3 Replies
  • 81 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by pritaeas
Member Avatar for 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.

Member Avatar for 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?

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.