Oracle check depending on multiple columns

Question

I'm having a problem where I can't really find a solution for because it is very specific and I'm hoping you could help me:

    CREATE TABLE books (
  passagierid NUMBER NOT NULL CONSTRAINT fk_bucht_passagier REFERENCES passagier, --FK
  flightnr VARCHAR2(8) NOT NULL,
  dep_date DATE NOT NULL,
  CONSTRAINT fk_bucht_flug FOREIGN KEY (flightnr, dep_date) REFERENCES flug, --FK
  CONSTRAINT pk_bucht PRIMARY KEY (passagierid, flightnr, dep_date), --PK
  bookingnr NUMBER NOT NULL CONSTRAINT ak_bookingnr UNIQUE,
  [...]
  seatnr VARCHAR2(5) NOT NULL,
  [...]

  -- something like that:
  CONSTRAINT chk_sitzpl_gleiche_buchung CHECK((seatnr,bookingnr,dep_date,flightnr) 
      NOT IN (SELECT seatnr,bookingnr,dep_date,flightnr FROM books))
  );

So inputs like these, where the same flight number, date and seat have to have the same booking number should be checked:

-- insert into books values (...,'PW2345','19.11.2013 15:02:00',1,...,'12c',...)
-- insert into books values (...,'PW2345','19.11.2013 15:02:00',2,...,'12c',...) not ok

But I think the constraint should be on UNIQUE(seatnr,dep_date,flightnr). Do not include bookingnr as this has already a UNIQUE constraint on it. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 24, 2013 at 14:49
And I guess that you don't want 2 customers on the same flight-number, same day and same seat. — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 24, 2013 at 14:49
but its the combination of all 4 that has to be unique so doesnt it also have to be the four? — user3027406
– user3027406, Commented Nov 24, 2013 at 14:50
Your own example shows that the combination of the 3 columns has to be unique. The booking-numbers can be different (like it is, 1 and 2 in your example.) — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Nov 24, 2013 at 14:52

Adarsh · Accepted Answer · 2013-11-24 14:45:33Z

1

What you need is a unique constraint on those columns.

SYNTAX IS:

CREATE TABLE XYZ(
....,
....,
CONSTRAINT <constraint_name> UNIQUE(column-list separated by commas));

You can read more about it here

edited Nov 24, 2013 at 14:45

answered Nov 24, 2013 at 14:37

Adarsh

3,6592 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3027406 Over a year ago

Thank you soo much!! such a simple answer and ive already spent so much time looking for it, damn..^^ i guess i didnt realize that unique can be applied on multiple columns. Anyways, thanks a lot, especially for such a fast answer! Have a great sunday!

Collectives™ on Stack Overflow

Oracle check depending on multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related