Home » SQL & PL/SQL » SQL & PL/SQL » Can Foriegn Key Constarint Deferred?
Can Foriegn Key Constarint Deferred? [message #397785] Mon, 13 April 2009 23:08 Go to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Hi,

I have a requirement where user is asking to defer the foriegen key violation constraint.

For say..
I have a table DEPT table with columns(deptno,dept_name) and EMP with columns(emp_id,ename,mgr,deptno<foreignkey reference of deptno from DEPT>)

When the user is trying to insert data into EMP through frontend application..obviously he is getting foriegnkey intergrity violation error..but he needs to insert data into EMP table wihtout throwing any error.I suggested by altering table EMP and dropping foriegn key constraint it can be done...but he said it needs to be done with out removing any constraints..

Please help me..is it possible to ignore the foreign key constraint violation error and insert data...without altering table.

Thanks in advance
Re: Can Foriegn Key Constarint Deferred? [message #397787 is a reply to message #397785] Mon, 13 April 2009 23:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want a foreign key but you want to be able to violate the foreign key. Isn't this the same thing as not having a foreign key?

Or do you want to defer the key checking until commit?

Regards
Michel
Re: Can Foriegn Key Constarint Deferred? [message #397788 is a reply to message #397785] Mon, 13 April 2009 23:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.

>I have a table DEPT table with columns(deptno,dept_name) and EMP with columns(emp_id,ename,mgr,deptno<foreignkey reference of deptno from DEPT>)

Do not allow user to specify a department which does not exist in DEPT table.
Re: Can Foriegn Key Constarint Deferred? [message #397789 is a reply to message #397785] Mon, 13 April 2009 23:19 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Actually...the tables USER is using are created long back..so we can not change it now it seems...so he wants to know whether is there any way to ignore the foreign key violation error while trying to insert data thru fron-end application and continue with the insert..

I have just taken examples as EMP and DEPT tables just to explain the situation...I have different tables with almost 30-40 columns..so I thought of explaining the problem by mentioning the EMP and DEPT..<please ignore them>..

Please let me know..
Re: Can Foriegn Key Constarint Deferred? [message #397790 is a reply to message #397785] Mon, 13 April 2009 23:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Please let me know..
Please comply with Posting Guidelines.

Foreign Keys exist for a reason.

What should be done with the data that fails the FK restriction?

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Can Foriegn Key Constarint Deferred? [message #397794 is a reply to message #397789] Mon, 13 April 2009 23:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't answer to my questions:
Michel Cadot wrote on Tue, 14 April 2009 06:11
You want a foreign key but you want to be able to violate the foreign key. Isn't this the same thing as not having a foreign key?

Or do you want to defer the key checking until commit?

Regards
Michel

Re: Can Foriegn Key Constarint Deferred? [message #397802 is a reply to message #397785] Tue, 14 April 2009 00:01 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Sorry for the late reply..

Yeah..it's the same as not having the foreign key...but the USER needs the foreign key constraint not to be changed..

How is it possible to defer the key checking until commit?
If possible how and what will the ins and outs/restrictions by using it...

Please let me know..
Re: Can Foriegn Key Constarint Deferred? [message #397805 is a reply to message #397802] Tue, 14 April 2009 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can disable it.
But it is plain stupid to keep a constraint knowing you violate it.

Regards
Michel
Re: Can Foriegn Key Constarint Deferred? [message #397829 is a reply to message #397785] Tue, 14 April 2009 02:01 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Will it be effect to the other tables which will referring the child table?

Also please let me know...do we need to have a record in the subsequent child tables referring to the first child table..

i.e..as in my previous example..let us say we have DEPT,EMP and one more table asw MGR....

so EMP.deptno is referring to DEPT.deptno...so for every record existing in EMP.deptno there should be a parent record in DEPT.deptno...but what if DEPT.dept_name is referring from MGR.dept_name and it is NULL...will it throw any error..
eg: deptno 10 is present in DEPT and EMP...but deptname for that deptno is present in MGR..

Please clarify..
Re: Can Foriegn Key Constarint Deferred? [message #397832 is a reply to message #397829] Tue, 14 April 2009 02:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Null values are allowed in columns with FK constraints - they simply say that this particular row does not point to a row in the parent table.

Read about Defferrable Constraints here

You can make a FK constraint deferred until commit time, as this example shows:
create table test_175 (pk_col  number
                      ,constraint test_175_pk primary key(pk_col));
                      
create table test_176 (fk_col  number
                      ,constraint test_176_fk foreign key (fk_col) references test_175(pk_col) deferrable initially immediate);

insert into test_176 values (2);
                      
alter table test_176 modify constraint test_176_fk initially deferred;

insert into test_176 values (2);

insert into test_175 values (2);

commit;

insert into test_176 values (3);

commit;
Re: Can Foriegn Key Constarint Deferred? [message #397834 is a reply to message #397785] Tue, 14 April 2009 02:31 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Thank you..but we can not defer a constraint that is not deferable..i.e.the foreign key constraint which was created in the creation table with out deferable can not be deferred..

So I need to drop the constraint and re-create it with deferrable option..but will it effect other database tables..

Thanks in advance..
Re: Can Foriegn Key Constarint Deferred? [message #397836 is a reply to message #397834] Tue, 14 April 2009 02:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Recreating a constraint as deferrable affects only that constraint.

You will need to ensure that no one puts invalid data into the table while you've got the constraint removed.

Re: Can Foriegn Key Constarint Deferred? [message #397841 is a reply to message #397832] Tue, 14 April 2009 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can make a FK constraint deferred until commit time

As far I understand what OP said, he wants the constraint to NOT be checked at any time.

Regards
Michel
Re: Can Foriegn Key Constarint Deferred? [message #397850 is a reply to message #397785] Tue, 14 April 2009 03:57 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Here is the exact tables...as mentioned earlier USER is getting an error when he is trying to insert record into table BUNT...

<<<BUNT table into which data is being inserted and throwing an integrity violation error from front-end>>

CREATE TABLE BUNT
(
BUNTNAVN VARCHAR2(12) NOT NULL,
SELSKAPSKODE VARCHAR2(4 ) NOT NULL,
AAR NUMBER(4) NOT NULL,
BILAGSART VARCHAR2(1) NOT NULL,
BILAGSTYPE VARCHAR2(3) NOT NULL,
BILAGSNR NUMBER(7) NOT NULL,
SEQ NUMBER(6) NOT NULL,
KONTONR VARCHAR2(18),
..other columns,
CONSTRAINT pk_bunt PRIMARY KEY
(BUNTNAVN, SELSKAPSKODE, AAR, BILAGSART, BILAGSTYPE, BILAGSNR, SEQ),
CONSTRAINT DET_BUNT
FOREIGN KEY (KONTONR)
REFERENCES DETALJKONTO (KONTONR))

--structure of DETALJKONTO
CREATE TABLE DETALJKONTO
(
KONTONR VARCHAR2(18) NOT NULL,
VAL_KODE VARCHAR2(3),
VALUTAFAST VARCHAR2(1),
FUNK_KODE NUMBER(3),
....other columns,

CONSTRAINT pk_detal PRIMARY KEY (KONTONR),
CONSTRAINT FUNK_DET FOREIGN KEY (FUNK_KODE)
REFERENCES FUNKSJONSKODE (FUNK_KODE),
CONSTRAINT VAL_DETA FOREIGN KEY (VAL_KODE)
REFERENCES VALUTA (VAL_KODE)))

<<Please ignore the syntax errors if any as I have modified some data here for better view>>

So when the USER is trying to insert data into BUNT he is getting integrity violation error and pointing at DET_BUNT constraint..

When I checked the particular record he was trying to insert that record exists in DETALJKONTO...

Please help in identifying the exact problem..



[Updated on: Tue, 14 April 2009 04:00]

Report message to a moderator

Re: Can Foriegn Key Constarint Deferred? [message #397852 is a reply to message #397850] Tue, 14 April 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
<<Please ignore the syntax errors if any as I have modified some data here for better view>>

You should use code tags for better view.

Quote:
When I checked the particular record he was trying to insert that record exists in DETALJKONTO...

You didn't check in the correct way: the one Oracle does.
Post what you did.

Regards
Michel
Re: Can Foriegn Key Constarint Deferred? [message #397854 is a reply to message #397852] Tue, 14 April 2009 04:05 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
ohh sorry....

No I mean to say is..he is trying to insert record with KONTONR = '1521' into BUNT table...so I checked that particular KONTONR(1521) exists in DETALJKONTO or not from backend database in TOAD...it exists in DETALJKONTO...so I couldn't undersatnd why it is showing inetgrity constraint violation error...

Re: Can Foriegn Key Constarint Deferred? [message #397856 is a reply to message #397854] Tue, 14 April 2009 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If Oracle says the Parent row wasn't there, then I believe it.

Two likely causes:
1) Was the parent row being created as part of the same transaction? If so, was the parent row posible added AFTER the child row?

2) Do you have any trailing spaces on the Varchar2 field?

You can create an Exceptions table, and modify the FK constraint to insert rows that fail validation into this table.
Details
Re: Can Foriegn Key Constarint Deferred? [message #397859 is a reply to message #397785] Tue, 14 April 2009 04:30 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
No..the parent row was not created as part of the same transaction...actually trying to insert the existing record of the KONTONR(1521 already existed in DETALJKONTO) into BUNT...also correct me if the parent row was added after the child row then will not oracle throw an error??..

No I don't have any trailing spaces in VARCHA2 field..
Re: Can Foriegn Key Constarint Deferred? [message #397862 is a reply to message #397859] Tue, 14 April 2009 04:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to COPY AND PASTE what you did if you want us to trust you.

Regards
Michel
Re: Can Foriegn Key Constarint Deferred? [message #397866 is a reply to message #397785] Tue, 14 April 2009 05:28 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Ok..here is what I have checked in TOAD..

When USER is trying to insert record into BUNT from front-end..he is getting the above said error..
But when I cheked the from back-end using...

SELECT KONTONR,VAL_KODE FROM BUNT WHERE kontonr = '1510'

I get more than 500 rows..here are the sample..

KONTONR VAL_KODE
--------------------
1510 KFO
1510 BBF
1510 GISS

When the USER is trying to insert into BUNT table KONTONR value 1521 and VAL_KODE as NOK ...it is giving the above said error..

So when I select using the below query on DETALJKONTO..

SELECT KONTONR,VAL_KODE,FUNK_KODE FROM DETALJKONTO WHERE kontonr = '1510'

I got one record as..

KONTONR VAL_KODE FUNK_KODE
------------------------------------
1510 NOK 150

Let me know what can be the problem..



Re: Can Foriegn Key Constarint Deferred? [message #397868 is a reply to message #397866] Tue, 14 April 2009 06:00 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
When the USER is trying to insert into BUNT table KONTONR value 1521 and VAL_KODE as NOK ...it is giving the above said error..



Since user is getting the error while trying to insert 1521 value in Bunt table, then why are you checking if 1510 is existing in DETALJKONTO table.

Your query should be

SELECT KONTONR,VAL_KODE,FUNK_KODE FROM DETALJKONTO WHERE kontonr = '1521' 


I guess that the above query doesnot yield any result and hence user is getting the error.

[Updated on: Tue, 14 April 2009 06:02]

Report message to a moderator

Re: Can Foriegn Key Constarint Deferred? [message #397869 is a reply to message #397785] Tue, 14 April 2009 06:13 Go to previous messageGo to next message
Hemanth123
Messages: 56
Registered: April 2009
Member
Ohh..sorry it's 1510 not 1521..typo mistake....
trying to insert 1510 value into BUNT..
and when I checked the same using..

SELECT KONTONR,VAL_KODE,FUNK_KODE FROM DETALJKONTO WHERE kontonr = '1510'....there is one record..

Please let me know what can be the problem..
Re: Can Foriegn Key Constarint Deferred? [message #397874 is a reply to message #397869] Tue, 14 April 2009 06:33 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
what happens if you insert into bunt table through toad/sqlplus? Does it go through?

[Updated on: Tue, 14 April 2009 06:34]

Report message to a moderator

Re: Can Foriegn Key Constarint Deferred? [message #397884 is a reply to message #397869] Tue, 14 April 2009 07:24 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hemanth123 wrote on Tue, 14 April 2009 13:13
Please let me know what can be the problem..

You are probably doing something different than you think, e.g. putting the value '1510' into different column. If you would
Michel Cadot wrote on Tue, 14 April 2009 11:37
COPY AND PASTE what you did
instead of vague describing, maybe somebody here could spot it. Also posting Oracle version could be helpful.

For me, Oracle 10.2.0.3 behaves correctly.
SQL commands for creating a (very simple) testcase:
SQL> CREATE TABLE DETALJKONTO
  2  (
  3  KONTONR VARCHAR2(18) NOT NULL,
  4  VAL_KODE VARCHAR2(3),
  5  CONSTRAINT pk_detal PRIMARY KEY (KONTONR)
  6  );

Table created.

SQL> CREATE TABLE BUNT
  2  (
  3  BUNTNAVN VARCHAR2(12) NOT NULL,
  4  KONTONR VARCHAR2(18),
  5  CONSTRAINT pk_bunt PRIMARY KEY
  6  (BUNTNAVN),
  7  CONSTRAINT DET_BUNT
  8  FOREIGN KEY (KONTONR)
  9  REFERENCES DETALJKONTO (KONTONR)
 10  );

Table created.

SQL> INSERT INTO DETALJKONTO( KONTONR, VAL_KODE )
  2  VALUES( '1510', 'ABC' );

1 row created.

SQL> commit;

Commit complete.

Insert commands to check foreign key violation:
SQL> select * from DETALJKONTO where KONTONR = '1510';

KONTONR            VAL
------------------ ---
1510               ABC

SQL> INSERT INTO BUNT( BUNTNAVN, KONTONR )
  2  VALUES( 'aaa', '1510' );

1 row created.

SQL> select * from DETALJKONTO where KONTONR = '1521';

no rows selected

SQL> INSERT INTO BUNT( BUNTNAVN, KONTONR )
  2  VALUES( 'bbb', '1521' );
INSERT INTO BUNT( BUNTNAVN, KONTONR )
*
ERROR at line 1:
ORA-02291: integrity constraint (FLYBOY.DET_BUNT) violated - parent key not found

SQL> 
Previous Topic: difference between explain plans
Next Topic: Count number of "Feb 29"s
Goto Forum:
  


Current Time: Sat Dec 10 04:58:46 CST 2016

Total time taken to generate the page: 0.09659 seconds