The reason you would do this is to ensure that the integrity of the data<\/a> is not compromised.<\/p>\n\n\n\n Consider the following database diagram.<\/p>\n\n\n\n The dashed lines represent a relationship between the tables. <\/p>\n\n\n\n I have a relationship between the Artists<\/strong> and Albums<\/strong> tables because the Albums<\/strong> table contains the ArtistId<\/strong> (so that I know which artist released each album). The reason I established this relationship is because I don’t want anyone to enter a non-existent ArtistId<\/strong>. That is, I don’t want the Albums<\/strong> table to contain an ArtistId<\/strong> that doesn’t exist in the Artists<\/strong> table.<\/p>\n\n\n\n You can see that I’ve also established a relationship between the Genres<\/strong> table and the Albums<\/strong> table. And I’ve done this for the same reason – I don’t want the Albums<\/strong> table to contain a GenreId<\/strong> that doesn’t exist in the Genres<\/strong> table. If an album is from a genre that doesn’t exist in the database, someone will need to first enter that genre into the Genres<\/strong> table. Once they’ve done that, they can go ahead and enter the album from that genre.<\/p>\n\n\n\n You’ll often hear of 3 types of relationships<\/a> that can be applied to a relational database. Here’s an overview of each one.<\/p>\n\n\n\n A one-to-one relationship<\/a> is a relationship between two tables where each table can have only one matching row in the other table.<\/p>\n\n\n\n Using the above screenshot as an example, the business case is that each employee’s pay details must be stored in a separate table to the employee’s contact details. In such a case, there can only be one row in the Pay<\/strong> table that matches a given employee in the Employees<\/strong> table. This is a good candidate for a one-to-one relationship.<\/p>\n\n\n\n The one-to-many relationship<\/a> is similar to the one-to-one relationship, except that it allows multiple matching rows in one of the tables.<\/p>\n\n\n\n In the above example, each author can have many books, but each book can only have one author. <\/p>\n\n\n\n Therefore, the Books<\/strong> table is allowed to contain multiple rows with the same AuthorId<\/strong> value. If an author has released five books, then there would be one row in Authors<\/strong> for that author, and five rows in Books<\/strong>, each with that author’s AuthorId<\/strong>.<\/p>\n\n\n\n In a many-to-many relationship<\/a>, each side of the relationship can contain multiple rows.<\/p>\n\n\n\n In this example, each book is allowed to have multiple authors. Therefore, I created a lookup table (also known as a “junction table”) that stores both the AuthorId<\/strong> and the BookId<\/strong>. <\/p>\n\n\n\n These two columns could be configured to be the primary key<\/a> of the table (in which case they would be a “composite primary key” or simply “composite key”), or you could create a separate column to be the primary key. <\/p>\n\n\n\n Note that the Books table doesn’t have AuthorId<\/strong> in this case. That column has been moved to the AuthorBooks<\/strong> table so that we can have many AuthorId<\/strong>s for the same BookId<\/strong>.<\/p>\n\n\n\n Database relationships can be created via a GUI tool (such as SQL Server Management Studio<\/a>, Microsoft Access, etc) or by running an SQL<\/a> statement.<\/p>\n\n\n\n Examples:<\/p>\n\n\n\n When creating a relationship with SQL, you do it by applying a foreign key<\/a> constraint against the child table. This foreign key references the primary key in the parent table.<\/p>\n\n\n\n Examples<\/p>\n\n\n\n If you’re new to relational databases, and you’re trying to get your head around this concept of a “relationship” in your database, I hope this article helps.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[19,20],"class_list":["post-14815","post","type-post","status-publish","format-standard","hentry","category-database-concepts","tag-relationships","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/14815","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=14815"}],"version-history":[{"count":4,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/14815\/revisions"}],"predecessor-version":[{"id":47190,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/14815\/revisions\/47190"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=14815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=14815"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=14815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}![\"Diagram](\"https:\/\/database.guide\/wp-content\/uploads\/2021\/02\/Relationship-Diagram.png\")
<\/figure>\n\n\n\nTypes of Relationships<\/h2>\n\n\n\n
One-to-One<\/h3>\n\n\n\n
![\"Diagram](\"https:\/\/database.guide\/wp-content\/uploads\/2021\/02\/Relationship-One-to-One.png\")
One-to-Many<\/h3>\n\n\n\n
![\"Diagram](\"https:\/\/database.guide\/wp-content\/uploads\/2021\/02\/Relationship-One-to-Many.png\")
Many-to-Many<\/h3>\n\n\n\n
![\"Diagram](\"https:\/\/database.guide\/wp-content\/uploads\/2021\/02\/Relationship-Many-to-Many.png\")
Creating a Database Relationship<\/h2>\n\n\n\n
\n
\n