开发者

Errors in my schema creation script

So I have a simple SQL script which creates a database schema of a simple library online catalog:

DROP TABLE book_copies;
/
DROP TABLE books_authors_xref;
/
DROP TABLE authors;
/
DROP TABLE books;
/

CREATE TABLE books (
    isbn VARCHAR2(13) NOT NULL PRIMARY KEY,
    title VARCHAR2(200),
    summary VARCHAR2(2000),
    date_published DATE,
    page_count NUMBER
);
/

CREATE TABLE authors (
    name VARCHAR2(200) NOT NULL PRIMARY KEY
);
/

CREATE TABLE books_authors_xref (
    author_name VARCHAR2(200),
    book_isbn VARCHAR2(13),
    CONSTRAINT pk_books_authors_xref PRIMARY KEY (author_name, book_isbn),
    CONSTRAINT fk_books_authors_xref1 FOREIGN KEY (author_name) REFERENCES authors (name),
    CONSTRAINT fk_books_authors_xref2 FOREIGN KEY (book_isbn) REFERENCES books (isbn)
);
/

CREATE TABLE book_copies (
    barcode_id VARCHAR2(100) NOT NULL PRIMARY KEY,
    book_isbn VARCHAR2(13),
    CONSTRAINT fk_book_copies FOREIGN KEY (book_isbn) REFERENCES books (isbn)
);
/

Whenever I run it through SQL*Plus, I get many er开发者_JS百科rors during its execution even though it looks like all SQL orders execute properly. This is the output I get:

Errors in my schema creation script

What does that mean? Am I doing something wrong?


The / in SQL*Plus executes the "command in the buffer". A statement terminated with a semicolon is executed and put into the buffer.

So the CREATE TABLE books .... is actually run twice. The first time because of the semicolon ; (which puts the statement into the buffer) and the second time when the parser hits the /.

That's why you get the "name is already used" error.

So you need to use either a semicolon or a slash, but not both.

Edit
You can see what's going on, when manually running a statement using both, in the following log I copied & pasted the first statement from your script to a SQL*Plus console:

SQL> DROP TABLE book_copies;

Table dropped.

SQL> /
DROP TABLE book_copies
           *
ERROR at line 1:
ORA-00942: table or view does not exist

You can see clearly how the DROP TABLE is execute because of the semicolon, and how the / executes it again.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