CODEWITHSUNDEEP
mysqlsqlphpmyadmin
4lisalehi
4lisalehi

Reputation: 29

Creating a relationship in phpMyAdmin

Being new to phpMyAdmin, I want to create a relation between a column (column_1) of one of my tables (Table_1) with a column (column_2) in another table (Table_2).

I first index column_1 in Table_1 and when I want to create the relation in relation view tab of Table_1, in front of column_1, I first choose the database from the first drop down menu, choose the Table_1 in second drop down menu, but the third drop down menu (Which seems should include column_2) has no select options.

My phpMyAdmin version is: 4.1.6.

Below is the result of my "CREATE SHOW my_table":

Table_1:

CREATE TABLE `Table_1` (

`column_1` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`column_1`),
KEY `FK_COLUMN1` (`column_1`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Table_2:

 CREATE TABLE `Table_2` (
`column_2` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`column_2`),
KEY `FK1_COLUMN2` (`column_2`))
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Upvotes: 0

Views: 5903

Answers (3)

Peter van der Wal
Peter van der Wal

Reputation: 11856

I don't know what's wrong with PMA. Following query should create the reference. If that fails the error-message will exlpain more ;)

ALTER TABLE `this_table`
    ADD CONSTRAINT `name_of_the_constraint` FOREIGN KEY (`this_column`) 
    REFERENCES `external_table` (`external_column`) 
    ON DELETE CASCADE ON UPDATE CASCADE;

ON DELETE CASCADE does mean that if the row in external_table got deleted, the row in this_table also will be removed. Also valid options are RESTRICT and SET NULL. Same applies for UPDATE (row in this_table will also be updated).

Edit:

To answer your comments: I've run following SQL on a new empty database/scheme and all runs perfectly fine without errors. If it doesn't at your machine I don't know what went wrong (I can't reproduce it). If following also runs at yours, what's different whith that what you were trying before?

CREATE TABLE `Table_1` (

`column_1` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`column_1`),
KEY `FK_COLUMN1` (`column_1`))
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `Table_2` (
`column_2` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`column_2`),
KEY `FK1_COLUMN2` (`column_2`))
ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `Table_1` (`column_1`) VALUES ( '1' ), ( '2' ); /* Add some both valid as invalid relations */
INSERT INTO `Table_2` (`column_2`) VALUES ( '1' ), ( '3' ); /* Add some both valid as invalid relations */

SET foreign_key_checks = 0;
ALTER TABLE Table_1 ADD FOREIGN KEY (column_1) REFERENCES Table_2(column_2) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE Table_2 ADD FOREIGN KEY (column_2) REFERENCES Table_1(column_1) ON DELETE RESTRICT ON UPDATE RESTRICT;

NB. Normally you only create the foreign key one direction. I've added both to show that either direction should work in this setup.

Upvotes: 2

Hossam Aldeen Ahmed
Hossam Aldeen Ahmed

Reputation: 782

select your database

from the menu choose Designer

you will find all of your tables .

from the small menu select create relation enter image description here

then choose your reference key and your foreign key

Upvotes: 1

user3901786
user3901786

Reputation: 103

To clarify:

Table_1 has an index column1 Table_2 has a column2 that you want to use as a foreign key into Table_1

I think you need to make column1 a Primary Key for Table_1 in order for this to work.

Once you do that, the third drop-down in relationship view for Table_2 will have column_1 as a value that you can select.

Hope this helps.

Upvotes: 1

Related Questions