PHP Data Connection primary KEY and foreign key!

Set MySQL data table primary key: Use the "PRIMARY key" keyword to create a primary key data column. The primary key column is set to not allow duplicate values, in many cases combined with "auto_increment" incrementing numbers. As shown in the following SQL statement:

To modify a column primary key or type, refer to the ALTER TABLE statement. Set MySQL data table foreign key The foreign key is set to associate a column in the current table with a primary key column in a data table. The main purpose is to control the data in the Foreign key table, maintain data consistency, integrity, that is, the data in the current table must be a data in the associated foreign key column, and the associated two data columns must be the same type; When a data modification or deletion is associated with a foreign key column, a corresponding action is touched on the current table. The following events and parameters can be extracted: Trigger event: On delete and on update parameters: Cascade (following the foreign key changes), restrict (restricting foreign key changes in the appearance), set null (null value), set default (setting defaults), no AC tion [Default] Sets the statement that is associated by the [Specify PRIMARY KEY keyword: foreign key (column name)] and [reference foreign KEY keyword: references < foreign key table name > (foreign key Column name)]. For example, create a shopping cart data sheet "GBooks" about books, where "Gbookid" creates a foreign key association with "BookID" in the books table.

Delete foreign key: First, view the CREATE table description using the show CREATE TABLE statement. Where the "CONSTRAINT" keyword is followed by a quoted name, which is the representation of the foreign key, the name that is automatically generated when the foreign key is created and, of course, the "CONSTRAINT" keyword can be used to customize the name directly during creation. The complete statement of its view is as follows:

Here, the name of the foreign key is "Gbooks_ibfk_1", the target is found, and then the ALTER statement is used to delete it.

