0
votes

all the details of the references tables are filled.My Create Statement is :-

Im Using Oracle 10g express Edition

create table Sales_order
(
S_order_no  varchar2(6) Primary key check( S_order_no like '0%'),
S_order_date    Date ,  
Client_no   Varchar2(25)    references as CLIENT_MASTER ,
Dely_add    Varchar2(6) ,
Salesman_no Varchar2(6) references as SALES_MASTER ,
Dely_type   Char(1)   check( Dely_type in ('p','f')) default 'f',
Billed_yn   Char(1),    
Dely_date   Date    check ( Dely_date <  s_order_date),
Order_status    Varchar2(10) check (Order_status in ('in process','fulfilled','back order','canceled'))
)

I got the oracle error "ORA-00903: invalid table name"

1

1 Answers

3
votes

A few errors.

  • as is invalid while referencing other tables
  • default should be before check constraint (line #8)
  • column-level check constraint can't reference other columns in the table (line 10), but table-level can (as @a_horse_with_no_name showed on a link in the comment and I replicated here). Alternatively, you could use a trigger.

SQL> create table client_master (client_no varchar2(25) primary key);

Table created.

SQL> create table sales_master (salesman_no varchar2(25) primary key);

Table created.

SQL> create table sales_order
  2  (
  3  s_order_no    varchar2(6) primary key check( s_order_no like '0%'),
  4  s_order_date  date ,
  5  client_no     varchar2(25)    references  client_master ,
  6  dely_add      varchar2(6) ,
  7  salesman_no   varchar2(6) references  sales_master ,
  8  dely_type     char(1) default 'f'  check( dely_type in ('p','f')) ,
  9  billed_yn     char(1),
 10  dely_date     date,
 11  order_status  varchar2(10) check (order_status in ('in process','fulfilled','back order','canceled')),
 12                check ( dely_date <  s_order_date)
 13  );

Table created.

SQL>