Hi, I have two tables in a database and in both I have a column named nameID. Are they automaticaly related?
I have tried the designer view and going to create relation but that gives me an error.
They don’t have a relation until you link them. You will want to add a constraint.
Ok so I select a table and go to the ‘Structure’ tab. Under the table I go to ‘Relation view’. From there I try to add a constraint. Heres the issue when I try to link the nameID on one table to another using the Foreign key constraint (INNODB) section nameID doesn’t show as an option to select. Should I be using the Internal relation section instead? Does the nameID have to be the primary key in both tables?
I have tried following tuts (e.g. http://fellowtuts.com/php/setting-up-foreign-key-in-phpmyadmin/) on how to create relations but with no success. Its probably something simple that im leaving out.
I have one table called coursename which contains columns named CourseID (primary) and Course_Name.
The other table is called coursemodule that contains CourseID, ModuleID(primary) and Module_Name.
I will need the tables to be linked together as I need to allow the user to select a Course_Name using a dropdown box that prints out the course_modules related to that course_name.
I have been trying to link the courseID of each table together.
Is this concept correct?
Any guidance would be appreciated.
They s may sound stupid but, which is the parent table and which is the child?
The relations go into many to many, one to many, and many to one.
How do you see your tables relating? Can you have a course outside of a module? A module outside of a course?
The other way if the tables are not dependent on another is to leave it and just use joins for the data.
Here you go:
[php]SET FOREIGN_KEY_CHECKS=0;
– Table structure for coursemodule
DROP TABLE IF EXISTS coursemodule;
CREATE TABLE coursemodule (
ModuleID int(11) NOT NULL AUTO_INCREMENT,
CourseID int(11) DEFAULT NULL,
Module_Name varchar(255) DEFAULT NULL,
PRIMARY KEY (ModuleID),
KEY CourseID (CourseID),
CONSTRAINT coursemodule_ibfk_1 FOREIGN KEY (CourseID) REFERENCES coursename (CourseID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
– Table structure for coursename
DROP TABLE IF EXISTS coursename;
CREATE TABLE coursename (
CourseID int(11) NOT NULL AUTO_INCREMENT,
Course_Name varchar(255) DEFAULT NULL,
PRIMARY KEY (CourseID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/php]
They s may sound stupid but, which is the parent table and which is the child?
[member=72272]astonecipher[/member], the answer to your question is actually right there if you look at what he posted.
CourseID is the only repeated column and is the primary key in only one table, therefore the one with the primary key is the parent which is coursename.
The relation is one Course with many modules for that course.
[member=46186]Kevin Rubio[/member] Sorry I don’t know where I’m meant to insert that code.
There is only so much I can do for you. That is the DDL to create the database the way you want it.
Your right I can see you’ve went above and beyond in helping me so thanks.
I am inexperienced at using phpmyadmin so im not farmailor with all its features; I insertd the code into the SQL query on the database section of phpmyadmin and it created a link between the tables.
There you go. Thats what you were supposed to do. There is a bit more satisfaction when you can figure things out on your own. If you have the time, you should try to figure out what you were doing wrong before.
I have not used phpmyadmin for awhile. I would highly recommend you use Navicat. It is available as a free trial.
Another tip is MySQL Workbench, it’s free (the open source community edition) 