Home » Other » General » Mismatch in data length of columns involved in referential integrity constraint (Oracle Database)
Mismatch in data length of columns involved in referential integrity constraint [message #394454] Fri, 27 March 2009 04:13 Go to next message
shweta.s
Messages: 2
Registered: March 2009
Junior Member
It is found that the foreign key constraint is allowed to create even if the column in referred (parent) table is having different data length than the column in referring (child) table.

Example:
1. DDL for parent table:
CREATE TABLE TABLE_1
(
TAB_1_COL_1 NUMBER(3,6) PRIMARY KEY,
TAB_1_COL_2 VARCHAR2(40 BYTE) UNIQUE
);
2. DDL for child table:
CREATE TABLE TABLE_2
(
TAB_2_COL_1 NUMBER(3,10),
TAB_2_COL_2 VARCHAR2(30 BYTE),
CONSTRAINT FK_CONST_1 FOREIGN KEY (TAB_2_COL_1) REFERENCES TABLE_1(TAB_1_COL_1),
CONSTRAINT FK_CONST_2 FOREIGN KEY (TAB_2_COL_2) REFERENCES TABLE_1(TAB_1_COL_2)
);


Above tables are created with no error.

In case of the constraint 'FK_CONST_1', precision values in the number columns are different. For the constraint 'FK_CONST_2' data length of the varchar2 columns are different.

[Updated on: Fri, 27 March 2009 04:20]

Report message to a moderator

Re: Mismatch in data length of columns involved in referential integrity constraint [message #394469 is a reply to message #394454] Fri, 27 March 2009 05:09 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Do you have a question?
Re: Mismatch in data length of columns involved in referential integrity constraint [message #396731 is a reply to message #394469] Tue, 07 April 2009 19:31 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
the FK is a "statement of truth" about the relationship between the data in the 2 tables. The FK will still enforce the business rule as long as the PK and FK values are valid for the column definitions. Of course it probably never makes sense to have different PK/FK column definitions...
Re: Mismatch in data length of columns involved in referential integrity constraint [message #396784 is a reply to message #396731] Wed, 08 April 2009 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Fri, 27 March 2009 11:09
Do you have a question?

Re: Mismatch in data length of columns involved in referential integrity constraint [message #397058 is a reply to message #396784] Wed, 08 April 2009 14:02 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Is a question mandatory?
Re: Mismatch in data length of columns involved in referential integrity constraint [message #397072 is a reply to message #397058] Wed, 08 April 2009 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No but if OP expects something from us it should be better to tell it, isn't it?

Regards
Michel
Re: Mismatch in data length of columns involved in referential integrity constraint [message #398716 is a reply to message #397072] Fri, 17 April 2009 04:03 Go to previous message
shweta.s
Messages: 2
Registered: March 2009
Junior Member
It is not a question. Just encountered that, columns with different definitions can be involved in parent child relationship.
Previous Topic: ORA 03113
Next Topic: Junior Oracle DBA CV
Goto Forum:
  


Current Time: Wed Dec 07 02:59:18 CST 2016

Total time taken to generate the page: 0.23739 seconds