Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    May 2004
    Posts
    11

    Unhappy Unanswered: alittle oracle help please??

    Hi, Im new to sql and currently working on designing a database but I've run into a few problems

    I have a table called Designer where its properties are -

    Designer_id - Primary key, 5 characters, uppercase

    D_Name - Name of the designer. Character data up to characters
    in length. Must not be null. Must be in upper case.

    Supervisor_id- The id of the supervisor of the designer. May be null
    but if present must match a value in Designer_id. A
    designer cannot supervise onself.

    From this I have written the code as follows but I am stuck on the Supervisor_id part -

    CREATE TABLE Designer
    (designer_id VARCHAR2(5)
    CONSTRAINT pk_Designer PRIMARY KEY,
    CONSTRAINT uc_designer_id CHECK (designer_id = UPPER(designer_id)),
    d_name VARCHAR2(20) NOT NULL
    CONSTRAINT uc_d_name CHECK (d_name = UPPER(d_name)),
    Supervisor_id VARCHAR2(5)
    <this parts missing>
    );

    I heard I had to use a Match <> operator or something but I cannot find anything about this anywhere??

    Thats the first part to my question, I also have another query I am stuck on.

    I have two other tables, first one is Application which has these properties -

    ApplicationNo-Primary key of the relation. Up to five chars in length

    AppName -Char field of up to 30 chars in length. Should be
    stored in upper case. Must not be null and must not be
    duplicated.

    Appsize -Size of the application in Mb. Must be greater than zero.

    CREATE TABLE Application
    (applicationno VARCHAR2(5), appname VARCHAR2(30),
    CONSTRAINT pk_Application PRIMARY KEY (applicationno,appname),
    CONSTRAINT uc_appname CHECK (appname = UPPER(appname)),
    appsize NUMBER(3)
    CONSTRAINT ap_appsize CHECK (appsize > 0)
    );

    and this is the second table for Packagedapplication with properties-

    Package_id -The id for the package this application is a part of.

    Version -The version of the package this application is part of.

    ApplicationNo-The id of one of the applications included in this
    package. Must match a value in the Application table.

    and this is the code I have used-

    CREATE TABLE Packagedapplication
    (package_id VARCHAR2(5)
    CONSTRAINT pk_Packedapplication PRIMARY KEY,
    version NUMBER(3,2)
    applicationno VARCHAR2(5)
    CONSTRAINT fk_applicationno references Application(applicationno)
    );

    I can create the Application table but when I try to create the Packagedapplication I get the following error -

    (package_id VARCHAR2(5)
    *
    ERROR at line 2:
    ORA-00922: missing or invalid option

    Or if I don’t use a primary key for package_id, ie. I use this code -

    CREATE TABLE Packagedapplication
    (package_id VARCHAR2(5),
    version NUMBER(3,2),
    applicationno VARCHAR2(5)
    CONSTRAINT fk_applicationno references Application(applicationno)
    );

    I get this error –

    CONSTRAINT fk_applicationno references Application(applicationno)
    *
    ERROR at line 5:
    ORA-02270: no matching unique or primary key for this column-list

    I know this is a very long post and I would appreciate any help/comments anyone has to offer. Thanks everyone for reading through it

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    In response to your Designer table question:

    Here is a full script for your table (Copy and execute):
    Code:
    -- Create table
    create table DESIGNER
    (
      DESIGNER_ID   VARCHAR2(5) not null,
      D_NAME        VARCHAR2(20) not null,
      SUPERVISOR_ID VARCHAR2(5)
    );
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table DESIGNER
      add constraint PK_DESIGNER primary key (DESIGNER_ID);
    alter table DESIGNER
      add constraint FK_SUPERVISOR foreign key (SUPERVISOR_ID)
      references DESIGNER (DESIGNER_ID);
    -- Create/Recreate check constraints 
    alter table DESIGNER
      add constraint UC_DESIGNER_ID
      check (designer_id = upper(designer_id));
    alter table DESIGNER
      add constraint UC_D_NAME
      check (d_name = upper(d_name));
    alter table DESIGNER
      add constraint UC_SUPERVISOR_ID
      check (designer_id != supervisor_id);

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    In response to your application tables:

    Here is a full script for your tables (Copy and execute):
    Code:
    -- Create table
    create table APPLICATION
    (
      APPLICATIONNO VARCHAR2(5) not null,
      APPNAME       VARCHAR2(30) not null,
      APPSIZE       NUMBER(3)
    );
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table APPLICATION
      add constraint PK_APPLICATION primary key (APPLICATIONNO,APPNAME);
    -- Create/Recreate check constraints 
    alter table APPLICATION
      add constraint UC_APPNAME
      check (appname = upper(appname));
    alter table APPLICATION
      add constraint UC_APPSIZE
      check (appsize > 0);
    
    -- Create table
    create table PACKAGEDAPPLICATION
    (
      PACKAGE_ID    VARCHAR2(5) not null,
      VERSION       NUMBER(3,2),
      APPLICATIONNO VARCHAR2(5),
      APPNAME       VARCHAR2(30)
    );
    -- Create/Recreate primary, unique and foreign key constraints 
    alter table PACKAGEDAPPLICATION
      add constraint PK_PACKAGE primary key (PACKAGE_ID);
    alter table PACKAGEDAPPLICATION
      add constraint FK_APPLICATION foreign key (APPLICATIONNO,APPNAME)
      references APPLICATION (APPLICATIONNO,APPNAME);
    Your first problem was that you did not specify a column name for the primary key in your packagedapplication table. Your second problem is that you are trying to create a foreign key that references the application table, but you are only specifying half of the key. You will see that the foreign key I created uses both the applicationno and appname fields where you were only using the applicationno.

  4. #4
    Join Date
    May 2004
    Posts
    11
    ahh right I understand where I went wrong but didnt know how to code it

    You way was fairly straight forward, I will give it a try soon

    Thanks for all your help!! Much appreciated

  5. #5
    Join Date
    May 2004
    Posts
    11
    Yup that worked great!

    I have a little prob tho

    I have this code for an Application table-

    CREATE TABLE Application
    (applicationno NUMBER(5) not null,
    appname VARCHAR2(30) not null,
    appsize NUMBER(3)
    );

    rem -- Create/Recreate primary, unique and foreign key constraints

    ALTER TABLE Application
    add CONSTRAINT pk_Application PRIMARY KEY (applicationno);

    rem -- Create/Recreate check constraints

    ALTER TABLE Application
    add CONSTRAINT uc_appname
    check (appname = UPPER(appname));
    ALTER TABLE Application
    add CONSTRAINT ch_appname
    check (appname != appname);
    ALTER TABLE Application
    add CONSTRAINT ch_appsize
    check (appsize > 0);

    When I insert this data I get this error msg:

    INSERT INTO Application
    VALUES('11128', 'EXCEL', 2);

    INSERT INTO Application
    *

    ERROR at line 1:
    ORA-02290: check constraint (OPS$C0271866.CH_APPNAME) violated

    Pleaes dont tell me that I have to change the code in the table

    thanks for ur help so far

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    I don't have much experience in this area, yet, but the following looks incorrect:

    add CONSTRAINT ch_appname
    check (appname != appname);

    What are you trying to do here? appname!=appname seems like it would never be true.

    -cf

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    ALTER TABLE Application
    add CONSTRAINT ch_appname
    check (appname != appname);
    It doesn't make any sense ... such a constraint will always fail. Rewrite it and your INSERT statement should work.

    P.S. Chuck, you could have told me you were writing the answer at the same time as I was
    Last edited by Littlefoot; 05-04-04 at 17:29.

  8. #8
    Join Date
    May 2004
    Posts
    11
    ok well what i wanted to write was that appname cannot equal appname as I dont want it to duplicated and I dont really want to insert a primary key.

    Is there any way of doing this? Thanks for your help guys

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There's no need for a PRIMARY KEY on appname - use UNIQUE constraint instead.

  10. #10
    Join Date
    May 2004
    Posts
    11
    Can you help me to write this Unique code please? I have never used it before.

    I think it goes something like

    ALTER TABLE Application
    add CONSTRAINT CREATE UNIQUE un_appname
    ON Application(appname);

    Is this right??

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    alter table application add constraint un_appname unique (appname);

  12. #12
    Join Date
    May 2004
    Posts
    11
    THANK YOU SOOO MUCH!

    I will give it a try

  13. #13
    Join Date
    May 2004
    Posts
    11
    ok I have 2 tables-

    PackagedApplication
    - package_id
    - version
    - applicationno

    and Application
    - applicationno
    - appname
    - appsize

    I want to do a query where I list all the applications that have never been part of a package. I tried this:

    SELECT *
    FROM PackagedApplication, Application
    Where Application.applicationno != PackagedApplication.applicationno;

    but its gives me 25 results and there should only be one, I must have done something wrong

    Can anyone see whats wrong with it?? Thanks

  14. #14
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    SELECT A.*
    FROM Application A
    Where Not Exist
    (SELECT 'N'
    FROM PackagedApplication PA
    WHERE A.applicationno = PA.applicationno)

  15. #15
    Join Date
    May 2004
    Posts
    11
    doesnt work, says this when i try it:

    (SELECT 'N'
    *

    ERROR at line 4:
    ORA-00936: missing expression

Posting Permissions

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