Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Create Table - Problem

    What is the difference between following two commands

    CREATE TABLE "TEST" ("FIRSTNAME" VARCHAR2(30 byte) NOT NULL,
    "LASTNAME" VARCHAR2(30 byte) NOT NULL,
    "USERID" VARCHAR2(60 byte) NOT NULL
    CONSTRAINT "PK_USERID_1" PRIMARY KEY("USERID") )

    CREATE TABLE TEST ("FIRSTNAME" VARCHAR2(30 byte) NOT NULL,
    "LASTNAME" VARCHAR2(30 byte) NOT NULL,
    "USERID" VARCHAR2(60 byte) NOT NULL
    CONSTRAINT "PK_USERID_1" PRIMARY KEY("USERID") )

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    nothing since you do not need the double-quotes.

    create table test (
    first_name varchar2(30),
    last_name varchar2(30),
    userid varchar2(60));

    I prefer to create all constraints seperate from the table-creation script so as to have control over where you create the key.

    alter table test
    add constraint TEST_PK primary key
    (userid)
    using tablespace PK_01;
    Last edited by The_Duck; 09-04-03 at 13:12.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If you use double quotes then that's the way the table will be built
    and your quering the table will have to match the case...

    ie: Create table test ("Test1" varchar2(10));


    Select test1 from test;

    select TEST1 from test;

    only

    Select Test1 from test

    will give you results ...
    Other queries will give you invalid column names...

    Gregg

  4. #4
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Answer ?

    Hi

    I didnt a chance to try the command on the DB itself, but I would assume that double quotes are used so that spaces in the name doesnt mark the end of the table_name. But on second thoughts, you cant have spaces in a table_name.

    Hmm .. then I would assume that there should not be any difference as the table names and the column names are case insensitive.

    Thanx and Regards
    Aruneesh

  5. #5
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    When a table name or object name is double quoted they are very different - they become case sensitive.

    Contrary to earlier replies, do everything you can to avoid quotes. If you need them, trust me, you have done something wrong.

    For example... (and as already shown) ....

    Create table TeSt (cOlName ....)

    Select colname from TEST works
    Select COLNAME from test works
    Select colNamefrom TEst works

    Now... Create Table "TeSt" ("cOlName"...)

    Select COLNAME from TEST fails
    Select colNAME from test fails
    Select cOlName from TeSt works

    I think we'd all prefer it without quotes.

    Hth
    Bill

  6. #6
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Thanx

    Hi Bill

    Good you pointed out this. Unfortunately I dont have a DB handy.
    Good info, though rarely someone uses double quotes in Oracle.

    Thanx and Regards
    Aruneesh

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    I don't have one handy either - I just got home from the pub (sad git I know, getting home from the pub and looking on here!).

    The point being to make very clear when expressing an opinion as opposed to expressing fact (as you almost did). It saves a lot of people a lot of time.

    Regards
    Bill

  8. #8
    Join Date
    Sep 2003
    Posts
    2
    Thanks for your replies..

    As per my undertaning , if we create any object in Oracle Database , all object names stored in upper case in the dictionary.. If we want to store any objects in lower case ,we have to specify object name in quotes. But I don't know what is the funda behind this.. One of our DBA defined all objects in lower case. He is no more with us.. So I am trying to find good reason for that..

    If we create any table like
    create table "test" .......

    select * from user_tables

    This will display

    test is table

    When we try to drop this using following command, this won't work

    drop table test;

    we have use like

    drop table "test"..


    Ravi

  9. #9
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    Best is NOT to use " when creating objects.

    It can only make things difficult.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

Posting Permissions

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