What do you understand by Master detailed relationship? Discuss its delete properties.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Related Q&A from Pat Phelan
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.