How to create a unique key with two columns in SQL-Server

Question

I have 3 tables:

1. StudentDetails(StudentNumber INT {PK}, IndexNumber, FirstName, ...)
2. SubjectDetails(SubjectNumber INT {PK}, SubjectCode, ...)
3. Marks(MarkCode INT {PK}, StudentNumber INT, SubjectNumber INT, Mark, ...)

Can I make StudentNumber and SubjectNumber a composite unique key. (which are already foreign keys)

Answer 1

The UNIQUE constraint designates a column or combination of columns as a unique key. To satisfy a UNIQUE constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls.

In your case, a student may have marks for several subjects, but the same StudentNumber, SubjectNumber combination may not repeat.

CREATE TABLE StudentDetails(StudentNumber INT NOT NULL, 
                            IndexNumber INT NOT NULL,
                            FirstName VARCHAR(255) NOT NULL,
                            PRIMARY KEY (StudentNumber)
                           )

CREATE TABLE SubjectDetails(SubjectNumber INT NOT NULL
               , SubjectCode INT NOT NULL,
              PRIMARY KEY (SubjectNumber))                           


CREATE TABLE Marks (MarkCode  int NOT NULL, 
                    StudentNumber  int  NOT NULL,
                    SubjectNumber int NOT NULL, 
                    Mark int NULL,

                    PRIMARY KEY (MarkCode),
                    CONSTRAINT unicity UNIQUE (StudentNumber,SubjectNumber)
                    )

See : When to use unique composite keys?

pros and cons of using multi column primary keys