0

I'm using Oracle SQL and I was wondering whether I can use a sub-query inside a CHECK constraint when I am creating a new table. Please consider the following example:

Suppose I want to create a table called EnrollsIn and it has two attributes - student and course. student is a string that holds an unique student ID and the course attribute holds a string of course code.

CREATE TABLE EnrollsIn (
   student char(8) PRIMARY KEY,
   course char(7),
   -- Insert constraint here (Constraint is written below)
);

INSERT INTO EnrollsIn VALUE ('12345678', 'COMP200');
INSERT INTO EnrollsIn VALUE ('12345678', 'COMP300');
INSERT INTO EnrollsIn VALUE ('12345678', 'COMP400');
INSERT INTO EnrollsIn VALUE ('12345678', 'MATH100');  -- This violates the constraint written below!

Now, I want this table to have a constraint that a single student can only enroll in maximum of 3 courses. In other words, the following query should always have the value of enrollCount <= 3 for each student:

-- I want the value "enrollCount" always <= 3 for all students
SELECT student, count(*) as enrollCount FROM EnrollsIn GROUP BY student;

Am I allowed to use this sub-query when defining the CHECK constraint inside the CERATE TABLE EnrollsIn statement? If so, how can I define this constraint?

AnsonH
  • 2,460
  • 2
  • 15
  • 29
  • 1
    Alas you cannot do this in a check constraint. Such a construct is called a SQL ASSERTION, which is not yet supported by Oracle - or any other major database - although there have been moves to add support. – APC Oct 23 '20 at 16:19
  • 3
    One way to implement this requirement is with a compound trigger: see [this answer](https://stackoverflow.com/a/64464269/146325) for an example. Alternatively you could create a materialized view with a check constraint: [there is an example here](https://stackoverflow.com/a/47280427/146325). – APC Oct 23 '20 at 16:23
  • @APC: I'm not sure the compound-trigger approach will work here. If you insert two rows for the same student in each of two concurrent sessions, the trigger would never detect the problem because it would only see the two rows in each session, not all four rows together, so surely you'd end up with that student enrolled on four courses? – Luke Woodward Oct 23 '20 at 18:23
  • 1
    By the way, Oracle provides `varchar2` for character strings. `char` is for fixed-length strings, which [nobody has ever needed](https://stackoverflow.com/a/42165653/230471), and using it often creates bugs. – William Robertson Oct 23 '20 at 18:59
  • 1
    @LukeWoodward - that's a valid point. For the compound trigger to work we need to lock the STUDENT record, which serializes the process. Not ideal in a Production environment, but okay for a homework solution (which I always assume is the case for student enrolment questions :D ) – APC Oct 24 '20 at 06:47

1 Answers1

1

It's better to ask the documentation about that:

Restrictions on Check Constraints

Check constraints are subject to the following restrictions:

  • Conditions of check constraints cannot contain the following constructs:
    • Subqueries and scalar subquery expressions
    • Calls to user-defined functions
astentx
  • 6,393
  • 2
  • 16
  • 25