Delete properties of Master/Detail relationship

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


    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top