1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Default constraint via ALTER TABLE

Discussion in 'General' started by nightrider43, Dec 16, 2010.

  1. nightrider43

    nightrider43 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    How can I create a default constraint for a column of a existing oracle database?

    In www dot w3schools dot com slash sql slash sql_default dot asp they suggest:

    SQL DEFAULT Constraint on ALTER TABLE

    To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

    SQL Server / Oracle / MS Access:

    ALTER TABLE Persons
    ALTER COLUMN City DROP DEFAULT

    But this syntax doesn't work under Toad 8.6.0.38

    The statement

    ALTER TABLE CADUSR ALTER COLUMN dssenhamd5 SET DEFAULT 'aaa'

    Gives an error:

    ORA-01735 Invalid ALTER TABLE option


    Thanks in advance

    Greetings from Sao Paulo - Brazil

    Ricardo
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Are you looking for this.......

    Code (SQL):
    SQL> CREATE TABLE PERSON (PERSON_ID NUMBER, NAME VARCHAR2(20), CITY VARCHAR2(20));

    TABLE created.

    SQL> ALTER TABLE PERSON MODIFY (CITY VARCHAR2(20) DEFAULT 'AAA');

    TABLE altered.

    SQL>
     
    Sadik likes this.
  3. nightrider43

    nightrider43 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    First, thank you for your answer.

    I discover that the syntax

    ALTER TABLE PERSON MODIFY (CITY DEFAULT 'AAA'); or
    ALTER TABLE PERSON MODIFY CITY DEFAULT 'AAA';

    do the same job too.

    One new question:

    How do I totally remove the DEFAULT from a table column, e.g., how do I remove the "DEFAULT 'AAA'" from the column CITY?

    Cheers

    Ricardo
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Follow this example:

    Code (SQL):
     
    SQL> ALTER TABLE emp MODIFY job DEFAULT 'aaa';
    TABLE altered.
    SQL>
    SQL> SELECT TABLE_NAME, column_name, data_default
      2  FROM user_tab_columns WHERE data_default IS NOT NULL;
    TABLE_NAME                     COLUMN_NAME                    DATA_DEFAU
    ------------------------------ ------------------------------ ----------
    EMP                            JOB                            'aaa'
    SQL>
    SQL> INSERT INTO emp (empno, ename, hiredate)
      2  VALUES (8881, 'SMING', sysdate -450);
    1 ROW created.
    SQL>
    SQL> SELECT empno, ename, hiredate, job FROM emp;
         EMPNO ENAME      HIREDATE  JOB
    ---------- ---------- --------- ---------
          7369 SMITH      17-DEC-80 CLERK
          7499 ALLEN      20-FEB-81 SALESMAN
          7521 WARD       22-FEB-81 SALESMAN
          7566 JONES      02-APR-81 MANAGER
          7654 MARTIN     28-SEP-81 SALESMAN
          7698 BLAKE      01-MAY-81 MANAGER
          7782 CLARK      09-JUN-81 MANAGER
          7788 SCOTT      09-DEC-82 ANALYST
          7839 KING       17-NOV-81 PRESIDENT
          7844 TURNER     08-SEP-81 SALESMAN
          7876 ADAMS      12-JAN-83 CLERK
         EMPNO ENAME      HIREDATE  JOB
    ---------- ---------- --------- ---------
          7900 JAMES      03-DEC-81 CLERK
          7902 FORD       03-DEC-81 ANALYST
          7934 MILLER     23-JAN-82 CLERK
          8881 SMING      28-SEP-09 aaa
    15 ROWS selected.
    SQL>
    SQL> ALTER TABLE emp MODIFY (job DEFAULT NULL);
    TABLE altered.
    SQL>
    SQL> SELECT TABLE_NAME, column_name, data_default
      2  FROM user_tab_columns WHERE data_default IS NOT NULL;
    TABLE_NAME                     COLUMN_NAME                    DATA_DEFAU
    ------------------------------ ------------------------------ ----------
    EMP                            JOB                            NULL
    SQL>
    SQL> DELETE FROM emp WHERE empno = 8881;
    1 ROW deleted.
    SQL>
    SQL> INSERT INTO emp (empno, ename, hiredate)
      2  VALUES (8881, 'SMING', sysdate -450);
    1 ROW created.
    SQL>
    SQL> SELECT empno, ename, hiredate, job FROM emp;
         EMPNO ENAME      HIREDATE  JOB
    ---------- ---------- --------- ---------
          7369 SMITH      17-DEC-80 CLERK
          7499 ALLEN      20-FEB-81 SALESMAN
          7521 WARD       22-FEB-81 SALESMAN
          7566 JONES      02-APR-81 MANAGER
          7654 MARTIN     28-SEP-81 SALESMAN
          7698 BLAKE      01-MAY-81 MANAGER
          7782 CLARK      09-JUN-81 MANAGER
          7788 SCOTT      09-DEC-82 ANALYST
          7839 KING       17-NOV-81 PRESIDENT
          7844 TURNER     08-SEP-81 SALESMAN
          7876 ADAMS      12-JAN-83 CLERK
         EMPNO ENAME      HIREDATE  JOB
    ---------- ---------- --------- ---------
          7900 JAMES      03-DEC-81 CLERK
          7902 FORD       03-DEC-81 ANALYST
          7934 MILLER     23-JAN-82 CLERK
          8881 SMING      28-SEP-09
    15 ROWS selected.
    SQL>

     
    Notice the default value in user_tab_Columns is showing NULL (it was actually a null value before all of this started) but the effect is still the same -- set the default to NULL and it goes away.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    As per the Oracle Documentation,

     
    nightrider43 likes this.