CODEWITHSUNDEEP
sqloracle-databaseddl
monfr_
monfr_

Reputation: 23

I can't create a foreign key: missing right parenthesis error

CREATE TABLE hotel_reservation(
    hotel_code VARCHAR2(5),
    room_type  VARCHAR2(75), CONSTRAINT check_room_type CHECK(room_type LIKE 'DELUXE' or room_type LIKE 'SUPERIOR' or room_type LIKE 'MASTERS SUITE') NOT NULL, 
    room_id number(3), CONSTRAINT fk_room_id FOREIGN KEY(room_id) REFERENCES rooms(room_id) NOT NULL
);

it keeps saying missing right parenthesis but I don't see anything missing.

Upvotes: 1

Views: 349

Answers (4)

William Robertson
William Robertson

Reputation: 16001

I prefer single-column constraints inline with the column, as it's a more compact syntax and also you can let the child table inherit its datatype from the parent.

-- Parent table for demo purposes:
create table rooms
( room_id integer primary key );

create table hotel_reservation
( hotel_code varchar2(5)  -- Probably this should be mandatory and a FK to hotels
, room_type  varchar2(75) not null
             constraint check_room_type check (room_type in ('DELUXE','SUPERIOR','MASTERS SUITE'))
, room_id    not null constraint fk_room_id references rooms(room_id) );

Note hotel_reservation.room_id inherits its datatype from rooms.room_id.

Upvotes: 1

Shanaka Madusanka
Shanaka Madusanka

Reputation: 1

CREATE TABLE hotel_reservation( hotel_code VARCHAR(5), room_type VARCHAR(75),

room_id integer, CONSTRAINT fk_room_id FOREIGN KEY(room_id) REFERENCES rooms(room_id) , CONSTRAINT check_room_type CHECK( room_type LIKE 'DELUXE' or room_type LIKE 'SUPERIOR' or room_type LIKE 'MASTERS SUITE' ) );

NOT Null is using for define the columns. Not for Constraints

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

The problem is the NOT NULL at the end. Here is one way to express this definition:

CREATE TABLE hotel_reservation (
    hotel_code VARCHAR2(5),
    room_type  VARCHAR2(75)  NOT NULL,
    CONSTRAINT check_room_type CHECK (room_type in ('DELUXE', 'SUPERIOR', 'MASTERS SUITE')), 
    room_id number(3) NOT NULL,
    CONSTRAINT fk_room_id FOREIGN KEY (room_id) REFERENCES rooms(room_id)
);

I also changed the first check constraint to use in rather than ors.

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142778

I prefer keeping constraints out-of-line, or - maybe / sometimes even better - out of the CREATE TABLE script. Why? It is easier to recreate them, if necessary.

One option:

CREATE TABLE hotel_reservation
(
   hotel_code   VARCHAR2 (5),
   room_type    VARCHAR2 (75) NOT NULL,
   room_id      NUMBER (3) NOT NULL,
   --
   CONSTRAINT check_room_type CHECK
      (   room_type LIKE 'DELUXE'
       OR room_type LIKE 'SUPERIOR'
       OR room_type LIKE 'MASTERS SUITE'),
   CONSTRAINT fk_room_id FOREIGN KEY (room_id) REFERENCES rooms (room_id)
);

And another:

CREATE TABLE hotel_reservation
(
   hotel_code   VARCHAR2 (5),
   room_type    VARCHAR2 (75) NOT NULL,
   room_id      NUMBER (3) NOT NULL
);

ALTER TABLE hotel_reservation ADD
  CONSTRAINT check_room_type CHECK
      (   room_type LIKE 'DELUXE'
       OR room_type LIKE 'SUPERIOR'
       OR room_type LIKE 'MASTERS SUITE');

ALTER TABLE hotel_reservation ADD
  CONSTRAINT fk_room_id FOREIGN KEY (room_id) 
  REFERENCES rooms (room_id);

Upvotes: 1

Related Questions