Hello,
I must increase column length (nvarchar) in some tables. Between these tables are foreign keys.
When i try do this on MS SQL databases using command like 'ALTER TABLE TABLE_NAME ALTER COLUMN COLUMNA NVARCHAR(50) NULL' then i get error like below:
Column 'dbo.TABLEA.NAME' is not the same length or scale as referencing column or The object 'FKNAME' is dependent on column 'name'.
So resume, on MS SQL i must drop all constraints, alter table and recreate constraints.
When i do this same thing on oracle database(oracle database has same schema like MS SQL database, same fk, same tables, columns etc) using command like "ALTER TABLE TABLE_NAME MODIFY COLUMN NVARCHAR2(40); " then length is change without any erros and i wounder does it correct situation on oracle? - all constraints are enabled. Does Oracle verify columns data length in foreign keys?
How i understand error (ORA-02267: column type incompatible with referenced column type) is show only if data type name is not equal between tables not data type length(?)
To reproduce :
1. create tables
CREATE TABLE "TABLEA"
( "NAME" NVARCHAR2(20) NOT NULL ENABLE,
"TEXTID" NUMBER,
CONSTRAINT "PK_TABLEA" PRIMARY KEY ("NAME")
);
CREATE TABLE "TABLEB"
( "ID" NUMBER NOT NULL ENABLE,
"NAME" NVARCHAR2(10),
"WWW" NUMBER,
CONSTRAINT "TABLEB" PRIMARY KEY ("ID")
);
2. create fk
ALTER TABLE TABLEB ADD CONSTRAINT FK_TABLEB FOREIGN KEY ("NAME") REFERENCES "TABLEA" ("NAME") ;
On MS SQL you get error about not the same length of column;)
Thanks,
Best regards,
Mateusz