Oracle to SQL Server migration with SSMA: Converting foreign keys with different types of columns and referenced columns

15 Oct 2021

Andrey Khudyakov (DB architect), Roman Muzyka (writer)

In Oracle, you can create a foreign key for a table using columns with various data types. Unfortunately, SQL Server Migration Assistant (SSMA) fails to convert them to SQL Server correctly since it does not support such elements. As a result, when you try to convert the Oracle code including the foreign key with the columns of different data types, you will see the following message: «Error O2SS0231: Foreign keys with different types of columns and referenced columns cannot be converted». Read this blog post to find out how you could solve this problem.

Possible solutions

Let’s start with the following Oracle code 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);

When you convert T_FK_CONSTR_DIFF_DTTPS_1 and T_FK_CONSTR_DIFF_DTTPS_2 constraints, you will receive the following outcome:

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 can be seen from this example, SQL Server Migration Assistant comments the DDL for the foreign key.

Here is a step-by-step approach that will help you solve this error:

1) Alter the table’s columns so that there will be no 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]

If the two referenced columns are ID float and ID_2 and numeric (10,0), you should change ID_2 numeric(10,0) to float. If you change these columns differently, there is a threat of losing data.

2) Uncomment foreign key DDL script and, after that, 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

Final thoughts

Do you have any other questions on how to solve common problems pertaining to Oracle to SQL Server migration with SSMA? Check our Jumpstart for the SSMA offer and contact us to successfully migrate your database.

Also, make sure to check our table on the blog post series dedicated to common SSMA database migration issues.