Unfortunately, the mailing system ate your formatting and it resulted in
a pile of text all together in one lump, however:
19 FOREIGN KEY(Username) REFERENCES Seller,
20 FOREIGN KEY(BookID) REFERENCES Book,
21 FOREIGN KEY(Authors) REFERENCES Book,
22 FOREIGN KEY(Edition) REFERENCES Book,
23 FOREIGN KEY(ISBN) REFERENCES Book,
24 FOREIGN KEY(CourseID) REFERENCES Course,
25 Check(Price>0)); FOREIGN KEY(Authors) REFERENCES Book,
Ok, first thing, be explicit. Change the above to add in the column(s)
that the foreign keys reference so that we (and future maintainers)
don’t have to keep guessing.
Next up, a foreign key can only reference the primary key or a unique
constraint on the parent table - does your book table have these?
When you don’t specify the referenced column(s) in the foreign key
clause, you automagically reference the primary key - so in the above,
all your foreign keys to book reference whatever the primary key is.
Line 21 is where the error (well, the first error) was flagged up. So
your foregin key on the authors column is attempting to reference the
primary key of the book table. I see dfrom your description of book that
you have no primary key defined. However, I assume it would be bookid -
so your are trying to reference a number(5) with a varchar2(50) - it
Next up, it looks very much like your normalisation is up that famous creek.
You have columns duplicated in the book and selling tables - title,
authors, edition, isbn.
You have authors (plural) as part of the book table. First normal form
required no repeating fields - authors is a separate table and each row
holds ONE author.
create table authors (
author_id not null number(5) primary key,
author_sex char(1) check (author_sex in (‘M’,‘F’,’?’)
Now, it is obvious if you ever read any James Patterson, that some
authors like to team up with others from time to time, so you need a
boo_authors table to allow a many to many relationship between books and
create table book_authors (
book_id number(5) not null,
author_id number(5) not null,
author_order number (2) not null
alter table book_authors
add constraint pk_book_authors
primary key (book_id, author_id);
create index ix_book_authors
on book_authors (author_id);
Now books can have an many authors as you like. Not only that, the order
they appear in catalogues etc is set by the Author_order column.
The columns in your selling table, that also appear in your book table
should be removed from selling.
You look like you need to have a customers table as well, referenced
from selling - to save you having to copy all the customers’ information
into the selling table.
And I hope you intend to encrypt at least the credit card number!
And one last thing, separate out your table structure from the indexes
and constraints please, it makes maintenance a lot easier (of the
scripts) and readable.