call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to SQL Server migration with SSMA: How to convert foreign keys with different types of columns and referenced columns

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

Posted by | On | In Database Migration, Microsoft SQL Server, Microsoft SQL Server Migration Assistant (SSMA), Oracle Database | Tags:
 

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.

SSMA for Oracle convert foreign keys

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.

foreign key cannot be converted troubleshooting

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

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.

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image