CODEWITHSUNDEEP
databasedatabase-designumlrelational-databasedatabase-schema
Garry Cross
Garry Cross

Reputation: 35

Database Constraints

I have a database with three tables: TEAM, PLAYER, CONTRACT.

TEAM(teamID, name)
PLAYER(playerID, name)
CONTRACT(contractID, playerID, teamID, dateOfSigning, expirationDate)

In this database, I want the constraint that a player can't have multiple contracts at the same time. I mention that I want that expired contracts remain registered in my database.

For example:

CONTRACT(1,1,1, 01/01/2000, 01/01/2005)
CONTRACT(1,1,1, 01/01/2001, 01/01/2003)

So, My player has a contract from 01/01/2000 to 01/01/2005 and another contract from 01/01/2001 to 01/01/2003. This is not possible.

Upvotes: 2

Views: 60

Answers (3)

T I
T I

Reputation: 9933

If in the contract table you made the playerid unique and add another table to keep a history of contracts. A player can then only have one contact but could have had many.

In the business layer you need to decide if the contract being inserted should overwrite the current contract. If so, archive and remove the current contract from the contract table and insert the new one.

Upvotes: 0

philipxy
philipxy

Reputation: 15118

Two different contracts for a player do not overlap if and only if one starts after the other finishes. So they overlap if and only if NOT(one starts after the other finishes). The constraint you want is that no rows of contractID pairs satisfy the condition that they overlap:

    CONTRACT(c1.contractID, playerID, c1.teamID, c1.dateOfSigning, c1.expirationDate)
AND CONTRACT(c2.contractID, playerID, c2.teamID, c2.dateOfSigning, c2.expirationDate)
AND c1.contractID <> c2.contractID
AND NOT(c1.dateOfSigning > c2.expirationDate
    OR c2.dateOfSigning > c1.expirationDate)

This means the following set of rows is empty:

SELECT c1.contractID, c2.contractID
FROM CONTRACT c1
JOIN CONTRACT c2
ON c1.playerID = c2.playerID
AND c1.contractID <> c2.contractID
WHERE NOT(c1.dateOfSigning > c2.expirationDate
    OR c2.dateOfSigning > c1.expirationDate)

If DBMSes supported SELECTs in CHECK then you could have a CONTRACT constraint:

CHECK (SELECT CASE
        WHEN EXISTS (SELECT 1 FROM (...))
        THEN 1
        ELSE 0 END)

But they don't so you have to test this SELECT in a trigger or stored procedure. (As explained in another answer.) Arbitrary constraint checking is not well-supported by DBMSes.

You can reduce computation by keeping expired contracts in a different table than active ones. (They aren't going to overlap with new ones.) But I have just used the table you gave.

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

This is not directly enforceable through declarative constraints. Unfortunately, current DBMSes don't offer "unique range" constraint (to my knowledge at least).

You are left with two options:

  • Either enforce this through your business logic (usually triggers or stored procedures). But be careful about concurrency (you may need to employ locking to avoid race conditions between concurrent transactions fiddling with related dates).
  • Or decrease the granularity of time. For example, if you divide the year to months, and declare which months are "occupied" by the given contract (by having separate row for each month), then you can easily (and declaratively!) enforce no duplicates can exist for the same month.

NOTE: The latter can be even more granular, e.g. days, but that will produce more rows in the database and cause problems with leap years etc. - you'll need to find the right balance for your particular case.

Upvotes: 1

Related Questions