Skip to Main Content
  • Questions

  • Non Identifying relationship key issue

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, subhadra.

Asked: March 01, 2011 - 9:36 am UTC

Last updated: March 02, 2011 - 12:43 pm UTC

Version: 10.2

Viewed 1000+ times

You Asked

Hi Tom,
Am confused and need some serious help.I have two table which are in a non identifying relationship cause every record in the child table maynot be associated with a record in the parent table.

The issue here is that there are a few column from the composite pirmary key of the parent table which is part of the primary key of the child table.I have used not nullable columns of the foreign key of the child tables in the primary key of that table.Also i intend to use RI triggers along with the design to maintain data integrity in the system.During data model review i got a comment that foreign key of a table incase of a non identifying relationship should not be part of primary key of the table as it can harm the cardinality of the relationship and also cause issues with data integirty of the system.Is it true.Please advice

and Tom said...

... .Also i intend to use RI triggers along with the design to maintain data integrity in the system ....

did you include the LOCK TABLE command in your 'logic' - if not, I seriously doubt you know how to perform referential integrity (RI) in a trigger (it would be, in short, not smart to attempt to do RI in a trigger). Please abandon this approach, it is not anything you want to even consider doing.

Basically, you have the classic EMP/DEPT tables.

Your definition of a non-identifying relationship:

.I have two table which are in a non identifying relationship cause every record in the child table maynot be associated with a record in the parent table

is incorrect. If you have what you say - then you don't need any RI at all - you are done.

If you are building a non-identifying relationship - then you have a table like DEPT with a primary key of DEPTNO and a table EMP with a primary key of EMPNO (it doesn't rely on its parent to identify itself) and a separate foreign key DEPTNO back to DEPT.

And here the only way to enforce that relation would be via a FOREIGN KEY - no code, NO CODE - just a foreign key.


... The issue here is that there are a few column from the composite pirmary key of the parent table which is part of the primary key of the child table ...

if that is the case, then the relationship is actually an identifying one.



So, in short, you are mixing up terms here.
You are about to make a huge mistake by attempting triggers (stop it, don't go there)

and nothing fancy needs to be done, just use a foreign key.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Non Identifying relationship key issue

Yogesh Purabiya, March 01, 2011 - 10:31 pm UTC

... The issue here is that there are a few column from the composite pirmary key of the parent table which is part of the primary key of the child table ...

It is saying "few" of the columns - and "NOT" "ALL" of them
Tom Kyte
March 02, 2011 - 7:26 am UTC

doesn't matter, if the child primary key consists in part of the parent key, it is an identifying relationship. The child cannot exist without the parent.

Identifying relation:

create table document( doc_id primary key, .... );
create table document_version( doc_id references document, version, ..., primary key(doc_id,version));


Non-identifying

create table dept( deptno primary key, ... );
create table emp( empno primary key, deptno references dept, .... );



And note: not a single trigger ANYWHERE.

More information needed.

Chuck Jolley, March 02, 2011 - 12:21 pm UTC

subhadra,
I think it might help if you explicitly said whether ALL the columns from the parent's primary key are in the child table.
Regardless of whether they are part of the child's pk.

If they are, then just create the fk constraint like Tom said, and forget your worries. It doesn't matter whether some, none, or all of the columns in the foreign key are a part of the child table's primary key.

If some of the parent pk columns aren't in the child table and you can't add them to the child table because some business rule would be violated AND you need the reference to the partial parent primary key logically, then it's very likely that your ER design is incorrect.

Tom Kyte
March 02, 2011 - 12:43 pm UTC

A real world example would definitely help.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library