Oracle SQL Developer - Adding Constraints on Foreign Key

The foreign key alone can only represents a one-to-many relationship. If you want to limit the "many" portion to a specific number, you'll need to either:

1. Do it in the application code or triggers.
2. Or redesign the rest of the database to "help" the FK in achieving that goal.

The (1) is easy to implement but easy to get wrong: you'll have to carefully employ locking to avoid race conditions in the concurrent environment:

• Let's say two concurrent transactions are attempting to connect the same student to a different course.
• The first transaction counts the courses currently connected to the student and finds that there are 3 of them. All good and well.
• The second transaction does the same and also sees only 3 courses (because the first transaction hasn't committed yet).
• So both transactions think they won't exceed the allowed number and both happily proceed with inserting their student-course connection.
• End result: the student is connected to 5 courses, violating the rule that it can be connected to only 4.

To avoid that, you'll need to serialize these operations, probably by locking the student through SELECT ... FOR UPDATE.

The (2) could be implemented by changing the key design, and then limiting the values a key can have. For example, enforcing that a student can take at most 4 courses can be done like this:

CREATE TABLE STUDENT (
    STUDENT_ID INT PRIMARY KEY
);

CREATE TABLE COURSE (
    COURSE_ID INT PRIMARY KEY
);

CREATE TABLE STUDENT_COURSE (
    STUDENT_ID INT REFERENCES STUDENT,
    COURSE_ID INT REFERENCES COURSE,
    COURSE_NO INT NOT NULL CHECK (COURSE_NO IN (1, 2, 3, 4)),
    PRIMARY KEY (STUDENT_ID, COURSE_ID),
    UNIQUE (STUDENT_ID, COURSE_NO)
);

The combination of CHECK and UNIQUE constraints means the DBMS itself will refuse to connect the same student to more than 4 courses.

Doing this will succeed:

INSERT INTO STUDENT_COURSE VALUES (11, 111, 1);
INSERT INTO STUDENT_COURSE VALUES (11, 222, 2);
INSERT INTO STUDENT_COURSE VALUES (11, 333, 3);
INSERT INTO STUDENT_COURSE VALUES (11, 444, 4);

But doing this obviously won't (CHECK constraint violation):

INSERT INTO STUDENT_COURSE VALUES (11, 555, 5);

BTW, when student is already connected to some courses and you want to find the remaining free "slots", you can do it like this:

SELECT NEW_NO
FROM (
    SELECT
        COURSE_NO + 1 NEW_NO,
        LEAD (COURSE_NO) OVER (ORDER BY COURSE_NO) NEXT_NO
    FROM STUDENT_COURSE
    WHERE STUDENT_ID = 11
)
WHERE NEW_NO <> NEXT_NO OR NEXT_NO IS NULL;

[SQL Fiddle]

Looks like you already learned about basic constraints in oracle (like check and foreign key). For the more complicated scenarious you may use triggers. Triggers are executed on set of events (when you try to insert, update or delete a record into table).

I assume that student takes only 4 courses mean that every student could have only 4 records in grades table. In trigger you can do exactly that. So you create a trigger before (there is also after ) insert or update, inside a trigger you check how many records student from inserting line already have and accept or decline particular insert/update operation.

Than you can write similar trigger for 25 students in a course. It will be another trigger on the same insert/update into grates table.

For exact syntax try documnentation first, triggers are fun :)

Also, later you may consider writing stored procedures as a more advanced way of implementing business logic in database. good luck!

CREATE TABLE STUDENT (
    STUDENT_ID INT PRIMARY KEY
);

CREATE TABLE STUDENT_COURSE (
    STUDENT_ID INT REFERENCES STUDENT,
    COURSE_ID INT REFERENCES COURSE,
    COURSE_NO INT NOT NULL CHECK (COURSE_NO IN (1, 2, 3,

CREATE TABLE COURSE (
    COURSE_ID INT PRIMARY KEY
);

),
    PRIMARY KEY (STUDENT_ID, COURSE_ID),
    UNIQUE (STUDENT_ID, COURSE_NO)
);