DROP TABLE libraryuser CASCADE CONSTRAINTS;
DROP TABLE staff CASCADE CONSTRAINTS;
DROP TABLE book CASCADE CONSTRAINTS;
DROP TABLE booktransaction CASCADE CONSTRAINTS;
DROP TABLE membership CASCADE CONSTRAINTS;
DROP TABLE library CASCADE CONSTRAINTS;
DROP TABLE reservation CASCADE CONSTRAINTS;
CREATE TABLE libraryuser
(user_id VARCHAR(7),
user_password VARCHAR(12) NOT NULL,
user_lname VARCHAR(20) NOT NULL,
user_fname VARCHAR(20) NOT NULL,
user_email VARCHAR(100) NOT NULL,
user_ic VARCHAR(12) NOT NULL,
user_tel_no VARCHAR(11) NOT NULL,
user_age VARCHAR(2) NOT NULL,
user_gender VARCHAR(6) NOT NULL,
member_id VARCHAR(5),
CONSTRAINT user_user_id_pk PRIMARY KEY(user_id),
CONSTRAINT user_member_id_fk FOREIGN KEY(member_id) REFERENCES membership(member_id));
CREATE TABLE staff
(staff_id VARCHAR(7),
staff_password VARCHAR(12) NOT NULL,
staff_email VARCHAR(100) NOT NULL,
staff_lname VARCHAR(10) NOT NULL,
staff_fname VARCHAR(20) NOT NULL,
staff_ic VARCHAR(12) NOT NULL,
staff_tel_no VARCHAR(11) NOT NULL,
staff_gender VARCHAR(6) NOT NULL,
staff_position VARCHAR(20) NOT NULL,
staff_hire_date DATE NOT NULL,
staff_dismissal_date DATE NOT NULL,
library_id VARCHAR(5),
CONSTRAINT staff_staff_id_pk PRIMARY KEY(staff_id),
CONSTRAINT staff_library_id_fk FOREIGN KEY(library_id) REFERENCES library(library_id));
CREATE TABLE book
(book_id VARCHAR(5),
book_name VARCHAR(50) NOT NULL,
book_status VARCHAR(10) NOT NULL,
book_publish_date DATE NOT NULL,
book_genre VARCHAR(10) NOT NULL,
book_price FLOAT(10) NOT NULL,
book_type VARCHAR(20) NOT NULL,
transaction_id VARCHAR(5),
CONSTRAINT book_book_id_pk PRIMARY KEY(book_id),
CONSTRAINT book_transaction_id_fk FOREIGN KEY(transaction_id) REFERENCES booktransaction(transaction_id));
CREATE TABLE booktransaction
(transaction_id VARCHAR(5),
transaction_date DATE NOT NULL(10),
book_status VARCHAR(10) NOT NULL,
return_date DATE NOT NULL(10),
return_time TIME(5) NOT NULL,
staff_id VARCHAR(7),
book_id VARCHAR(5),
user_id VARCHAR(7),
CONSTRAINT booktransaction_transaction_id_pk PRIMARY KEY(transaction_id),
CONSTRAINT booktransaction_user_id_fk FOREIGN KEY(user_id) REFERENCES libraryuser(user_id),
CONSTRAINT booktransaction_book_id_fk FOREIGN KEY(book_id) REFERENCES book(book_id),
CONSTRAINT booktransaction_staff_id_fk FOREIGN KEY(staff_id) REFERENCES staff(staff_id));
CREATE TABLE membership
(member_id VARCHAR(5),
member_status VARCHAR(20) NOT NULL,
member_start_date DATE NOT NULL(10),
member_experied_date_ DATE NOT NULL(10),
user_id VARCHAR(7),
CONSTRAINT membership_member_id_pk PRIMARY KEY(member_id),
CONSTRAINT membership_user_id_fk FOREIGN KEY(user_id) REFERENCES libraryuser(user_id));
CREATE TABLE library
(library_id VARCHAR(5),
library_name VARCHAR(35) NOT NULL,
library_branches VARCHAR(15) NOT NULL,
library_location VARCHAR(15) NOT NULL,
CONSTRAINT library_library_id PRIMARY KEY(library_id));
CREATE TABLE reservation
(reservation_id VARCHAR(5),
reservation_date DATE NOT NULL(30),
reservation_time DATE NOT NULL(5),
staff_id VARCHAR (7),
user_id VARCHAR(7),
book_id VARCHAR(5),
CONSTRAINT reservation_reservation_id_pk PRIMARY KEY(reservation_id),
CONSTRAINT reservation_staff_id_fk FOREIGN KEY(staff_id) REFERENCES staff(staff_id),
CONSTRAINT reservation_user_id_fk FOREIGN KEY(user_id) REFERENCES user(user_id),
CONSTRAINT reservation_book_id_fk FOREIGN KEY(book_id) REFERENCES book(book_id));
INSERT INTO libraryuser (user_id, user_password, user_lname, user_fname, user_email,
user_ic, user_tel_no, user_age, user_gender, member_id) values
('1906510', '987654321582', 'Chua', 'Yi Wen', '[email protected]', '990101-07-0560',
'012-7103379', '23', 'Female', '10026');
INSERT INTO libraryuser (user_id, user_password, user_lname, user_fname,
user_email, user_ic, user_tel_no, user_age, user_gender, member_id) values
('1903932', '774935718254', 'Looi', 'Phoebe', '[email protected]', '001128-08-1799',
'016-5184564', '22', 'Male', '10027');
INSERT INTO libraryuser (user_id, user_password, user_lname, user_fname,
user_email, user_ic, user_tel_no, user_age, user_gender, member_id) values
('1906463', '127489546217', 'Khor', 'Qiao Zher', '[email protected]', '010621-01-5371',
'011-1037894', '21', 'Male', '10028');
INSERT INTO libraryuser (user_id, user_password, user_lname, user_fname,
user_email, user_ic, user_tel_no, user_age, user_gender, member_id) values
('1906537', '845761826749', 'Razak', 'bin Osman', '[email protected]',
'000327-14-0011','016-7816964', '22', 'Male', '10029');
INSERT INTO libraryuser (user_id, user_password, user_lname, user_fname,
user_email, user_ic, user_tel_no, user_age, user_gender, member_id) values
('1704351', '130088252503', 'Saw', 'Wai Thean', '[email protected]', '980430-44-3791',
'017-2510110', '24', 'Male', '10030');
INSERT INTO libraryuser (user_id, user_password, user_lname, user_fname,
user_email, user_ic, user_tel_no, user_age, user_gender, member_id) values
('2103003', '175496820031', 'Ching', 'Joexuan', '[email protected]', '031008-03-0100',
'012-7710253', '19', 'Female', '10031');
INSERT INTO staff (staff_id, staff_password, staff_email, staff_lname,
staff_fname, staff_ic, staff_tel_no, staff_gender, staff_position,
staff_hire_date, staff_dismissal_date, library_id) values
('0000100', '874517896423', '[email protected]', 'Ong', 'Grace',
'811231-09-6910', '014-7415628', 'Female', 'Computer Support Specialist', '04-05-2005', '-', '43431');
INSERT INTO staff (staff_id, staff_password, staff_email, staff_lname,
staff_fname, staff_ic, staff_tel_no, staff_gender, staff_position,
staff_hire_date, staff_dismissal_date, library_id) values
('0000101', '210021657784', '[email protected]', 'Aqila', 'binti Sofra', '900401-08-3318',
'018-7710605', 'Female', 'Librarian', '01-01-2016', '10-03-2020', '-', '43432');
INSERT INTO staff (staff_id, staff_password, staff_email, staff_lname,
staff_fname, staff_ic, staff_tel_no, staff_gender, staff_position,
staff_hire_date, staff_dismissal_date, library_id) values
('0000102', '334157894666', '[email protected]', 'Lam', 'Wei Hong', '740228-55-5763',
'012-3457741', 'Male', 'Library Assistant', '28-11-2002', '43431');
INSERT INTO staff (staff_id, staff_password, staff_email, staff_lname,
staff_fname, staff_ic, staff_tel_no, staff_gender, staff_position,
staff_hire_date, staff_dismissal_date, library_id) values
('0000103', '914520001201', '[email protected]', 'Vincent', 'Chew', '890331-12-0369',
'018-7516888', 'Male', 'Libraria', '10-06-2019', '-', '43431');
INSERT INTO staff (staff_id, staff_password, staff_email, staff_lname,
staff_fname, staff_ic, staff_tel_no, staff_gender, staff_position,
staff_hire_date, staff_dismissal_date, library_id) values
('0000104', '645514201254', '[email protected]','Lim', 'Wei Kun',
'900109-13-1111',
'016-7614785', 'Male', 'Library Technician', '10-06-2019', '-', '43432');
INSERT INTO staff (staff_id, staff_password, staff_email, staff_lname,
staff_fname, staff_ic, staff_tel_no, staff_gender, staff_position,
staff_hire_date, staff_dismissal_date, library_id) values
('0000105', '745143320565', '[email protected]','Chia', 'Wen Ning','681008-09-3456',
'010-7770404', 'Female', 'Manager', '20-12-2003', '20-02-2021', '43432');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('71700', 'The Brain That Changes Itself', 'Borrowed', '01-04-2007',
'Non-fiction', '164.44', 'Neuroplasticity','54321');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('71800', 'The House of Mirth', 'Borrowed', '14-10-1905', 'Fiction', '54.92', 'Drama', '54322');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('71900', 'The Yellow Birds', 'Available', '11-09-2012','Fiction', '117.85', 'War story', '-');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('72000', 'Anne of Green Gables', 'Damaged', '20-06-1908', 'Fiction', '79.00','Bildungsroman', '-');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('72100', 'Harry Potter', 'Borrowed', '26-06-1997', 'Fiction', '659.33', 'Fantasy', '54323');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('72200', 'A Stranger in the House', 'Borrowed', '27-07-2017', 'Fiction', '42.17', 'Thriller', '54324');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('72300', 'Cold Comfort Farm', 'Borrowed', '08-09-1932', 'Fiction', '52.30', 'Comedy', '54325');
INSERT INTO book (book_id, book_name, book_status, book_publish_date,
book_genre, book_price, book_type, transaction_id) values
('72400', 'Anxious People', 'Available', '25-04-2019', 'Fiction', '68.22', 'Humor', '-');
INSERT INTO booktransaction(transaction_id, transactions_date, book_status,
return_time, staff_id,book_id, user_id) values
('54321','20/01/2022','Borrowed','28/01/2022','13:50','0000101','71700','1906510');
INSERT INTO booktransaction(transaction_id, transactions_date, book_status,
return_time, staff_id, book_id, user_id) values
('54322','01/03/2022','Borrowed','07/03/2022','10:56','0000101','71800','1906537');
INSERT INTO booktransaction(transaction_id, transactions_date,
book_status,return_time,staff_id,book_id,user_id) values
('54323','14/03/2022','Borrowed','20/03/2022','12:00','0000102','72100','1906463');
INSERT INTO booktransaction(transaction_id,transactions_date,book_status,
return_time,staff_id,book_id,user_id) values
('54323','23/03/2022','Borrowed','04/04/2022','15:30','0000103','72200','1903932');
INSERT INTO booktransaction(transaction_id,transactions_date,book_status,
return_time,staff_id,book_id,user_id) values
('54325','01/04/2022','Borrowed','04/03/2022','15:30','0000102','72300','2103003');
INSERT INTO membership(member_id,member_status,
member_start_date,member_expried_date_,user_id) values
('10030','Non-active','26/06/2017','20/12/2021','1704351');
INSERT INTO membership(member_id,member_status,
member_start_date,member_expried_date_,user_id) values
('10026','Active','03/03/2020','09/09/2024','1906510');
INSERT INTO membership(member_id,member_status,
member_start_date,member_expried_date_,user_id) values
('10028','Active','01/09/2019','13/07/2023','1906463');
INSERT INTO membership(member_id,member_status,
member_start_date,member_expried_date_,user_id) values
('10031','Active','14/05/2021','21/03/2025','2103003');
INSERT INTO membership(member_id,member_status,
member_start_date,member_expried_date_,user_id) values
('10027','Active','31/10/2019','20/05/2022','1903932');
INSERT INTO membership(member_id,member_status,
member_start_date,member_expried_date_,user_id) values
('10029','Active','07/07/2022','09/09/2024','1906537');
INSERT INTO library(library_id,library_name,
library_branches,library_location) values
('43431','UTAR Main Library','Malasyia','Kampar');
INSERT INTO library(library_id,library_name,
library_branches,library_location) values
('43432','UTAR Mary KUOK Pick Hoo Library','Malasyia','Sungai Long');
INSERT INTO reservations(reservation_id,reservation_date,
reservation_time,staff_id,book_id) values
('11111','08/03/2022','10:00','0000101','1906537','71800');
INSERT INTO reservations(reservation_id,reservation_date,
reservation_time,staff_id,book_id) values
('11112','25/03/2022','13:00','0000102','1903932','72100');
INSERT INTO reservations(reservation_id,reservation_date,
reservation_time,staff_id,book_id) values
('11113','06/04/2022','14:00','0000103','2103003','72000');
COMMIT;
For my situation, now only table library can run and created. Others fail with a syntax error, maybe I got others wrong too. I already try to fix problems but it still failure to execute. I think maybe CASCADE CONSTARINTS part has problem and cause the whole error happens.
Error: near line 2: near "CASCADE": syntax error
Error: near line 3: near "CASCADE": syntax error
Error: near line 4: near "CASCADE": syntax error
Error: near line 5: near "CASCADE": syntax error
Error: near line 6: near "CASCADE": syntax error
Error: near line 7: near "CASCADE": syntax error
Error: near line 8: near "CASCADE": syntax error
When I use sql compiler to run, the output shows
Error: near line 137: 13 values for 12 columns
Error: near line 143: 11 values for 12 columns
Error: near line 203: no such table: booktransaction
Error: near line 207: no such table: booktransaction
Error: near line 211: no such table: booktransaction
Error: near line 215: no such table: booktransaction
Error: near line 219: no such table: booktransaction
Error: near line 223: no such table: membership
Error: near line 227: no such table: membership
Error: near line 231: no such table: membership
Error: near line 235: no such table: membership
Error: near line 239: no such table: membership
Error: near line 243: no such table: membership
Error: near line 255: no such table: reservations
Error: near line 259: no such table: reservations
Error: near line 263: no such table: reservations
Error: near line 268: cannot commit - no transaction is active
[Execution complete with exit code 1]
This is the compiler result.
I really don't know where the error is. Maybe my code is bad code?
CodePudding user response：
myCompiler.IO is using SQL Lite, it isn't Oracle, so that is why the script failed the syntax check.
as shown here: https://www.mycompiler.io/view/1POwrlGMsGO if you run the standard SQL Lite version check, it returns a value:
select sqlite_version();
3.31.1
You should try an actual SQL IDE or sites like DB Fiddle, SQL Fiddle or db<>fiddle that let you specify the RDBMS engine to use.
CodePudding user response：
Although you tagged the question with the Oracle tag, that's clearly not Oracle because
- its error messages don't look like that
- there's no
timedatatype there datedatatype doesn't have size- you can't reference a table whose name is
userbecause that's a reserved word (for currently connected user)
Whichever database it is (presuming you fix possible syntax errors - such as that suspicious date and time datatype size), you should first create parent tables, and then child tables (those that reference their parents).
However, that sometimes won't work because of circular reference, where two tables reference each other. In that case, you should create foreign key constraints separately, using alter table command.
This is Oracle, just to show how you should really be doing it.
Create tables (without foreign key constraints) first:
SQL> CREATE TABLE libraryuser
2 (user_id VARCHAR(7),
3 user_password VARCHAR(12) NOT NULL,
4 user_lname VARCHAR(20) NOT NULL,
5 user_fname VARCHAR(20) NOT NULL,
6 user_email VARCHAR(100) NOT NULL,
7 user_ic VARCHAR(12) NOT NULL,
8 user_tel_no VARCHAR(11) NOT NULL,
9 user_age VARCHAR(2) NOT NULL,
10 user_gender VARCHAR(6) NOT NULL,
11 member_id VARCHAR(5),
12 CONSTRAINT user_user_id_pk PRIMARY KEY(user_id));
Table created.
SQL>
SQL> CREATE TABLE staff
2 (staff_id VARCHAR(7),
3 staff_password VARCHAR(12) NOT NULL,
4 staff_email VARCHAR(100) NOT NULL,
5 staff_lname VARCHAR(10) NOT NULL,
6 staff_fname VARCHAR(20) NOT NULL,
7 staff_ic VARCHAR(12) NOT NULL,
8 staff_tel_no VARCHAR(11) NOT NULL,
9 staff_gender VARCHAR(6) NOT NULL,
10 staff_position VARCHAR(20) NOT NULL,
11 staff_hire_date DATE NOT NULL,
12 staff_dismissal_date DATE NOT NULL,
13 library_id VARCHAR(5),
14 CONSTRAINT staff_staff_id_pk PRIMARY KEY(staff_id));
Table created.
SQL>
SQL> CREATE TABLE book
2 (book_id VARCHAR(5),
3 book_name VARCHAR(50) NOT NULL,
4 book_status VARCHAR(10) NOT NULL,
5 book_publish_date DATE NOT NULL,
6 book_genre VARCHAR(10) NOT NULL,
7 book_price FLOAT(10) NOT NULL,
8 book_type VARCHAR(20) NOT NULL,
9 transaction_id VARCHAR(5),
10 CONSTRAINT book_book_id_pk PRIMARY KEY(book_id));
Table created.
SQL>
SQL> CREATE TABLE booktransaction
2 (transaction_id VARCHAR(5),
3 transaction_date DATE NOT NULL,
4 book_status VARCHAR(10) NOT NULL,
5 return_date DATE NOT NULL,
6 --return_time TIME(5) NOT NULL,
7 return_time date not null,
8 staff_id VARCHAR(7),
9 book_id VARCHAR(5),
10 user_id VARCHAR(7),
11 CONSTRAINT booktransaction_transaction_id_pk PRIMARY KEY(transaction_id));
Table created.
SQL>
SQL> CREATE TABLE membership
2 (member_id VARCHAR(5),
3 member_status VARCHAR(20) NOT NULL,
4 member_start_date DATE NOT NULL,
5 member_experied_date_ DATE NOT NULL,
6 user_id VARCHAR(7),
7 CONSTRAINT membership_member_id_pk PRIMARY KEY(member_id));
Table created.
SQL>
SQL> CREATE TABLE library
2 (library_id VARCHAR(5),
3 library_name VARCHAR(35) NOT NULL,
4 library_branches VARCHAR(15) NOT NULL,
5 library_location VARCHAR(15) NOT NULL,
6 CONSTRAINT library_library_id PRIMARY KEY(library_id));
Table created.
SQL>
SQL> CREATE TABLE reservation
2 (reservation_id VARCHAR(5),
3 reservation_date DATE NOT NULL,
4 reservation_time DATE NOT NULL,
5 staff_id VARCHAR (7),
6 user_id VARCHAR(7),
7 book_id VARCHAR(5),
8 CONSTRAINT reservation_reservation_id_pk PRIMARY KEY(reservation_id));
Table created.
SQL>
Now create foreign key constraints (once again: user will raise an error):
SQL> alter table libraryuser add
2 CONSTRAINT user_member_id_fk FOREIGN KEY(member_id) REFERENCES membership(member_id);
Table altered.
SQL> alter table staff add
2 CONSTRAINT staff_library_id_fk FOREIGN KEY(library_id) REFERENCES library(library_id);
Table altered.
SQL> alter table book
2 CONSTRAINT book_transaction_id_fk FOREIGN KEY(transaction_id) REFERENCES booktransaction(transaction_id);
CONSTRAINT book_transaction_id_fk FOREIGN KEY(transaction_id) REFERENCES booktransaction(transaction_id)
*
ERROR at line 2:
ORA-01735: invalid ALTER TABLE option
SQL> alter table booktransaction add
2 CONSTRAINT booktransaction_user_id_fk FOREIGN KEY(user_id) REFERENCES libraryuser(user_id);
Table altered.
SQL> alter table booktransaction add
2 CONSTRAINT booktransaction_book_id_fk FOREIGN KEY(book_id) REFERENCES book(book_id);
Table altered.
SQL> alter table booktransaction add
2 CONSTRAINT booktransaction_staff_id_fk FOREIGN KEY(staff_id) REFERENCES staff(staff_id);
Table altered.
SQL> alter table membership add
2 CONSTRAINT membership_user_id_fk FOREIGN KEY(user_id) REFERENCES libraryuser(user_id);
Table altered.
SQL> alter table reservation add
2 CONSTRAINT reservation_staff_id_fk FOREIGN KEY(staff_id) REFERENCES staff(staff_id);
Table altered.
SQL> alter table reservation add
2 CONSTRAINT reservation_user_id_fk FOREIGN KEY(user_id) REFERENCES user(user_id);
CONSTRAINT reservation_user_id_fk FOREIGN KEY(user_id) REFERENCES user(user_id)
*
ERROR at line 2:
ORA-00903: invalid table name
SQL> alter table reservation add
2 CONSTRAINT reservation_book_id_fk FOREIGN KEY(book_id) REFERENCES book(book_id);
Table altered.
SQL>