unique constraint check based on a parameter in the parent table

Question

Table User has (userId, scoreType, ...)

@Table(name = "User", uniqueConstraints =
   @UniqueConstraint(columnNames = userId))

-- scoreType could be either points or percentage

Table UserScore (id, userId, points, percentage)

I would like to provide the flexibility to store either points or percentage based on user.scoreType. So if scoreType for a user is marked as points, we can assume that UserScore table will only have points populated and vice-versa.

a. I am assuming because of the above requirement, I will not be able to add a nullable = false check on either UserScore.points or UserScore.percentage.

b. How should I define the @UniqueConstraint check for the UserScore table. Should it be

@Table(name = "UserScore", uniqueConstraints = { 
   @UniqueConstraint(columnNames = userId, points), 
   @UniqueConstraint(columnNames = userId, percentage))

Would appreciate any other view points on this issue

Answer 1

a. I am assuming because of the above requirement, I will not be able to add a nullable = false check on either UserScore.points or UserScore.percentage.

Correct

b. How should I define the @UniqueConstraint check for the UserScore table. Should it be @Table(name = "UserScore", uniqueConstraints = { @UniqueConstraint(columnNames = userId, points), @UniqueConstraint(columnNames = userId, percentage))

Neither will work for the business rules you've talked about. @UniqueConstraint(columnNames = userId, points) will only allow unique combinations of userid and points; @UniqueConstraint(columnNames = userId, percentage) will only allow unique combinations of userid and percentage.

Answer 2

You could use the same column for points and percentage, like score int. Then you store percentage with "assumed decimal point", like for 25.7% score = 257. With this, there is no need for the constraint.

You could also move scoreType into UserScore table to allow for mix of scoring for each user -- after all, it does describe the score.

CREATE TABLE UserScore (
     id int
    ,[userID] int
    ,scoreType varchar(3)
    ,score int
    )

When you query the table (SQL server):

SELECT
   id
  ,[userId]
  ,CASE scoreType WHEN 'pts' THEN score ELSE 0 END AS points
  ,CASE scoreType WHEN 'pts' THEN 0.0 ELSE (cast(score AS decimal(4,1)) /10.0) END AS percentage
FROM UserScore

Answer 3

What I'm reading out of your description is that a user can only have one entry in userScore, and that entry can only contain points or percentage, but not both. If so, the user and userScore tables should just be one table with the columns userId, points, percentage. scoreType is already implicit and can be omitted. And then use a check constraint to ensure that only one of points and percentage is filled in. So something like

CREATE TABLE User (
    userId int PRIMARY KEY,
    points int,
    percentage numeric,
    CHECK (points IS NULL OR percentage IS NULL),
    CHECK (points IS NOT NULL OR percentage IS NOT NULL)  -- if desired
);

If you really want a scoreType column, create a view:

CREATE VIEW UserView AS
    SELECT userId,
           CASE WHEN points IS NOT NULL THEN 'points'
                ELSE 'percentage' END AS scoreType
    ...
    FROM User;

Answer 4

There are two cases here, based on how much change you are able/allowed to introduce into the existing schema.

If you can change the schema, the simplest solution here would be to only have one field and use the scoreType field in the user record to determine how to interpret the data. If your percentage is always two decimal places then you could create your table like this:

CREATE TABLE userScore (
    id INT,
    userID INT,
    scoreValue INT
);

And query it like this:

SELECT id,
       CASE WHEN scoreType = 'points' THEN scoreValue ELSE NULL END as points,
       CASE WHEN scoreType = 'percentage' THEN scoreValue / 100.0 ELSE NULL END as percentage
WHERE userID = ?

Now there's no need for a constraint of any sort.

If the user's scoreType field can change and the field (points or percentage) used would dependent on scoreType's value at the time the score is stored, you have to add scoreType to my version of userScore in order to keep the rules for interpreting that data with each record since you can't rely on the parent record being consistent (but it does eliminate the need for a join).

---

If you have no option to change the schema, you could do it with ON INSERT/UPDATE triggers to fetch the scoreType field to verify that the resulting record will only have a value in the proper field. In PostgreSQL (the DB I'm most familiar with), the syntax for the meat of the trigger function would be something like this (for clarity, v_* fields are variables/parameters):

-- for trigger ON INSERT/UPDATE to userScore

SELECT INTO v_scoreType scoreType FROM user WHERE userID = v_userID;
IF   (v_scoreType = 'points' AND NEW.points IS NOT NULL AND NEW.percentage IS NULL)
  OR (v_scoreType = 'percentage' AND NEW.points IS NULL AND NEW.percentage IS NOT NULL) THEN
    RETURN NEW; -- this record is OK
ELSE
    RAISE EXCEPTION 'User Score type is %, tried to set points = % and percentage = %', v_scoreType, NEW.points, NEW.percentage;
END IF;

Hopefully one of these ideas helps!

Answer 5

Maybe you can try to default the percentage to some absurd value when the score type is points and do the same for points when scoreType is percentage. Scenario 1: User= Bob, ScoreType = Points

       User= Bob, Points=2.5,Percentage -100000 (Default value for percentage) 

And then you can define a composite primary key

@Table(name = "UserScore", uniqueConstraints = { @UniqueConstraint(columnNames = userId, points,percentage)}