Database relationship diagram;The SQL serverssms GUI provides a very useful tool. It can display the relationship between tables and other objects in a graphical manner, and we can also add, modify, and other database objects in a graphical way. Before learning, let's take a look at what is the relationship between tables and foreign key constraints.
Relationship between tables and foreign key constraintsA foreign key is a field used to establish a link between two tables. You can create a link between two tables by adding the primary key of one table to another, this field becomes the foreign key of another table. Let's give an example. A link exists between the class and student tables. Because there is a logical connection between the class and the students. Now we create a classid column in student of the student table so that it corresponds to the classid in the class table. In this way, the classid column in the class table is the foreign key column in the student table. The specific operation is as follows: Open the design view of the student table in the student table. Add a classid column under studentid. the type and length are the same as those in the class table. null values are not allowed. Click the attribute button on the toolbar. Open the attribute dialog bar of the student table and switch to the link option. Click "new". A relationship between tables is displayed. The Link name is fk_student_class, and class is the table where the primary key is located. Student is the table where the foreign key is located. Select the classid column from the drop-down list of the class table. Select the classid column in the drop-down list of the student table. Because we want to establish a relationship between the two tables through classid. "Check Existing data in Creation" indicates whether to check existing records. "Cascade deletion related records" if a class does not exist, the system automatically deletes all student records. "Cascade update related records" indicates that one student in student belongs to Class 2, but now the classid of Class 2 is changed to Class 4, the value of the corresponding record in student will also be changed to 4. Click Close and return to the design view to save. The system prompts that both tables must be saved, but we have modified only one table, which indicates that we have established a connection between them. Save.
Next we will create a database relationship diagram, and let everyone learn to use the database relationship diagram to do some common things. Open the dufei database in the SSMs graphic interface and open it. Select the graph node, right-click the area on the right, and select the new database graph in the shortcut menu that appears, open the new database relationship diagram wizard. You can see in the welcome wizard that you can create a new Wizard and add tables to it. You can also automatically add related tables based on this table. The system can automatically arrange these tables in the graph. Next, continue. The window for the table to be selected appears. There are two lists in this window, the available tables on the left and the tables that can be added to the graph on the right. For example, the personnel table and company table in the first table. At the bottom of the table on the left, what is the function of the check box "automatically add related table options? After a foreign key link is established between the personnel table and the company table, the two tables become mutually linked tables. Right? When we establish a relationship graph, are these two tables indispensable? It is very likely that after we add a table to the graph, the other table will also be added to the graph. In this case, we can select this option. Select the personnel table and add it to the graph. The company table is automatically added. Click Next. At this time, we can see that the system automatically adds the personnel and company tables to the graph and arranges them to make them more clear. After that, we can also select them with the mouse, adjust their positions. In the graph, each table is Graphically displayed. The personnel table and company table are displayed in a box, and they are linked through a chain, the names of each column are also displayed, right. We can also change the column name of the table, instead of being limited to graph. Perform the modification operation, and then verify it in the table. Select a chain between them to check the chain attributes. This chain is just created. The two ends are different. one end of the key is in contact with the class, indicating that this is the primary key and the other end is infinite. This indicates that it is a foreign key, and the right-click "attribute" opens the link window we used. The above content can be explained in the department table, employee table, and leadership table! In relational databases, relationships can prevent redundant data. If multiple employees belong to the same department. Should every employee describe the department information once, which will lead to data duplication! After the relationship between tables is set, only one pointer is required to reference relevant information. It usually uses a one-to-many relationship! If you select the force relationship between insert and update, when the foreign key table is inserted and updated, the system checks whether the primary key value is correct. If there is no corresponding primary key value in the primary key table, the operation is prohibited. Cascade update related fields: whenever the primary key value is updated, it indicates that the foreign key value of the link is automatically updated to delete related records: when a record is deleted from the primary key table, related records are deleted from the foreign key table.
