1

I have the following table schema;

CREATE TABLE LOCKS (
    lock_uid INTEGER NOT NULL,
    file_uid VARCHAR2(32 char) NOT NULL UNIQUE,
    owner VARCHAR2(255 char) NOT NULL,
    expires DATE NOT NULL,
    PRIMARY KEY (lock_uid),
    FOREIGN KEY (owner) REFERENCES user(username)
);

The table is responsible for recording file locks that users of my application might hold on files in the system. The unique constraint on the file_uid column will prevent more than one lock being held for any file.

I would like some advice on how to allow people to obtain a lock on a file when there is already a lock on the file, but it has expired (ie, expires < sysdate).

My initial thought is to use a trigger on insert so that if there are any file locks that have expired in the table, it will remove them before trying to insert the new lock. This will prevent the duplicate key constraint violation which would occur otherwise.

Can anyone think of a better way? Ideally I would enforce this purely via declarative constraints on the table but it doesn't seem possible.

Paul
  • 11
  • 1

0 Answers0