Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Question Unanswered: Trouble creating a table

    Hi,

    When I try to create a tabel in SQL+ (Release 9.0.1.3.0) using the sql given below, I receive the error message "ORA-00922: missing or invalid option".

    Here is the sql I am trying to use:

    create table hire(
    hire_id integer CONSTRAINT pk_hire primary key,
    hire_status varchar2(10),
    hire_collection_date date DEFAULT sysdate,
    hire_return_date date,
    hire_price number(5,2) CONSTRAINT ch_hire CHECK(hire_price >= 0.00),
    cust_id integer fk_hire references customer(cust_id),
    prod_id integer fk_hire_1 references product(prod_id)
    );


    I do not understand what the message is telling me and cannot for the life of me work out what is wrong with this particluar table.

    HELP!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think you need to change the last two lines to....

    cust_id integer constraint fk_hire references customer(cust_id),
    prod_id integer constraint fk_hire_1 references product(prod_id)

    HTH
    Bill

  3. #3
    Join Date
    Apr 2003
    Posts
    2

    Thanks!

    Thanks Bill. I will try that out.


    Originally posted by billm
    Hi,

    I think you need to change the last two lines to....

    cust_id integer constraint fk_hire references customer(cust_id),
    prod_id integer constraint fk_hire_1 references product(prod_id)

    HTH
    Bill

  4. #4
    Join Date
    Apr 2003
    Location
    Jagdishpur
    Posts
    146
    Originally posted by billm
    Hi,

    I think you need to change the last two lines to....

    cust_id integer constraint fk_hire references customer(cust_id),
    prod_id integer constraint fk_hire_1 references product(prod_id)

    HTH
    Bill

    Hi,
    You do not create references there with the create table script. comment thsi part and create the table hire. And then alter table to create thise two references. It will work.

    create table hire(
    hire_id integer CONSTRAINT pk_hire primary key,
    hire_status varchar2(10),
    hire_collection_date date DEFAULT sysdate,
    hire_return_date date,
    hire_price number(5,2) CONSTRAINT ch_hire CHECK(hire_price >= 0.00),
    cust_id integer /*fk_hire references customer(cust_id)*/,
    prod_id integer /*fk_hire_1 references product(prod_id)*/
    );

    alter table hire.......

    Hope it will solve ur purpose.

    - Kamesh Rastogi
    - KR

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    There are a number of ways of going about it, mostly down to style.

    Create table xxx (
    column y constraint a,
    column z constraint b
    )

    --- or ---

    Create table xxx (
    column y,
    column z,
    constraint a,
    constraint b
    )

    --- or ---

    Create table xx (
    column y,
    column z
    )

    alter table xx add constraint a ...
    alter table....

    --- or ---

    even a mixture of all three.

    It's mostly a matter of personal style, sometimes creation order of tables and constraints also influences it.

    Regards
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •