Tags: database conversion errors
Oracle allows you to create foreign key for table using columns with different data types. But SQL Server Migration Assistant (SSMA) for Oracle cannot convert them to SQL Server correctly because SQL Server doesn’t support the foreign keys that use columns with different data types. So, when you try to convert the original code that includes the foreign key with the columns of different data types, SSMA will generate the following error message: «Error O2SS0231: Foreign keys with different types of columns and referenced columns cannot be converted». In this blog post we will show how you can address this error and eliminate this conversion issue from your project.
Possible Remedies
Consider the following example:
1 2 3 4 5 6 7 8 | CREATE TABLE TEST_FUNCTIONS.T_FK_CONSTR_DIFF_DTTPS_1 (ID NUMBER NOT NULL, NAME VARCHAR(100)); ALTER TABLE TEST_FUNCTIONS.T_FK_CONSTR_DIFF_DTTPS_1 ADD PRIMARY KEY (ID); CREATE TABLE TEST_FUNCTIONS.T_FK_CONSTR_DIFF_DTTPS_2 (ID INT, NAME VARCHAR(100), ID_2 NUMBER(10,0) NOT NULL); ALTER TABLE TEST_FUNCTIONS.T_FK_CONSTR_DIFF_DTTPS_2 ADD FOREIGN KEY (ID_2) REFERENCES TEST_FUNCTIONS.T_FK_CONSTR_DIFF_DTTPS_1 (ID); |
The result of SSMA conversion of T_FK_CONSTR_DIFF_DTTPS_1 and T_FK_CONSTR_DIFF_DTTPS_2 functions will look like:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | CREATE TABLE [dbo].[T_FK_CONSTR_DIFF_DTTPS_1] ( [ID] FLOAT(53) NOT NULL, [NAME] VARCHAR(100) NULL) GO ALTER TABLE [dbo].[T_FK_CONSTR_DIFF_DTTPS_1] ADD CONSTRAINT [SYS_C00221003] PRIMARY KEY CLUSTERED ([ID] ASC) GO CREATE TABLE [dbo].[T_FK_CONSTR_DIFF_DTTPS_2] ( [ID] NUMERIC(38, 0) NULL, [NAME] VARCHAR(100) NULL, [ID_2] NUMERIC(10, 0) NOT NULL) /* * SSMA error messages: * O2SS0231: Foreign keys with different types of columns * and referenced columns cannot be converted:SYS_C00221005. ALTER TABLE [dbo].[T_FK_CONSTR_DIFF_DTTPS_2] ADD CONSTRAINT [SYS_C00221005] FOREIGN KEY ([ID_2]) REFERENCES [TEST_FUNCTIONS].[dbo].[T_FK_CONSTR_DIFF_DTTPS_1] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION */ |
As you can see, SSMA comments the DDL for the foreign key.
To solve this error, you should perform the following actions:
1) Alter table’s columns in such a way that avoid data truncation during data migration from Oracle to SQL Server.
[sql]ALTER TABLE dbo.T_FK_CONSTR_DIFF_DTTPS_2 ALTER COLUMN ID_2 float(53) NOT NULL [/sql]Namely, if the two referenced columns are ID float and ID_2 numeric(10,0) correspondingly you ought to change ID_2 numeric(10,0) to float. But not vice versa. Otherwise you may lose the data.
2) Uncomment foreign key DDL script and execute it.
1 2 3 4 5 6 7 8 | ALTER TABLE [dbo].[T_FK_CONSTR_DIFF_DTTPS_2] ADD CONSTRAINT [SYS_C00221005] FOREIGN KEY ([ID_2]) REFERENCES [TEST_FUNCTIONS].[dbo].[T_FK_CONSTR_DIFF_DTTPS_1] ([ID]) ON DELETE NO ACTION ON UPDATE NO ACTION |
Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!
Related posts
- How to convert cursor or cursor variable as a function or procedure call parameter
- How to convert unsupported table expressions
- How to convert Unparsed SQL — Pivot Operator
- How to convert materialized view with float type
- How to convert interval expressions
- How to convert interval literals
- How to convert database links
- How to convert aggregate functions referring to remote table
- How to convert unsupported SQL clause
- How to convert virtual columns
Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.
Source of technical information: Oksana Eremenko, Technical Lead at DB Best.
