Convert foreign key constraint from SQL Server to Oracle

I want to convert the following script(SQL SERVER) to Oracle:

ALTER TABLE [dbo].[TDistribucion]  WITH CHECK ADD  CONSTRAINT [FK_TDistribucion_TDocumentos] FOREIGN KEY([Id_Documento])
REFERENCES [dbo].[TDocumentos] ([Id])
GO
ALTER TABLE [dbo].[TDistribucion] CHECK CONSTRAINT [FK_TDistribucion_TDocumentos]
GO

I have tried to run the same script in Oracle:

ALTER TABLE TDISTRIBUCION  WITH CHECK ADD CONSTRAINT FK_TDistribucion_TDocumentos FOREIGN KEY(ID_DOCUMENTO)
REFERENCES TDOCUMENTOS (ID);
 
ALTER TABLE TDISTRIBUCION CHECK CONSTRAINT FK_TDistribucion_TDocumentos;

and I got this error:

Error starting at line : 3 in command - 
ALTER TABLE TDISTRIBUCION  WITH CHECK ADD CONSTRAINT FK_TDistribucion_TDocumentos FOREIGN KEY(ID_DOCUMENTO) 
REFERENCES TDOCUMENTOS (ID) 
Error report - 
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"
*Cause:    
*Action:

I believe that the “WITH CHECK ADD” is the problem!

Answer

See if this helps.

A master table – its primary key column will be referenced from the tdistribucion table.

SQL> create table tdocumentos (id number primary key);

Table created.

A detail – tdistribucion – table (with only one column, just to demonstrate the issue):

SQL> create table tdistribucion (id number);

Table created.

A foreign key constraint, using proper syntax. It will succeed if no records violate the constraint and fail if opposite. Oracle does it at once, you don’t have to run another “check”. As both tables are empty, it’ll succeed:

SQL> alter table tdistribucion add constraint
  2    fk_dis_doc foreign key (id)
  3    references tdocumentos (id);

Table altered.

However, if there were some rows that violate the constraint, you wouldn’t be able to create it:

SQL> alter table tdistribucion drop constraint fk_dis_doc;

Table altered.

SQL> insert all
  2    into tdocumentos   (id) values (1)     --> 1 can't be referenced ...
  3    into tdistribucion (id) values (2)     --> ... by 2
  4  select * from dual;

2 rows created.

SQL> alter table tdistribucion add constraint
  2    fk_dis_doc foreign key (id)
  3    references tdocumentos (id);
  fk_dis_doc foreign key (id)
  *
ERROR at line 2:
ORA-02298: cannot validate (SCOTT.FK_DIS_DOC) - parent keys not found

---

But, if you create it as enable novalidate, it’ll “discard” rows that violate the constraint, but will check any subsequent inserts:

SQL> alter table tdistribucion add constraint
  2    fk_dis_doc foreign key (id)
  3    references tdocumentos (id)
  4    enable novalidate;

Table altered.

Just to show that rows that violate the constraint really exist:

SQL> select * from tdocumentos;

        ID
----------
         1

SQL> select * from tdistribucion;

        ID
----------
         2

This is OK:

SQL> insert all
  2    into tdocumentos   (id) values (100)
  3    into tdistribucion (id) values (100)
  4  select * from dual;

2 rows created.

This isn’t OK:

SQL> insert all
  2    into tdocumentos   (id) values (300)
  3    into tdistribucion (id) values (400)
  4  select * from dual;
insert all
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DIS_DOC) violated - parent key not
found


SQL>