This question already has an answer here:
- Constrains relationship 1 answer
I have these tables:
|-------------tblSchool -------------| | + ID | | + Name | | | | | tblSchoolYear tblTerm | + SchoolID | + SchoolID | + Name | + Name | | | | |---------- tblSchoolTerm ----------| + SchoolYearID + TermID + StartDate + EndDate
As you can see, I have 2 tables tblSchoolYear and tblTerm that both have SchoolIDs. These 2 tables then makesup a SchoolTerm. My problem is I want the to prevent the combination of tblSchoolYear and tblTerm in tblSchoolTerm withe different SchoolIDs.
For example, if I have 2 schools with IDs 100 and 101, I don't want to have a SchoolYear and Term in SchoolTerm in which SchoolYear has a SchoolID of 100 and the Term a SchoolID of 101.
What I can think of is using a trigger to enforce this, but I wanted to ask if maybe there is a better design to solve this problem without resorting to triggers.
Using SQL Server 2012 by the way.
For those asking why I can't just have 2 tables, tblSchool and tblSchoolTerm, I have other columns in tblSchoolYear and tblTerm that contains data that I don't wnat duplicated. Sorry, I'll add some sample columns more to make it clearer.