CODEWITHSUNDEEP
sqloracle-databaseoracle11gsqlplus
chelo
chelo

Reputation: 35

Modify table's column while containing data, from number to varchar2 directly

I have two tables:

CREATE TABLE vendedores (
    cedula      NUMBER(11) NOT NULL,
    nombre      VARCHAR2(30) NOT NULL,
    apellido    VARCHAR2(30) NOT NULL,
    telefono    VARCHAR2(15) NOT NULL,
    direccion   VARCHAR2(60) NOT NULL,
    CONSTRAINT pk_vendedores PRIMARY KEY ( cedula )
)
TABLESPACE basedtp;

CREATE TABLE ventas (
    id                NUMBER(8) NOT NULL,
    id_cliente        NUMBER(8) NOT NULL,
    fecha             DATE NOT NULL,
    numero_factura    NUMBER(8) NOT NULL,
    monto_total       NUMBER(9) NOT NULL,
    plazo             NUMBER(2) NOT NULL,
    tipo              VARCHAR2(2) NOT NULL,
    cedula_vendedor   NUMBER(11) NULL,
    id_sucursal       NUMBER(2) NULL,
    CONSTRAINT pk_ventas PRIMARY KEY ( id ),
    CONSTRAINT fk_ventas_clientes FOREIGN KEY ( id_cliente )
        REFERENCES clientes ( id )
            ON DELETE CASCADE,
    CONSTRAINT fk_ventas_vendedores FOREIGN KEY ( cedula_vendedor )
        REFERENCES vendedores ( cedula )
            ON DELETE CASCADE,
    CONSTRAINT fk_ventas_sucursal FOREIGN KEY ( id_sucursal )
        REFERENCES sucursal ( id_sucursal )
            ON DELETE CASCADE
)
TABLESPACE basedtp;

If a I have some data, for example:

INSERT INTO vendedores (
    cedula,
    nombre,
    apellido,
    telefono,
    direccion
) VALUES (
    '4993886',
    'nombre_vendedor_01',
    'apellido_vendedor_01',
    'telefono01',
    'direccion_vendedor_01'
);

INSERT INTO ventas (
    id,
    id_cliente,
    fecha,
    numero_factura,
    monto_total,
    plazo,
    tipo,
    cedula_vendedor,
    id_sucursal
) VALUES (
    '1',
    '1',
    SYSDATE,
    '1',
    '100000',
    '1',
    't1',
    '4993886',
    '1'
);

And after that I need to modify cedula and cedula_vendedor columns like this:

ALTER TABLE vendedores MODIFY
    cedula VARCHAR2(11);

ALTER TABLE ventas MODIFY
    cedula_vendedor VARCHAR2(11);

But I get the following error:

Error starting at line : 276 in command -
ALTER TABLE vendedores MODIFY
    cedula VARCHAR2(11)
Error report -
ORA-02267: column type incompatible with referenced column type
02267. 00000 -  "column type incompatible with referenced column type"
*Cause:    The datatype of the referencing column is incompatible with the

Error starting at line : 279 in command -
ALTER TABLE ventas MODIFY
    cedula_vendedor VARCHAR2(11)
Error report -
ORA-02267: column type incompatible with referenced column type
02267. 00000 -  "column type incompatible with referenced column type"
*Cause:    The datatype of the referencing column is incompatible with the

Well, I know I can create an auxiliary column with varchar2 datatype and pass on all the data, delete the old columns and create again the constraints but there is no more direct way to redefine the columns?

Upvotes: 1

Views: 1413

Answers (1)

kc2018
kc2018

Reputation: 1460

The error messages were caused by CONSTRAINT fk_ventas_vendedores FOREIGN KEY ( cedula_vendedor ) REFERENCES vendedores ( cedula ) in table ventas.

Add the following drop constraint statement before the ALTER statements, you will get a different error: "column to be modified must be empty to change datatype".

ALTER TABLE ventas DROP CONSTRAINT fk_ventas_vendedores;

Refer to: Oracle SQL to change column type from number to varchar2 while it contains data

Upvotes: 1

Related Questions