Specifying a limit for a value in SQL when a particular value is met-CodePudding

In SQL how is it possible to set a limit for an attribute value when another condition is met? For example, a student can borrow a max of 3 resources and staff can borrow a max of 6

Create Table USER_T
(   UserID int PRIMARY KEY NOT NULL,
    UserType varchar (25) NOT NULL,
    No_of_Resources_Borrowed int check (No_of_Resources_Borrowed=<6)
    CONSTRAINT con_user_typ check (UserType IN ('Student','Staff'))
);

CodePudding user response：

You want a CONSTRAINT clause

Create Table USER_T
(   UserID int PRIMARY KEY NOT NULL,
    UserType varchar (25) NOT NULL,
    No_of_Resources_Borrowed int NOT NULL,
    CONSTRAINT Resources_by_type CHECK (
      No_of_Resources_Borrowed 
      <= case UserType when 'Student' then 3 when 'Staff' then 6 else 2147483647 end ),
    CONSTRAINT con_user_typ CHECK (UserType IN ('Student','Staff'))
    );