phpMyAdmin lets you define foreign keys using their "relations" view. But since, MySQL only supports foreign constraints on "INNO DB" tables, the first step is to make sure the tables you are using are of that type.
To setup a foreign key so that the PID column in a table named CHILD references the ID column in a table named PARENT, you can do the following:
- For both tables, go to the operations tab and change their type to "INNO DB"
- Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
- In the CHILD table, define an index for the PID column.
- While viewing the structure tab of the CHILD table, click the "relation view" link just above the "add fields" section.
- You will be given a table where each row corresponds to an indexed column in your CLIENT table. The first dropdown in each row lets you choose which TABLE->COLUMN the indexed column references. In the row for PID, choose PARENT->ID from the dropdown and click GO.
By doing an export on the CHILD table, you should see a foreign key constraint has been created for the PID column.
Different types of relationships you can stipulate in PHPmyadmin
CASCADE
Whenever rows in the master (referenced) table are deleted (resp. updated), the respective rows of the child (referencing) table with a matching foreign key column will get deleted (resp. updated) as well. This is called a cascade delete (resp. update[2]).
RESTRICT
A value cannot be updated or deleted when a row exists in a foreign key table that references the value in the referenced table. Similarly, a row cannot be deleted as long as there is a reference to it from a foreign key table.
NO ACTION
NO ACTION and RESTRICT are very much alike. The main difference between NO ACTION and RESTRICT is that with NO ACTION the referential integrity check is done after trying to alter the table. RESTRICT does the check before trying to execute the UPDATE or DELETE statement. Both referential actions act the same if the referential integrity check fails: the UPDATE or DELETE statement will result in an error.
SET NULL
The foreign key values in the referencing row are set to NULL when the referenced row is updated or deleted. This is only possible if the respective columns in the referencing table are nullable. Due to the semantics of NULL, a referencing row with NULLs in the foreign key columns does not require a referenced row.
SET DEFAULT
Similar to SET NULL, the foreign key values in the referencing row are set to the column default when the referenced row is updated or deleted.
In phpmyadmin, you can assign Foreign key simply by its GUI. Click on the table and go to Structure tab. find the Relation View on just bellow of table (shown in below image).
![Phpmyadmin Relation View Link](https://www.binarytides.com/blog/wp-content/uploads/2009/08/phpmyadmin_relation_view-600x443.png)
Phpmyadmin Relation View Link
In the Relation view page the field (which was made an INDEX) can be made a foreign key referencing to some other field in another table.
corresponding SQL query automatically generated and executed.
Query: -
ALTER TABLE yourtable
ADD CONSTRAINT fkey
FOREIGN KEY (P_Idq)
REFERENCES Personstable(P_Idq)
Comments
Post a Comment