CODEWITHSUNDEEP
validationms-accessms-access-2016
twisted
twisted

Reputation: 802

How to limit a table to 5 records with a certain field combination

I'm using MS Access 2016.

Suppose I have a Student table, a Subject table (ie Geography, History etc) and a StudentSubject table that records which subjects each student has chosen.

StudentSubject contains StudentId and SubjectId as foreign keys.

No student is to choose more that 5 subjects.

Is there any way to define a validation rule on the table such that a given StudentId may not appear in the StudentSubject table more than 5 times?

I could enforce the contraint by introducing an additional table, but if possible, I like to avoid that.

I also want to define a constraint at table level, rather than use vba code that gets invoked when a record is inserted via a form. Access, as far as I know, has no such thing as triggers, as one would have in an sql system.

Upvotes: 2

Views: 250

Answers (1)

Erik A
Erik A

Reputation: 32682

You can use a CHECK constraint to limit the possibilities:

ALTER TABLE StudentSubject 
ADD CONSTRAINT Max5Subjects 
CHECK(
    NOT EXISTS(
        SELECT 1 
        FROM StudentSubject 
        GROUP BY StudentID 
        HAVING Count(StudentID) > 5
    )
)

Note that this might slow down data entry a bit, especially if StudentID is not indexed.

Check constraints need to be executed either in ANSI 92 compatible mode, or using ADO (e.g. using CurrentProject.Connection.Execute). More details here

To execute it using ADO, you can just use this single line in the immediate window:

CurrentProject.Connection.Execute "ALTER TABLE StudentSubject ADD CONSTRAINT Max5Subjects CHECK(NOT EXISTS( SELECT 1 FROM StudentSubject GROUP BY StudentID HAVING Count(StudentID) > 5))"

Also, keep in mind that if somehow there are records that violate the constraint (e.g. because they were present before the constraint got added), your table will get locked down fully.

Upvotes: 2

Related Questions