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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Database Design Web Links: tips, tutorials, scripts, and more.
- Have a Database Design tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Database Design questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Database Design guru is waiting to answer your toughest questions.
This was first published in April 2001