CODEWITHSUNDEEP
sqloracle-database
YeiBi
YeiBi

Reputation: 55

SQL : can not create a table

I do the following:

use oracle developer

create table loan 
(
        barcode                 number (20) not null ,
        borrowernumber          number (7) ,
        loancurrentdate         date ,
        loanreturndate          date ,
        loanreserveorder        number (20) ,
        paymentdate             date ,
        borrower_borrowernumber number not null
);

alter table loan add constraint loan_pk primary key (barcode) ;

Cannot create a table, then change it ...

create table loan 
(
    loanno                  INT not null,
    loandate                date not null,
    loanreturndate          date null,
    loanreserve             number (20) null,
    paymentdate             date null,
);

alter table loan add constraint loan_pk primary key (loanno) ;

I get the following error. Why am I getting this?

Error report
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"

It also shows "invalid identifier"

Error report:

SQL Error: ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"

Or should I delete paymentdate?

After delete the wrong comma, the table created.

Upvotes: 1

Views: 211

Answers (2)

Abdo Bmz
Abdo Bmz

Reputation: 641

This should work

drop table loan;
create table loan 
(
    loanno                  INT not null,
    loandate                date not null,
    loanreturndate          date null,
    loanreserve             number (20) null,
    paymentdate             date null
);

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143088

Both examples you posted are correct (apart from that superfluous comma at the end of the second CREATE TABLE statement). This are SQL*Plus examples, but - as SQL Developer emulates it well, I believe that they should work just fine there too.

The first one:

SQL> create table loan
  2  (
  3          barcode                 number (20) not null ,
  4          borrowernumber          number (7) ,
  5          loancurrentdate         date ,
  6          loanreturndate          date ,
  7          loanreserveorder        number (20) ,
  8          paymentdate             date ,
  9          borrower_borrowernumber number not null
 10  );

Table created.

SQL> alter table loan add constraint loan_pk primary key (barcode) ;

Table altered.

The second one:

SQL> drop table loan;

Table dropped.

SQL> create table loan
  2  (
  3      loanno                  INT not null,
  4      loandate                date not null,
  5      loanreturndate          date null,
  6      loanreserve             number (20) null,
  7      paymentdate             date null
  8  );

Table created.

SQL> alter table loan add constraint loan_pk primary key (loanno) ;

Table altered.

SQL>

What do you exactly mean by saying "Cannot create a table, then change it ..." after the first CREATE TABLE? Any error? If so, which one?

"ORA-00942: table or view does not exist" is probably raised by ALTER TABLE as you can't alter it if it doesn't exist (but the mystery is why you can't create it).

"ORA-00904: : invalid identifier" means that you used a column name which doesn't exist in the table, such as

SQL> alter table loan modify xxx number;
alter table loan modify xxx number
                        *
ERROR at line 1:
ORA-00904: "XXX": invalid identifier

If possible, do the same as I did - copy/paste SQL*Plus session so that we could see what you did and how Oracle responded.

Upvotes: 1

Related Questions