Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Increase column length - foreign key restrictions vs MS SQL

MateuszHJan 20 2016 — edited Jan 20 2016

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

This post has been answered by Jiri.Machotka-Oracle on Jan 20 2016
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 17 2016
Added on Jan 20 2016
3 comments
1,059 views