How to prevent from duplicates in SQL Server table

Question

I am new to SQL Server. I have created a DB in Access and defined my relationships there. After this, I have used SQL Server Import and Export Data tool to convert the DB and use in SQL Server. My table structure is as follows:

Student            Course             Course-Module    Module          
-------------      -------------      -------------    -------------
TagID (PK)         CourseID (PK)      CourseID (FK)    ModuleID (PK)
StudentName        CourseName         ModuleID (FK)    ModuleName
CourseID                                                             

Module-Session     Session            Atendance
--------------     --------------     --------------
ModuleID  (FK)     SessionID (PK)     TagID     (FK)
SessionID (FK)     SessionName        SessionID (FK)       
                   SessionDate        ScanningTime
                   SessionTimeStart
                   SessionTimeEnd

I have developed a query that will find a current SessionID for the given TagID insert the result into Attendance table along with the time of insertion.

My query is (VS C# syntax)

string query = @"INSERT INTO Attendance " +
               "SELECT s.TagID, se.SessionID, " +
               " @todaysDate " +
               " AS ScanningTime " +
               " FROM (((Student s " +
               " LEFT JOIN [CourseID-ModuleID] cm ON s.CourseID = cm.CourseID) " +
               " LEFT JOIN [ModuleID-SessionID] ms ON cm.ModuleID = ms.ModuleID) " +
               " LEFT JOIN [Session] se ON ms.SessionID = se.SessionID) " +
               " WHERE s.TagID = @tagNo " +
               " AND se.SessionDate = cast(getdate() as date) " +
               " AND se.SessionTimeStart <= @Plus30Min " +
               " AND se.SessionTimeEnd >= @Plus30Min ";

Query runs, and produce expected results and inserts them to Attendance table.

My problem is that I would like to make fields TagID and SessionID of Attendance table to be unique, i.e I want one student to register for particular session only ONCE, not multiple times as it is possible now.

EDIT:

An example of my problem. Student scans a tag and the result is placed in Attendance table:

Attendance
--------------------------------------
TagID      SessionID      ScanningTime
4820427      Test1    2013-08-13 15:10:02.590

After a few second, student tries to scan again, and the result is added as new record

Attendance
--------------------------------------
TagID      SessionID      ScanningTime
4820427      Test1    2013-08-13 15:10:02.590
4820427      Test1    2013-08-13 15:10:09.103

This is want I want to avoid. I just want to allow the student to scan ONCE (So it should allow only for one record with combination of 4820427 and Test1). If he will try to do it again, the error message should pop up.

So you want to change the `INSERT` that only unique primary keys are inserted? What kind of duplicates do you have, show some sample data and the expected result (and remove the C# code from the query, otherwise we cannot copy&paste it). — Tim Schmelter, Aug 13 '13 at 14:59

score 2 · Answer 1 · answered Aug 13 '13 at 14:56

2

make TagID and SessionID as a primarykey, a composite key.

open design view,
choose the two fk keys.
and make as primarykey.

answered Aug 13 '13 at 14:56

MrSimpleMind

7,890
3
40
45

But OP has aready noted `PK` for both, so i assume that they are already primary keys. – Tim Schmelter Aug 13 '13 at 14:57
Attendance table has 2 foreign keys, not primary – jaspernorth Aug 13 '13 at 14:58
in design view I'm able to set only 1 primary key of Attendance table – jaspernorth Aug 13 '13 at 14:59
you should be able to check more than one field, what version you use? otherwise use sql for making unique constraints primary key clustred. I will put code in answer too. – MrSimpleMind Aug 13 '13 at 15:12

score 1 · Accepted Answer · edited May 23 '17 at 12:05

1

Just create a UNIQUE constraint... that is exactly what they're for.

ALTER TABLE Attendance ADD CONSTRAINT UNIQUE NONCLUSTERED
(
    TagID,
    SessionID
)

edited May 23 '17 at 12:05

Community

1
1

answered Aug 13 '13 at 15:08

Joe Phillips

49,743
32
103
159

could you please elaborate? I'm totally new to SQL – jaspernorth Aug 13 '13 at 15:09
It seams like this should help. Is it possible to create Unique constraint on two collumns together? – jaspernorth Aug 13 '13 at 15:14

How to prevent from duplicates in SQL Server table

2 Answers2