2

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.

twisted
  • 742
  • 2
  • 11
  • 19
  • As from Access 2010 you can use Data Macros which are similar to triggers. Although the best way would be to use a form event. – Rene Apr 26 '18 at 18:59
  • @Rene Imo a constraint is preferable over form events, since a constraint will also limit the possibilities of directly entering content into the table. This is not a good use case for data macros either. – Erik A Apr 26 '18 at 19:13
  • @Erik, hence why I stated that the best way would be to use an event ;) – Rene Apr 26 '18 at 19:41

1 Answers1

2

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.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thank you Erik. I'll have to brush up on my vba to get that working. Once done, is there any indication in the Access ui that a table has a constraint? – twisted Apr 26 '18 at 20:02
  • Nope, check constraints are not visible in any way in the UI afaik. It requires only a single line of VBA, or you can just switch mode. – Erik A Apr 26 '18 at 20:11
  • @rgh I've included the line, if you're having trouble executing it. Note that while it's not visible in the UI, it does give a descriptive UI message (with the constraint name) when violated. – Erik A Apr 26 '18 at 20:24
  • Got it working, thank you. Didn't realize Access supported that. – twisted Apr 26 '18 at 20:48