Q

Delete properties of Master/Detail relationship

What do you understand by Master detailed relationship? Discuss its delete properties.


I think you mean a Master/Detail relationship, which is more commonly called a parent/child relationship these days.

The Master row is the logical owner of the detail row. A master row can never be deleted while a child row still exists that refers to the master row. For instance:

CREATE TABLE t_media_formats ( 
  media_format_id      INT               IDENTITY 
  CONSTRAINT XPKt_media_format 
     PRIMARY KEY (media_format_id) 
,  name                 NVARCHAR(25)      NOT NULL 
  ) 

CREATE TABLE t_media ( 
  Media_id             INT               IDENTITY 
  CONSTRAINT XPKt_media 
     PRIMARY KEY (media_id) 
,  media_format_id      INT               NOT NULL 
  CONSTRAINT XFK01t_media 
     FOREIGN KEY (media_format_id) 
        REFERENCES t_media_format (media_format_id) 
,  name                 NVARCHAR(25)      NOT NULL 
,  copyright            SMALLDATETIME     NULL 
  )

Using this schema, each format needs to be created before it can be used. So for example, you might:

INSERT t_media_formats (name) 
  SELECT 'CD' UNION SELECT 'Cassette' UNION SELECT 'DVD'

Then you might:

INSERT t_media (media_format_id, name, copyright) 
  SELECT mf.media_format_id, '1', NULL 
     FROM t_media_format AS mf WHERE 'CD' = mf.name

For the Beatle's recording of 1, I'd use a NULL copyright, since the remix was copyrighted 2000, but each track has a different copyright date. Once you added this row, you could no longer delete the t_media_format row for 'CD' because this row references it. In this example, the t_media_format row for 'CD' is the "master" or the "parent" row, and the t_media row for '1' is the "detail" or the "child" row.

At least so far, you haven't created any t_media rows that reference the t_media_format rows for either the 'Cassette' or the 'DVD' rows. If you wanted to delete them, you still could.

For More Information


This was first published in April 2001

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close