MySQL FOREIGN key use

first, foreign key1foreign key: A field that links two tables, describing the primary foreign key relationship through the primary key of the primary table and the foreign key from the table, presenting a one-to-many relationship.　　For example: commodity category (i) on commodity (multi), Main Table: Commodity category table, from table: Commodity table. 2foreign Key Features: The value from the foreign key is a reference to the primary table primary key, and must be consistent with the primary table primary key type from the table foreign key type3. Declaring a FOREIGN KEY constraint syntax: ALTER TABLE (from table) add [constraint] (foreign key name) foreign key (from table foreign key field name) references primary table (primary key of primary table) for example: (1). AlterTABLEProduct (from table)ADDCONSTRAINT CID (foreign key name) FOREIGN key (CID) (from the Table foreign key field name) REFERENCES category (primary table) (CID) (primary key of the main table); (2). AlterTABLEProductADDFOREIGN KEY (CID) REFERENCES category (CID); 　　[Foreign key name] is used to delete foreign key constraints, generally recommended "_FK" end alter TABLE from the table drop foreign key foreign key name; 3. Purpose of using foreign keys: ensure data integrity4Note: You cannot add a record from a table that does not exist in the primary table, the primary table cannot delete the records that have already been referenced in the table, the relationship between the table and the table, and that is the relationship between the table and the table data. 1. One-to-many relationships: Common instances: Customer and order, classification and commodity, department and employee A-to-many table principle: Create a field from a table (multiparty), a field as the primary key syntax for a foreign key to a primary table (one party): ALTERTABLEProductADDFOREIGN KEY (CID) REFERENCES category (CID); 2. Many-to-many relationships: Common examples: Students and courses, goods and orders, people and roles many-to-many table principle: need to create a third table, the intermediate table must have at least two fields, the two fields as foreign keys to the respective party's primary key (that is, a many-to-many split into two one-to-many) two tables are main table, the third 　　　　Table, which provides two fields, are foreign keys.　　　　Syntax: ALTER TABLE from table (stu_course) Add foreign key (SNO) references Stu (SID); 　　ALTER TABLE from table (stu_course) Add foreign key (CNO) References course (CID); 3. Single-to-one relationship: (understanding) in the actual development of the application is not many, because a-to can create a table common instances: Company and address two principles: foreign key is unique, foreign key is the primary key three, Operation1. Multiple table Queries (1). Cross Join query (two tables are multiplied, not used) Select*From a a , B; (2). Internal connection query (using keyword: INNER join--inner can be omitted) implicit in-Connection: Select*From a a where condition; For example: Select* from category C,product p where c.cid=P.cid; Explicit Intra-connection: select*From A inner join B on condition; For example: Select* FROM category as C inner join product as p on p.cid=C.cid; (3). Outer join query (using the keyword outer JOIN--outer can be omitted) left outer connection: Leave outer JOIN Select*From A left outer join B on condition; For example: Select* FROM category C left join product p on p.cid=C.cid; Right outer connection: outer JOIN Select*From A right outer join B on condition; For example: Select* from category C right join product p on p.cid=C.cid; The difference between inner and outer joins: LEFT OUTER join: The query is connected within the intersection of all and two tables of the left table: The query is the intersection of two tables right outer joins: The query is the intersection of all and two tables of the right table2. Subquery A SELECT statement result as part of another select syntax (query criteria, query results, tables, and so on) For example: Query cosmetics category listing item details select* FROM product WHERE cid = (select CID from category where cname=' Botong Tong');

MySQL foreign key use