Skip to main content

Adding foreign keys in phpMyAdmin / Query

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:
  1. For both tables, go to the operations tab and change their type to "INNO DB"
  2. Make sure ID is the primary key (or at least an indexed column) of the PARENT table.
  3. In the CHILD table, define an index for the PID column.
  4. While viewing the structure tab of the CHILD table, click the "relation view" link just above the "add fields" section.
  5. 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
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.
Phpmyadmin Foreign Key
Phpmyadmin Foreign Key
corresponding SQL query automatically generated and executed.


Query: -
ALTER TABLE yourtable
ADD CONSTRAINT fkey
FOREIGN KEY (P_Idq)
REFERENCES Personstable(P_Idq)

Comments

Popular posts from this blog

Login and Registration Example in JSP with Session

Those who want to start with jsp and MySQL, this is an excellent example for themselves. Here you can learn how to insert data to MySQL using JSP. Also you can learn about session handling in jsp. 1 2 3 4 5 6 7 8 9 10 CREATE TABLE `members` (    `id` int (10) unsigned NOT NULL auto_increment,    `first_name` varchar (45) NOT NULL ,    `last_name` varchar (45) NOT NULL ,    `email` varchar (45) NOT NULL ,    `uname` varchar (45) NOT NULL ,    `pass` varchar (45) NOT NULL ,    `regdate` date NOT NULL ,    PRIMARY KEY   (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; index.jsp 1 2 3 4 5 6 ...

Timer funcitons in JavaScript: Reminder Script

Timers are used in web pages and we will discuss how to setup one and how to use one with an example. The function setTimeout() in function is used and here is its general syntax. mytime = setTimeout(expression, msec); mytime  is the identifier used to identify the current timeout function.  expression  is the statement that is to be executed after the specified time has ticked off.  msec  is the duration of time in milliseconds after which the expression will be executed.  You can see by using setTimeout function we can execute any function or object after a set of time. For example if msec is set 5000 then the expression will be executed after 5 seconds or 5000 milliseconds.  We will try one example where we will have four period buttons and each button will set a different time for another function to execute and display a alert button. We will call it as a reminder script and we will get one alert box based on the period button we click...

Binary Addition

/* File Name : BinAdd.java */    import java.util.*; public class BinAdd    {  public static String addBit(String a, String b, String c)  { String r=""; if(a.equals("1") && b.equals("0") || a.equals("0") && b.equals("1")) { if( c.equals("0")) r="1"; else { r="0"; c="1"; } } else if( a.equals("0") && b.equals("0") ) { if(c.equals("0")) r="0"; else r="1"; } else if( a.equals("1") && b.equals("1") ) { if(c.equals("0")){ r="0"; c="1"; } else { r="1"; c="1"; } } return c+r; }   public static String add(String a, String b)   { String r=""; int len=a.length(); String carry="0"; for(int i=len-1;i...

Real time changing Clock showing date and time

We can display a clock which will be showing the local time of the client computer by using JavaScript. Note that this time displayed is taken from user computer and not from the server.  We have seen in our  date object example how to display current date and time   where the current date and time is displayed once. Here we will try to display changing clock or real time change in date and time by using  setTimeout function . This setTimeout function we have used to trigger the time display function in a refresh rate of 1000 mill seconds ( 1 Sec ). This refresh rate can be changed to any other value. By changing the value to 5000 the clock will refresh and show the exact time once in every 5 seconds.  Here is the demo of this script and code is given below that.  Sat Apr 23 2016 08:27:22 GMT+0530 (IST)   Here is the code <html> <head> <title>(Type a title for your page here)</title> <script type="text/javascript...