Primary and foreign keys are set to make sense: primary and foreign keys are binders that organize multiple tables into an effective relational database. The design of primary keys and foreign keys has a decisive impact on the performance and availability of the physical database. The database schema must be converted from a theoretical logical design to an actual physical design. The structure of the primary key and the foreign key is the crux of the design process. Once the designed database is used in production environments, it is difficult to modify these keys, so it is necessary and worthwhile to design the primary and foreign keys in the development phase. Primary key: A relational database relies on a primary key-it is the cornerstone of a database physical pattern. There are only two uses of the primary key on the physical level: 1. Uniquely identifies a row. 2. As an object that can be effectively referenced by a foreign key. Based on these two uses, here are some of the principles that I followed when I designed my primary key for physics: 1. The primary key should not be meaningful to the user. If a user sees data in a connection table that represents a many-to-many relationship and complains that it is useless, it proves that its primary key is well designed. 2. The primary key should be single-column to improve the efficiency of the connection and filtering operations. 3. Never update the primary key. In fact, there is no reason to update a primary key because it has no other purpose than to uniquely identify a row. Note: This principle does not apply to data that is often required for data consolidation when data conversion or multi-database merging. 4. The primary key should not contain dynamically changing data such as timestamps, creation time columns, modified time columns, and so on. 5. The primary key should be automatically generated by the computer. If a person is to intervene in the creation of a primary key, it will have a meaning other than a single row of identifiers. Once this boundary is crossed, it is possible to assume the motivation to modify the primary key, so that the key means used by the system to link record lines and manage record lines will fall into the hands of those who do not understand the design of the database. Foreign keys are used to relate to other tables: one column in this table is the same as a column in another table, in order for the two tables to be linked, the columns in one table are set as foreign keys, and the other table column is set as the primary key, and the two tables are associated. A table can have more than one foreign key. But there can only be one primary key. Relational database management systems like MySQL, which are based on the ability to create relationships between tables in a database. By easily establishing a record-to-record connection in different tables, the RDBMS can analyze the data in different ways, while keeping the database organized in a systematic way and with minimal redundancy. Relational database management systems like MySQL, which are based on the ability to create relationships between tables in a database. By easily establishing a record-to-record connection in different tables, the RDBMS can analyze the data in different ways, while keeping the database organized in a systematic way and with minimal redundancy. Simple description: These relationships are basically managed by a foreign key, in which all tables in the relationship haveFields of the same meaning are used as public parts to connect records in different tables. A foreign key can be one-to-two, a table record can only be connected to one record of another table, or one-to-many, and a table record is connected to multiple records from another table. The definition of "key" and "index" in MySQL is the same, so the foreign key and the primary key are also one of the indexes. The difference is that MySQL automatically indexes the primary keys for all tables, but the foreign key fields must be explicitly indexed by the user. This is the same as some of the more heavily feudal families, foreign children (daughter-in-law, inverted into) are generally not respected. Vulgar Example: a one-to-one relationship between tables example: There are two tables, the first table is a record of how many people, who, that is, employee number and employee name these basic tables. Another table records how much wages are paid to users each month, so-called payroll is also. But the payroll can not be the name of the employee as the primary key, also through the employee ID, because the employee's name is likely to repeat AH. Department manager called Zhang San, younger brother also called Zhang San, then these two Zhang San wages can be same? And everyone in the employee table has a salary, otherwise no one will give you work, and a person can only have a salary, otherwise the boss also disagree. So the employee and Payroll tables are related to one-to-a relationships through employee IDs. But we should have a good value, we can not work for the money, we are to learn knowledge, learn culture, for the early realization of four modernizations (don't ask me what, also don't ask me in the end do not) and strive. So don't shout in the salary table if you don't have it. Well. /* Create an Employee table */CREATE TABLE employees (ID int (5) NOT NULL auto_increment, name varchar (8) NOT NULL, PRIMARY key (ID)) type =innodb; /* Create a payroll table */CREATE TABLE payroll (ID int (5) not NULL, emp_id int (5) is not NULL, name varchar (8) is not NULL, payroll float (4,2) Not NULL, PRIMARY key (ID), index emp_id (emp_id), foreign Key (emp_id) references employees (ID)) type = InnoDB; Example of a one-to-many relationship between tables: There are two tables, the corrupt officials table, and the ID and name of the corrupt official. There is also a corrupt official mistress table, note that a corrupt official is not necessarily only a mistress, it has a 2345 milk is very normal, so in the corrupt officials table inside a data, corresponding to Mistress list may haveMultiple records, which is a one-to-many relationship that is associated with a corrupt official ID. Referential integrity: When a foreign key is related to a field in another table, and this relationship is temporary, the system is called a state of referential integrity. That is, if a field appears only once in all tables, and the change in this field of each table affects the other tables, there is referential integrity. The terminology may not be easy to understand, in fact, to maintain consistency of all data in tables that have foreign keys. For example, "Zhang San" quit, in the employee table there must be no such person, but if there is still a child in the salary table, then the eldest brother will be very angry. In addition, for example, a magistrate, because some small achievements, from the magistrate to become a magistrate, then his mistress of the status also to adjust, at least from the Magistrate mistress changed to the Magistrate mistress, otherwise the mistress will not agree. MySQL's foreign keys can only be used in the InnoDB table: today's mainstream databases automatically consider referential integrity issues. When you update or delete data, it will change the data in the associated tables to you. Biru official Zhang San renamed King Two Leper, the title of his mistress will automatically changed to Wang Er leper's mistress. Well. MySQL has always been a wait-and-see attitude, which allows the use of foreign keys, but for the purpose of integrity checking, this functionality is ignored in all table types except for the InnoDB table type. This may be weird, but it's actually quite normal: for each insert, update, and delete of all foreign keys for a database, an integrity check is a time-consuming and resource-intensive process that can affect performance, especially when dealing with complex or entangled connection trees. As a result, users can choose the best combination for specific needs on the basis of the table. Therefore, if you need better performance and do not need integrity checking, you can choose to use the MyISAM table type, and if you want to build the table in MySQL based on referential integrity and want to maintain good performance on this basis, it is best to choose the table structure as the InnoDB type. MySQL creates foreign key syntax: The syntax for creating foreign keys is this: FOREIGN key (the field name of the current table) ... REFERENCES reference table (field name of the reference table) foreign key (emp_id) REFERENCES employees (ID); This means that the emp_id field of the current table is a foreign key with the ID of employees. Note: Once a foreign key is created, MySQL only allows data columns that are already in the foreign key table to be added to the current table. For example, the corrupt officials have "king two leper", then the Mistress table only can have "Wang er leper mistress." That is, only to confirm that a person is a corrupt official, in order to put his mistress information in this table, otherwise it is no drop. All tables in the relationship must be InnoDB tables, in non-InnoDB tables, MySQL will ignore foreign KEY ... The references modifier. The fields used for foreign key relationships must be explicitly indexed in all reference tables, and InnoDB cannot automatically create indexes. In a foreign key relationship, the data type of the field must be similar, which is especially important for integer types that must match both size and symbol. MySQL allows us to delete a table even if the table has a foreign key constraint, and does not produce an error (even if doing so may break the foreign key created earlier) delete the foreign key method: Long ago, people can only delete a foreign key by deleting the table. But now MySQL (in 4.0.13 and later) provides a way to reduce the ease of removing foreign keys from a table, but it's not clear, but at least it's no longer so shameless. ALTER TABLE table-name DROP FOREIGN KEY Key-id; Here is a concept, what is the ID of this foreign key? We can get the value of Key-id by the Show CREATE Table command. We will discuss this in detail in the future and you can demonstrate it yourself. /* Displays the build table structure statement, Key-id for Payroll_ibfk_1 */show CREATE TABLE payroll/g/* *************************** 1. Row *************************** table:payroll Create table:create Table ' payroll ' (' id ' int (5) Not NULL, ' emp_id ' int (5 Not NULL, ' name ' varchar (8) is not NULL, ' Payroll ' float (4,2) is not NULL, PRIMARY key (' ID '), key ' emp_id ' (' emp_id '), Constr AINT ' Payroll_ibfk_1 ' FOREIGN KEY (' emp_id ') REFERENCES ' employees ' (' ID ')) engine=innodb DEFAULT charset=latin1 1 row in Set (0.00 sec) */Auto key update and delete: foreign keys guarantee the integrity of newly inserted records. But what happens if I delete a record from a named table in a references clause? What happens in a secondary table that uses the same value as a foreign key? Obviously, those records should also be deleted, otherwise there will be many in the database withoutMeaning of the orphan record. MySQL may pass to foreign KEY ... The REFERENCES modifier adds an on delete or on UPDATE clause to simplify the task, which tells the database how to handle orphaned tasks in this situation. Note that with the on UPDATE and on delete rules, setting MySQL to perform automatic operations can result in serious data corruption if the key relationship is not set. For example, if a series of tables are connected by a foreign key relationship and an on delete CASCADE rule, any change in the primary table will cause even only the original deletion of some records that will be contacted to be deleted without warning. So, we still have to check these rules before we operate, and we need to check them again after the operation.