sqloracle-databaseora-00904

beginner sql missing keyword and invalid identifier

CREATE table Book
(
book_title varchar (100) not null ,
book_genre char(60) not null,
Date_of_publish date not null,
user_code char(7) not null ,
book_id char (7) primary key not null , 
constraint writer__id_fk foreign key (writer_id),
constraint publisher__id_fk foreign key (publisher_id)

);

I'm getting

[ORA-00905: missing keyword]

in publisher table

CREATE table publisher

(
publisher_id char (7) primary key not null,
publisher_name char(20) not null,
publisher_number char(10) not null,
publisher_email varchar2(60) not null,
publisher_address varchar2(60) not null,
);

I'm getting

[ORA-00904: : invalid identifier]

Solution

  • Welcome to the wonderful world of SQL! :-)

    General remark: Please tell us what kind of DBMS you're using. MySQL? SQL Server? Oracle? SQlite? Different systems use different kinds of syntaxes.

    First statement:

    The problem seems to be in the FOREIGN KEY-portion. Usually, you'll state something like:

    CONSTRAINT [constraint_name] FOREIGN KEY([column_in_this_table]) REFERENCES OTHER_TABLE([column_in_other_table])

    edit (added):

    The [column_in_this_table] has to exist in your DDL (CREATE TABLE-statement), like so: CREATE TABLE Book ( book_title ... etc., publisher_id INT, CONSTRAINT FK_publ_id FOREIGN KEY(publisher_id) REFERENCES publisher(publisher_id));

    Here, you'll have a 'original' column called 'publisher_id', in the 'publisher'-table. You refer to it from within the 'Book'-table, by first having a 'publisher_id' column in the 'Book'-table (which should have the same DDL as the original column by the way). Next, you'll add a FOREIGN KEY to the 'Book'-table, that is imposed on the Book(publisher_id) column. Note, that you could also name the column in your 'Book'-table differently -- like, say, 'Spongebob' or 'Patrick'. But for future use, you'd like naming conventions that tell what you might expect to find in a column. So you'd name columns for what they contain.

    Second statement:

    The problem is with the last portion of your statement, where there's a comma after the NOT NULL portion for column publisher_address.

    (Part of) your statement:

    publisher_address varchar2(60) not null, );

    Try replacing that with:

    publisher_address VARCHAR2(60) NOT NULL);

    edit (note to self): VARCHAR2 turns out to be a valid datatype in Oracle databases (see: Oracle documentation)