Toad World® Forums

ORA-02267: column type incompatible with referenced column type

Message from: pininfarina

SQL> CREATE TABLE Selling 2 (Username char(10) PRIMARY KEY, 3 BookID number(5),
4 Title char(30), 5 Authors char(50), 6 Edition number(2), 7 ISBN number(13), 8
CourseID number(5), 9 Price number(4,2), 10 BuyerUsername char(10), 11
BuyerCreditCard# number(16), 12 TransactionDate number(8), 13 UNIQUE(Username),
14 UNIQUE(BookID), 15 UNIQUE(ISBN), 16 UNIQUE(CourseID), 17
UNIQUE(BuyerUsername), 18 UNIQUE(BuyerCreditCard#), 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, * ERROR at line 21:
ORA-02267: column type incompatible with referenced column type SQL> describe
book; Name Null? Type

Morning Berkay

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
won’t work.

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_surname varchar2(150),
author_forename varchar2(150),
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
authors:

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.


Cheers,
Norm. [TeamT]

On 09/12/11 09:29, Norman Dunbar wrote:

create table book_authors (
book_id number(5) not null,
author_id number(5) not null,
author_order number (2) not null
);

You’ll also need:

alter table book_authors
add constraint fk_bookid
foreign key
references book(bookid);

alter table book_authors
add constraint fk_authorid
foreign key
references authors(author_id);

Normally, you would also index your foreign keys, but as we already have
an index on the PK which has book_id as the leading column we are fine.
Plus, we also created an index on author_id already (to allow looking up
all the books by a single author) - so we are done here.


Cheers,
Norm. [TeamT]