CODEWITHSUNDEEP
sqloracle-databaseconstraints
Leon Husmann
Leon Husmann

Reputation: 684

Oracle check constraint with subquerys [exercise]

I want to solve this exercise (should be solvable). You have the following table. The table includes seat reservation, all seats between a start-/enddate are reserved.

CREATE TABLE reservation ( 
    owner CHAR(10) NOT NULL PRIMARY KEY,
    start INTEGER  NOT NULL,
    end   INTEGER  NOT NULL
)

Extend this table definition with an constraint what checks, that no place will be reserved twice.

As far as I know it isn't possible to use a subquery in a check constraint. So what kind of constraint can I use?

Upvotes: 3

Views: 803

Answers (2)

MT0
MT0

Reputation: 167972

You can solve this using a materialized view:

CREATE TABLE reservation ( 
    owner   CHAR(10) NOT NULL PRIMARY KEY,
    "start" INTEGER  NOT NULL,
    "end"   INTEGER  NOT NULL
);

CREATE MATERIALIZED VIEW LOG ON reservation
   WITH SEQUENCE, ROWID("start","end")
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW reservation_MV
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT "start" + LEVEL - 1 AS table_number
      FROM   reservation
      CONNECT BY "start" + LEVEL - 1 <= "end";

ALTER TABLE reservation_MV ADD CONSTRAINT reservation__mv__pk
  PRIMARY KEY ( table_number );

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

This cannot be done with a single CHECK constraint because essentially it is a constraint that relies on more than one rows of the table.

It's similar to a UNIQUE constraint in a way. In a unique constraint, no two rows can have the same values. In this, no two rows can have overlapping ranges. So, if we could replace equality condition (=) with a condition that check for overlapping ranges, it would be just right.

In another DBMS (Postgres), it is actually possible to enforce exactly this and similar constraints using the proprietary EXCLUDE constraint.

In Oracle now and other DBMS that have the common FOREIGN KEY, UNIQUE and CHECK constraints, it is actually possible but complicated, if you are allowed to change the table design by adding another column:

CREATE TABLE reservation ( 
    owner CHAR(10) NOT NULL PRIMARY KEY,
    start INTEGER  NOT NULL,
    end   INTEGER  NOT NULL,
    previous_end INTEGER NULL,

    CONSTRAINT valid_range
        CHECK (start <= end),
    CONSTRAINT unique_end
        UNIQUE (end), 
    CONSTRAINT previous_range_fk
        FOREIGN KEY (previous_end) 
        REFERENCES reservation (end), 
    CONSTRAINT valid_previous
        CHECK (previous_end < start)
) ;

and we are good to go. You just have to provide the previous_end value correctly, essentially making the table a linked list. All the constraints work together to ensure that ranges do not overlap.

Upvotes: 3

Related Questions