Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    205

    Unanswered: An ALTER table query ? - Interesting !!

    Hi all,

    1) How to add a unique constraint to the existing column in a table ? Please give some examples. I believe this query will be handled with ADD clause, is it possible with MODIFY clause ?

    2) Is there any constraint in Oracle with DEFAULT clause ? I have heard of like this

    ALTER TABLE test2
    MODIFY
    (
    col1 DEFAULT 100
    )

    But I need to know , whether the same can be handled with any DEFAULT constraint as exists in SQL Server

    SQL Server eg : -

    ALTER TABLE test2 WITH NOCHECK ADD
    CONSTRAINT ct1 DEFAULT (100) FOR col1

    3) Like in the above query, Is there anything like 'WITH NOCHECK' in Oracle explicitly for ALTER TABLE clause. There is CHECK / NOCHECK for constraints, but here the scenario is different , if I am right.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    1. ALTER TABLE table_name ADD CONSTRIANT cons_name UNIQUE (column_list) USING INDEX TABLESPACE index_tablespace;

    alter table emp add constraint uk_emp unique (empno, ename) using index tablespace idx_tblspc;

    MODIFY won't work. How to drop it?
    ALTER TABLE emp DROP CONSTRAINT uk_emp;

    2. In Oracle it would be (just like you said):
    ALTER TABLE emp MODIFY ename DEFAULT 'SamCute';

    ALTER TABLE emp ADD CONSTRAINT ch_ename CHECK (ename = 'SamCute');

    Or, if you'd like to do it in only one step:
    ALTER TABLE emp MODIFY ename DEFAULT 'SamCute' ADD CONSTRAINT ch_ename CHECK (ename = 'SamCute');

    (BTW, never tried to do it in one statement before. Thanks for posting this question; now I know it is possible too )

    3. NOCHECK? If you don't want to check it, omit CHECK. Didn't find NOCHECK option in my manual.

  3. #3
    Join Date
    Mar 2004
    Posts
    205

    Hi Littlefoot and all, Once again - Interesting !!

    The following is an SQL Server query :-

    ALTER TABLE test2 WITH NOCHECK ADD
    CONSTRAINT ct1 DEFAULT (100) FOR col1,
    CONSTRAINT ct2 DEFAULT (50) FOR col2,
    CONSTRAINT ct3 UNIQUE NONCLUSTERED (ct3) ON [PRIMARY]

    Note : Here on [primary] is that the index will be created on the filegroup 'primary'

    Please , can you give the Oracle equivalent for this. I have some problem in handling this default and unique constraints in a single query in Oracle.

    I did like this :-

    alter table test2 modify(col1 default 100, col2 default 50) - But here I don't how to add the third unique constraint here in this query. Is it possible to add unique constraint in modify clause. Please give the Oracle equivalent query for the above.

    Thanks,
    SAM

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, let me try (it works on Oracle):

    ALTER TABLE emp
    MODIFY (empno DEFAULT 10, ename DEFAULT 'SamCute')
    ADD CONSTRAINT uk_job UNIQUE (job) USING INDEX TABLESPACE idx_tblspc;

    Is that what you need?

  5. #5
    Join Date
    Mar 2004
    Posts
    205
    Hi Little foot,

    Exactly , the same I want. Will it work in Oracle ? I will try.

    Thanks a lot.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure it'll work! I first tried it on my DB before posting it here

  7. #7
    Join Date
    Mar 2004
    Posts
    205
    Thanks a lot, I will try

  8. #8
    Join Date
    Mar 2004
    Posts
    205
    Hi Little foot,

    BTW, why you have added tablespace related stuffs ? Is it the equivalent for NONCLUSTERED index (or) for what ? Please explain.

Posting Permissions

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