CREATE TABLE Reservations
        (ReservationID  int NOT NULL PRIMARY KEY,
        roomid int NOT NULL,
        start datetime NOT NULL,
        finish datetime NOT NULL,
            CONSTRAINT No_Overlaps
                CHECK (NOT EXISTS
                    (SELECT R1.RoomID
                        FROM Reservations AS R1
                        WHERE start BETWEEN R1.start AND R1.finish and R1.roomid = roomID
                            OR finish BETWEEN R1.start AND R1.finish  and R1.roomid = roomID));

Subqueries are not allowed in this context. Only scalar expressions are allowed.