mySQL Composite Key, Primary Key, Unique Key and References to the composite key

Question

I have a question about the specific keys and how to reference them. I am learning and this would be very helpful thank you in advance.

In SQL what is the difference between these styles?

Also, when creating a composite primary key the foreign must also have a composite foreign key to match the composite primary key. In the first table, I can reference it if I added unique in the specification without creating a foreign composite but if I remove unique I cannot reference the primary key since it is a composite. Why does adding unique allow me to reference the composite primary key in the first table without having a foreign composite key? Also, why can I add the UNIQUE keys in the composite key?

Also, would this be similar to adding a UNIQUE KEY composite such as in the 2nd table? The third table does not allow me to reference that table since I would have to have a composite foreign key.

CREATE TABLE college.LocationNTime(
    CourseN INT,
    `Quarter` VARCHAR(30) UNIQUE,
    DayTime VARCHAR(30) UNIQUE,
    RoomN INT,
    PRIMARY KEY (CourseN, `Quarter`, DayTime),
    FOREIGN KEY (CourseN) REFERENCES Course(CourseN)
);

CREATE TABLE college.LocationNTime(
    CourseN INT,
    `Quarter` VARCHAR(30),
    DayTime VARCHAR(30),
    RoomN INT,
    PRIMARY KEY (CourseN),
    UNIQUE KEY(`Quarter`, DayTime),
    FOREIGN KEY (CourseN) REFERENCES Course(CourseN)
);

CREATE TABLE college.LocationNTime(
    CourseN INT,
    `Quarter` VARCHAR(30),
    DayTime VARCHAR(30),
    RoomN INT,
    PRIMARY KEY (CourseN, `Quarter`, DayTime),
    FOREIGN KEY (CourseN) REFERENCES Course(CourseN)
);

Answer 1

PRIMARY KEY (CourseN, `Quarter`, DayTime),
FOREIGN KEY (CourseN) REFERENCES Course(CourseN)

In this construct, you are making a composite primary key with columns (CourseN, Quarter DayTime). This implies that any combination of values of these three columns must be unique in the table. Also, you indicate that CourseN is a foreign key that references the synonym column in table Course, so every value of column CourseN in your referring table LocationNTime must be present in the referred table Course.

This looks like a valid set up, where LocationNTime is like a dependent table of Course (like a child table or the-like).

---

PRIMARY KEY (CourseN),
UNIQUE KEY(`Quarter`, DayTime),
FOREIGN KEY (CourseN) REFERENCES Course(CourseN)

Here, CourseN is the primary key of the table, so each value in this column must be unique. But it is also a foreign key to Course. This does not really make sense in terms of normalization: if each and every record in LocationNTime relate to a unique record in Course, then why create two tables instead of storing all the data in a single record in Course?

On top of this, you are creating a unique constraint on (Quarter, DayTime), hence signaling that the combination of values in these columns unique.

This design looks flawned.

---

Consider the following dataset:

CourseN    Quarter    DayTime
1          1          14:00:00
1          2          14:10:00
1          3          14:00:00
1          4          14:10:00

The first design will allow this dataset, while the second will not, since the are duplicates in CourseN. The following dataset would not be allowed either, since there are duplicates in (Quarter, DayTime):

CourseN    Quarter    DayTime
1          1          14:00:00
2          2          14:10:00
3          1          14:00:00
4          2          14:10:00

I believe that the first design is probably what you need.

---

Finally: you seem to think that a column that has a foreign key constraint (here, LocationNTime(CourseN)) needs to be UNIQUE: this is not the case. The only thing it needs is an index (and MySQL creates it for you under the hood when you declare it anyway). It's the column that is referred by the foreign key (here Course(CourseN)) that needs to unique.