Re: ADD columns

In comp.databases.oracle.misc, you wrote:

>In article <LvJh3.2817$8v6.218574_at_carnaval.risq.qc.ca>,
> "Mr.X" <pandore_at_itr.qc.ca> wrote:
>> how can I add a column to a table without loosing everithing ?
>> I want to add two columns : etat_exp and etat_exs both (boolean or
>char)
>> That is my table :
>>
>> SERIE_DOC NUMBER(2) NOT NULL,
>> NO_SOUS_SERIE NUMBER(3) NOT NULL,
>> NO_SS_SOUS_SERIE FLOAT NOT NULL,
>> LETTRE_DOC CHAR(1) NOT NULL,
>> TEMPS_ACTIF_P NUMBER(2),
>> TEMPS_SEMI_ACTIF_P NUMBER(2),
>> DATE_CREATION_DOC DATE,
>> DATE_FIN_ACTIF_P DATE,
>> DATE_FIN_SEMI_ACTIF_P DATE,
>> TEMPS_ACTIF_S NUMBER(2),
>> TEMPS_SEMI_ACTIF_S NUMBER(2),
>> DATE_FIN_ACTIF_S DATE,
>> DATE_FIN_SEMI_ACTIF_S DATE,
>> DESC_DESCRIPTION VARCHAR2(100),
>> DIVISION VARCHAR2(50),
>> NO_SEQUENCE_DOC NUMBER(3) NOT NULL,
>> DESTIN_STATUS_P CHAR(1),
>> DESTIN_STATUS_S CHAR(1),
>> DESCRIPTEURS VARCHAR2(100),
>>
>> Thanx ! Pat

>These are the rules for adding a column to a table:
>
>1. You may add a column at ANY time if NOT NULL isn't specified.
>
>2. You may add a NOT NULL column in three steps:
> 1. Add the column without NOT NULL specified
> 2. Fill every row in that column with data
> 3. Modify the column to be NOT NULL

  I add columns to one particular table about once every week and have always used the following (Using Oracle 7.3.4) :

ALTER TABLE foo
  ADD a_new_column CHAR(1) DEFAULT 'N' NOT NULL     CONSTRAINT foo_chk25 CHECK (a_new_column IN ('Y', 'N'));

This adds the row, makes it not null, fills all existing rows with 'N' and   adds a constraint, all in one step.

--
Reality is a cheap substitute for Prozac Received on Mon Jul 12 1999 - 00:50:24 CDT