Two table relation in mysql
You have to ask yourself is this a 1 to 1 relationship or a 1 out of many relationship. That is, does every account have a customer and every customer have an account. Or will there be customers without accounts. Your question implies the latter. Show
If you want to have a strict 1 to 1 relationship, just merge the two tables.
In the other case, the correct way to create a relationship between two tables is to create a relationship table.
Then if you have a customer_id and want the account info, you join on customersaccounts and accounts:
Because of indexing this will be blindingly quick. You could also create a VIEW which gives you the effect of the combined customersaccounts table while keeping them separate Thus far in this book, all the work we've done has been with a single database table. The majority of databases you'll work with as a developer will have more than one table, and those tables will be connected together in various ways to form table relationships. In this chapter we'll explore the reasons for having multiple tables in a database, look at how to define relationships between different tables, and outline the different types of table relationships that can exist. NormalizationAt this point, our To implement some of these requirements we could simply try adding more columns to our Wow, that's a lot of information all in one table! There are other issues here as well, such as duplication of data (often referred to as 'redundancy'). For each book that a user checks out, we have to repeat all of the user data in our table. It's a similar story with the book data, if more than one person checks out a book, such as with 'My Second SQL Book', we have to repeat the book title, author, isbn, and published date. Duplicating data in this way can lead to issues with data integrity. For example what if for one of the 'My Second SQL Book' checkouts the title is entered as 'My 2nd SQL Book' instead, or a typo had been made with the isbn on one of the rows? From looking at the data in the table, how would we know which piece of data is correct? How do we deal with this situation? The answer is to split our data up across multiple different tables, and create relationships between them. The process of splitting up data in this way to remove duplication and improve data integrity is known as normalization. Normalization is a deep topic, and there are complex sets of rules which dictate the extent to which a database is judged to be normalized. These rule-sets, known as 'normal forms', are beyond the scope of this book; for now there are two important things to remember:
We know that we want to split the data for our application across multiple tables, but how do we decide what those tables should be and what relationships should exist between them? When answering questions such as these it is often useful to zoom out and think at a higher level of abstraction, and this is where the process of database design comes in. Database DesignAt a high level, the process of database design involves defining entities to represent different sorts of data and designing relationships between those entities. But what do we mean by entities, and how do different entities relate to each other? Let's find out. EntitiesAn entity represents a real world object, or a set of data that we want to model within our database; we can often identify these as the major nouns of the system we're modeling. For the purposes of this book we're going to try and keep things simple and draw a direct correlation between an entity and a single table of data; in a real database however, the data for a single entity might be stored in more than one table. What entities might we define for our SQL Book application?
Now we have defined the entities we need, we can plan tables to store the data for each entity. Those tables might look something like this: RelationshipsWe're making good progress with our database design. We've decided on the entities we want and have formed a picture of the tables we need, the columns in those tables, and even examples of the data that those columns will hold. There's something missing though, and that's the relationships between our entities. If we look at the diagram of our five tables, the tables are all isolated and it's not obvious how these tables should relate to each other. Let's simplify our tables a bit and explicitly define some relationships between them. This diagram shows an abstract representation of our various entities and also the relationships between them, (note: in reality we could imagine that more than one user might share the same address; this structure is intended for illustration purposes). We can think of this diagram as a simple Entity Relationship Diagram, or ERD. An ERD is a graphical representation of entities and their relationships to each other, and is a commonly used tool within database design. There are different types of ERD varying from conceptual to detailed, and often using specific conventions such as crow's foot notation to model relationships. We won't go into the details of these different types, or the conventions they use, in this book. For now it's useful to simply think of an ERD as any diagram which models relationships between entities. KeysOkay, so we now know the tables that we need and we've also defined the relationships that should exist between those tables in our ERD, but how do we actually implement those relationships in terms of our table schema? The answer to that is to use keys. In an earlier section of this book we looked at an aspect of schema called constraints, and explored how constraints act on and work with the data in our database tables. Keys are a special type of constraint used to establish relationships and uniqueness. They can be used to identify a specific row in the current table, or to refer to a specific row in another table. In this chapter we'll look at two types of keys that fulfill these particular roles: Primary Keys, and Foreign Keys. Primary KeysA necessary part of establishing relationships between two entities or two pieces of data is being able to identify the data correctly. In SQL, uniquely identifying data is critical. A Primary Key is a unique identifier for a row of data. In order to act as a unique identifier, a column must contain some data, and that data should be unique to each row. If you're thinking that those requirements sound a lot like our The
Although any column in a table can have Being able to uniquely identify a row of data in a table via that table's Primary Key column is only half the story when it comes to creating relationships between tables. The other half of this story is the Primary Key's partner, the Foreign Key. Foreign KeysA Foreign Key allows us to associate a row in one table to a row in another table. This is done by setting a column in one table as a Foreign Key and having that column reference another table's Primary Key column. Creating this relationship is done using the
We'll explore some specific examples of how this is used when we look at setting up various kinds of relationships later in this chapter, but in general terms you can think of this reference as creating a connection between rows in different tables. Imagine for instance that we have two tables, one called In the diagram above, the 'Red' row of our By setting up this reference, we're ensuring the referential integrity of a relationship. Referential integrity is the assurance that a column value within a record must reference an existing value; if it doesn't then an error is thrown. In other words, PostgreSQL won't allow you to add a value to the Foreign Key column of a table if the Primary Key column of the table it is referencing does not already contain that value. We'll discuss this concept in a bit more detail later on. The specific way in which a Foreign Key is used as part of a table's schema depends on the type of relationship we want to define between our tables. In order to implement that schema correctly it is useful to formally describe the relationships we need to model between our entities:
The entity relationships described above can be classified into three relationship types:
Let's look at them each in turn. one-to-oneA one-to-one relationship between two entities exists when a particular entity instance exists in one table, and it can have only one associated entity instance in another table. Example: A user can have only one address, and an address belongs to only one user. This example is contrived: in the real world, users can have multiple addresses and multiple people can live at the same address. In the database world, this sort of relationship is implemented like this: the
Executing the above SQL statement will create an Let's go ahead and add some data to our table.
The Referential IntegrityWe're going to take a slight detour here to discuss a topic that's extremely important when dealing with table relationships: referential integrity. This is a concept used when discussing relational data which states that table relationships must always be consistent. Different RDBMSes might enforce referential integrity rules differently, but the concept is the same. The constraints we've defined for our What happens if we try to add another address for a user who already has one?
The error above occurs because we are trying to insert a value How about if we try to add an address for a user who doesn't exist?
Here we get a different error. The If you're wondering why we can add a user without an address but can't add an address without a user, this is due to the modality of the relationship between the two entities. Don't worry about exactly what this means for now, just think of it as another aspect of entity relationships. The ON DELETE clauseYou might have noticed in the table creation statement for
our Determining what to do in situations where you delete a row that is referenced by another
row is an important design decision, and is part of the concept of maintaining referential integrity. If we don't set such clauses we leave the decision of what to do up to the RDBMS we are using. In the case of PostgreSQL, if we try to delete a row that is being referenced by a row in another table and we have no One-to-ManyOkay, time to get back to our different table relationship types with a look at one-to-many. A one-to-many relationship exists between two entities if an entity instance in one of the tables can be associated with multiple records (entity instances) in the other table. The opposite relationship does not exist; that is, each entity instance in the second table can only be associated with one entity instance in the first table. Example: A review belongs to only one book. A book has many reviews. Let's set up the necessary data. First let's create our tables
These table creation statements for our
Note that the foreign key Now we have created our
The order in which we add the data is important here. Since a column in Just as with the In a real database
our Many-to-ManyA many-to-many relationship exists between two entities if for one entity instance there may be multiple records in the other table, and vice versa. Example: A user can check out many books. A book can be checked out by many users (over time). In order to implement this sort of relationship we need to introduce a third, cross-reference, table. This table holds the
relationship between the two entities, by having two Here, the
We can see on the first row of Don't worry if you don't completely understand this right away. Shortly, we'll expand on what these associations look like in terms of the data in
You may have noticed that our table contains a couple of other columns This kind of additional context can be useful within
the business logic of the application using our database. For example, in order to prevent more than one user trying to check out the same book at the same time, the app could determine which books are currently checked out by querying those that have a value in the As with one-to-many relationships, the foreign keys in many-to-many relationships should not allow Now that we have our
Let's have a look at what this data looks like in terms of the relationships between the tables. Here we can see that the We can perhaps think of a many-to-many relationship as combining two one-to-many relationships; in this case between SummaryIn this chapter we covered a number of different topics regarding table relationships:
To recap, here is a list of common relationships that you'll encounter when working with SQL:
Earlier in this book we looked at how to query data in a database table using How can I find the relationship between two tables in MySQL?To see foreign key relationships of a column:
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA. KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = 'db_name' AND REFERENCED_TABLE_NAME = 'table_name' AND REFERENCED_COLUMN_NAME = 'column_name';
Can 2 tables have 2 relationships?EDIT: Yes, of course there can be two relationships between the same two tables.
What is the relationship between two tables?A relationship works by matching data in key columns, usually columns (or fields) that have the same name in both tables. In most cases, the relationship connects the primary key, or the unique identifier column for each row, from one table to a field in another table.
How do you create a relationship between two tables in SQL?Use SQL Server Management Studio. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and select Design. ... . From the Table Designer menu, select Relationships. ... . In the Foreign-key Relationships dialog box, select Add. ... . Select the relationship in the Selected Relationship list.. |