Junction tables - unique rows across 2 columns

Question

Junction tables - unique rows across 2 columns

lanky_doodle

SSCrazy

Points: 2378
More actions
January 15, 2015 at 2:18 pm

#294383

Hey,
Is this possible? Take a simple example:
Students:
ID (PK), Name, Lastname, DoB
Classes:
ID (PK), Name
Student_Classes:
Student_ID, Class_ID
How would I ensure Student_Classes doesn't contain something like this:
Student_ID | Class_ID
1 | 1 <------|
1 | 2..........|-- duplicate
2 | 2..........|
1 | 1 <------|
Which is possible to insert, but not 'allowed'. I know I could have a 3rd column with uniqueidentifier but then it would still allow the same student_id and class_id to be inserted.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

haveyoumetcp SSC Eights! Points: 904 More actions · Answer 1

Create a PK on both the Student_ID and Class_ID columns in the Student_Classes table. This is called a Composite Primary Key.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

You can use a PK or simply a UNIQUE constraint or index. For example:

CREATE UNIQUE CLUSTERED INDEX Student_Classes__CL

ON dbo.Student_Classes ( Student_ID, Class_ID ) WITH ( FILLFACTOR = 96 ) ON [PRIMARY];

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

lanky_doodle SSCrazy Points: 2378 More actions · Answer 3

Thanks both.

Scott, what's the purpose of FILLFACTOR? Never used it before.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

FILLFACTOR directs SQL on how full to make each page of data. 96% leaves 4% -- roughly 300 bytes -- free on each page to allow for new rows / row expansion. FILLFACTOR 100[%] would pack each page completely full.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".