phpmyadmin Question posted in
The official documentation can be found here.

How to create a relational database with Primary and Foreign keys in Phpmyadmin?

I have installed XAMPP on my Windows Computer, ran the Apache and MySQL modules and browsed to http://localhost and then I clicked on phpmyadmin. I created a database and created a table.

This table was called schools and had a column called ID. I gave this column a primary index. The table also had another column called ‘Name’ which stored the name of the school.

I then created another table called ‘Users’ and in this table I made a column called schoolID which I gave an index. The table also had other columns such as first name and last name. I then went to the designer tab of the database in phpmyadmin and clicked create relationship. I then first clicked on the Primary key in the schools table and secondly clicked on the Index schoolID for the users table.

It then told me that the relationship was created successfully however I saw no green line connecting the two keys and when I went to insert into the table ‘users’… I was not given a dropdown list of the possible schools that could be connected from school ID’s and there was no sign that the two fields were connected. It is as if there is no relationship at all.

If I try to create the relationship again it simply gives me the error 1062 “Internal relationship could not be added – Duplicate entry “testdatabase-testdatabase.users-school’ for key ‘PRIMARY'”

I then went onto REMOTEMYSQL.COM and created a database following EXACTLY the same steps to find that it worked perfectly. Why is my XAMPP server not creating relationships and what am I doing wrong?

Tags:

2

Answers


  1. 0

    I made a column called schoolID which I gave an index ..

    I’m not sure what you mean by that. You want to create a list of schools that a user is assigned to / in a relationship with?

    Try setting up your tables as below:

    school table

    id = auto-increment // <-- your primary key
name = varchar(255)

id | name
----------
1 | Hogwarts
2 | Hard Knocks

    users table

    id = auto-increment // <-- primary key 
name = varchar(255)
school_id = int

id | name | school_id
----------
1 | John Doe | 1 
2 | Jane Doe | 1 
3 | Foo Bar | 2

    Fetch all users going to Hogwarts:

    SELECT * 
FROM users 
WHERE school_id = 1;

    Fetch all users going to the school of Hard Knocks!

    SELECT * 
FROM users 
WHERE school_id = 2;

    You can alter this, obviously, but hopefully it points you in the right direction. Try and use MySQL from the command line when you’re getting started. It’ll help you really focus on writing the queries, versus relying on phpmyadmin or anything.

    Login or Signup to reply.
  2. 0

    It has nothing to do with XAMPP, rather PMA.

    Client is important, but I believe latest PMA must be able to support integrity.

    Make sure your tables are created as InnoDB, not MyISAM. Also if you experienced other RDBMS before you may see some surprises. First of all I don’t think you can see drop down list in PMA.

    Another way to check integrity is to browse table with FK (users in your case). The field with FK must be shown as a link.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search