this table isnt working in oracle apex can someone help me figure out why-CodePudding

this table isnt working in oracle apex can someone help me figure out why

CREATE TABLE booking (
Booking_ID int GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
Guest_ID int NOT NULL,
Room_ID int  NOT NULL,
Employee_ID int NOT NULL,
booking_date date NOT NULL,
booking_time timestamp NOT NULL, 
booking_desc varchar(100) NOT NULL,
constraint GuestID_FK  references guest(Guest_ID),
constraint RoomID_FK  references room(Room_ID),
constraint EmployeeID_FK  references employee(Employee_ID),
primary key(Booking_ID));

CodePudding user response：

It helps if you specify which error(s) you got when you ran this code.

As table references some other tables, I'll first create them (so that foreign key constraints wouldn't fail because of that); I'll create dummy tables with only primary key columns:

SQL> create table guest (guest_id int primary key);

Table created.

SQL> create table room (room_id int primary key);

Table created.

SQL> create table employee (employee_id int primary key);

Table created.

OK; now, your code:

SQL> CREATE TABLE booking (
  2  Booking_ID int GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  3  Guest_ID int NOT NULL,
  4  Room_ID int  NOT NULL,
  5  Employee_ID int NOT NULL,
  6  booking_date date NOT NULL,
  7  booking_time timestamp NOT NULL,
  8  booking_desc varchar(100) NOT NULL,
  9  constraint GuestID_FK  references guest(Guest_ID),
 10  constraint RoomID_FK  references room(Room_ID),
 11  constraint EmployeeID_FK  references employee(Employee_ID),
 12  primary key(Booking_ID));
constraint GuestID_FK  references guest(Guest_ID),
                                       *
ERROR at line 9:
ORA-00907: missing right parenthesis


SQL>

Aha. So, what's wrong with it? It is an outline constraint clause which has to follow certain syntax.

SQL> create table booking (
  2  booking_id   int generated by default as identity,
  3  guest_id     int not null,
  4  room_id      int not null,
  5  employee_id  int not null,
  6  booking_date date not null,
  7  booking_time timestamp not null,
  8  booking_desc varchar2(100) not null,
  9  constraint guestid_fk    foreign key (guest_id)    references guest(guest_id),
 10  constraint roomid_fk     foreign key (room_id)     references room(room_id),
 11  constraint employeeid_fk foreign key (employee_id) references employee(employee_id),
 12  constraint booking_pk primary key (booking_id));

Table created.

SQL>

Or, you could use inline constraints:

SQL> create table booking (
  2  booking_id   int generated by default as identity constraint pk_book primary key,
  3  guest_id     int constraint fk_book_guest references guest (guest_id) not null,
  4  room_id      int constraint fk_book_room  references room (room_id) not null,
  5  employee_id  int constraint fk_book_emp   references employee (employee_id) not null,
  6  booking_date date not null,
  7  booking_time timestamp not null,
  8  booking_desc varchar2(100) not null);

Table created.

Note that

you don't have to specify NOT NULL constraint for primary key columns; they can't accept null by definition
it is always a good idea to name constraints; you don't have to, but Oracle then creates system-generated names (such as SYS_C008464) which tell you nothing
Oracle recommends us to use varchar2 instead of varchar datatype
you probably don't need both booking_date and booking_time; one column would do: booking_date date as date datatype in Oracle contains both date and time; timestamp - if you meant to use it for time only - shouldn't be used for that purpose as it doesn't contain time only; it contains date and time (with fractional seconds). Do you need to know booking time with such a precision? I, somehow, doubt it