Ask the Expert

Delete properties of Master/Detail relationship

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


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: