Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Adding a constraint...

    Hi guys

    I need to add a constraint to a table. This table already has a couple of constraints specified by which were specified using:

    Code:
    ALTER TABLE USLM 
    ADD ( CONSTRAINT USLM_PK 
    PRIMARY KEY (DATETIME, MSC)  
    USING INDEX LOCAL);
    I'd like to add another column called LOC as a PK to the constraints.

    Can I simply say:

    And now I need to add another column as a PK for the table I'm loading data in.

    Would like to confirm the syntax please:

    Code:
    ALTER TABLE USLM
    ADD ( CONSTRAINT USLM_PK
    PRIMARY KEY (LOC) 
    USING INDEX LOCAL);
    or must the existing constraints be disabled first?
    Last edited by shajju; 11-04-09 at 04:04.

  2. #2
    Join Date
    Aug 2008
    Posts
    464
    OK guys....It just occurred to me that it has to be a constraint issue. Basically my files contain multiple columns with each file containing columns for datetime,msc and other columns.
    So I understand when the loader starts loading the file (from the ext table to the db table) and then after processing the first row and before loading the second one, it checks the constraints to see if a row with these values is already in the db or not and if it finds one, it discards that second row and moves on to the next. So inorder to load all the rows, the constraints must be chosen so that the loader can identify each row.

    Please confirm my understanding?

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    I'd like to add another column called LOC as a PK to the constraints.
    You are out of luck - a table can have only one primary key. You might create an unique constraint on that column though.
    Quote Originally Posted by shajju View Post
    Would like to confirm the syntax please
    Are you really unable to find correct syntax in the documentation? I would remove the outer parentheses; anyway you have logical problem, as I said above.
    Quote Originally Posted by shajju View Post
    OK guys....It just occurred to me that it has to be a constraint issue. Basically my files contain multiple columns with each file containing columns for datetime,msc and other columns.
    So I understand when the loader starts loading the file (from the ext table to the db table) and then after processing the first row and before loading the second one, it checks the constraints to see if a row with these values is already in the db or not and if it finds one, it discards that second row and moves on to the next. So inorder to load all the rows, the constraints must be chosen so that the loader can identify each row.

    Please confirm my understanding?
    As I am not aware about any generic "loader from the ext table to the db table", and as you did not post its detail specification, I cannot confirm anything. Just a guess - it probably uses "WHEN OTHERS THEN NULL;" exception handling bug.

  4. #4
    Join Date
    Aug 2008
    Posts
    464
    Thanks. Though my table has 2 columns defined a PK columns...How is this considered as one PK?

    So if I want add one more column to the constraint columns already defined as PKs, I can only drop the table and recreate it?

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju View Post
    Thanks. Though my table has 2 columns defined a PK columns...How is this considered as one PK?
    Hm, what about its definition? How many primary keys do you see here?
    Quote Originally Posted by shajju View Post
    Code:
    ALTER TABLE USLM 
    ADD CONSTRAINT USLM_PK 
    PRIMARY KEY (DATETIME, MSC)  
    USING INDEX LOCAL;
    I see one named USLM_PK.
    According your questions: please open the Oracle documentation. It is available e.g. online on http://tahiti.oracle.com/. Concepts book contains very useful description of basic database features and the way Oracle implements them. You really should know them.
    Quote Originally Posted by shajju View Post
    So if I want add one more column to the constraint columns already defined as PKs, I can only drop the table and recreate it?
    Dropping the constraint (and creating it again) will suffice.
    But, are you really aware, what you want to do? In other words, do you know the reason, for which LOC column shoul be added to primary key?

  6. #6
    Join Date
    Aug 2008
    Posts
    464
    Hi

    Please advise if the following is not possible and why?

    First

    ALTER TABLE USLM DROP CONSTRAINT USLM_PK;

    Then to add the desired primary key constraint.

    ALTER TABLE USLM ADD (CONSTRAINT USLM_PK PRIMARY KEY(DATETIME, MSC,LOCATION_AREA_CODE) USING INDEX LOCAL TABLESPACE SIE_USLM_I;

    Thanks.

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Yes, I know why I want to add another column to the PK constraint. Because currently the rows in the file being loaded into the table USLM are not being identified as unique rows. To make them unique to the loader, the loader also has to look at the LOC column.

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Thanks for the advice.

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    It is possible. Ok, have a look at Concepts book to learn about basic database features and terminology.

Posting Permissions

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