sql - Record in one table unique to a record in another table

Question

Let me try to make it simple with an example.

I am creating a database with 2 tables, School and Students

School table with 2 columns SchoolID(PK) and Location Student table with 3 columns StudentID(PK), SchoolID(FK) and Grade

The Student table will have students from all the schools which can be identified by the foreign key SchoolID. The StudentID column will be unique to each student across all schools. Well and good so far.

I need another column in Student table which will be unique only with respect to the foreign key value. Let's call this ID. I need this column to be unique only to each SchoolID. So, If I filter out the students belonging to a particular SchoolID, I shouldn't get 2 students with same ID.

I'm not sure if that's a valid scenario.

Edit: This is good

This is not

标签： sqlentity-framework

Answer 1

我认为数据模型有问题。由于您StudentID在 Student-table 中具有主键，因此该列在该表中将始终是唯一的。但似乎您正在尝试创建一个学生学校表，您可以在其中将同一学生连接到多所学校（但一个学生不能多次连接同一学校）。我认为您至少需要 3 个表： Student (Pk StudentID) School (Pk SchoolId) StudentSchool

该StudentSchool表将有两个 FK 列：StudentID 和 SchoolID。为了防止同一学生多次映射到同一所学校，您可以让 PK 包含 StudentId 和 SchoolId 或创建唯一约束。