Logical constraint question

aetherealize · #1 (**permalink**) 10-03-08, 22:23

I have three tables / relvars:

Users,
Groups,
and a linking table between them, Users-Groups.

One of the records in the Groups table is "Students".

If I want to add a Students table (and a linking table Users-Students), how can I impose the following logical constraint?

"A user is associated with the Students group if and only if the user is associated with the Students table."

I don't know if it matters, but I want the Students-Users relationship to be many-to-many.

Any idea is very much appreciated!

andrewst · #2 (**permalink**) 10-04-08, 07:55

If you are looking for a SQL constraint, then you need what is called an "ASSERTION" which would be something like:

Code:

not exists (select userid from user_groups ug 
         where group_id='Students' and not exists 
         (select null from user_students us where us.userid = ug.userid))

Unfortunately, while ASSERTIONS are part of the SQL standard, they aren't implemented in most (any?) DBMSs.

If your DBMS supports materialized views, then these can be used to enforce such complex constraints. I wrote about this here a few years ago (though I must confess I have never taken this any further since, nor used such MVs in a production database!)

Your requirement would be expressed something like this:

Code:

create materialized view user_student_chk_mv
refresh complete on commit as
select userid from user_groups ug 
         where group_id='Students' and not exists 
         (select null from user_students us where us.userid = ug.userid);

alter table user_student_chk_mv
add constraint user_student_chk_mv_chk
check (1=0) deferrable;

However, since you referred to relvars, it sounds like you want a theoretical, Tutorial D solution - which would be something like

IS_EMPTY (user_groups {userid} where group_id='Students' MINUS user_students {userid})

Internet Services

Web Development

Digital Marketing

Consumer Tech