CODEWITHSUNDEEP
sqloracle-database
user3027406
user3027406

Reputation: 13

Oracle check depending on multiple columns

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

Upvotes: 1

Views: 57

Answers (1)

Adarsh
Adarsh

Reputation: 3641

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

Upvotes: 1

Related Questions