Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    8

    Question Logical constraint question

    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!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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})
    Last edited by andrewst; 10-04-08 at 09:08.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •