CREATE TABLE Continent ( CCODE VARCHAR(6) NOT NULL UNIQUE, CGROUP VARCHAR(255), TYPE VARCHAR(255), NAME VARCHAR(255), NUM_ID INT(4) NOT NULL UNIQUE, PRIMARY KEY (CCODE), TYPE = InnoDB);
CREATE TABLE Nations ( NAME VARCHAR(255) NOT NULL UNIQUE, TYPE VARCHAR(255), NCODE VARCHAR(6), CCODE VARCHAR(6), PRIMARY KEY (NAME), INDEX (CCODE), FOREIGN KEY (CCODE) REFERENCES Continent(CCODE) ON DELETE RESTRICT ON UPDATE CASCADE) TYPE = InnoDB; I beleive that should get you what you are looking for ( I too am still a beginner so there are probably many more efficent ways of doing it). I noticed that you CCODE in the Nations table wasn't the same size as in the Continent table. Fixed that. Good luck! Respectfully, Ligaya Turmelle "David Blomstrom" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > OK, I've made some changes, and I'm still trying to > figure out this foreign key stuff. This time, I'll > focus on just two tables, named "continents" and > "nations." > > I put screen shots of their structure and some sample > rows online at http://www.geoworld.org/try.gif > > It sounds like non-repeating numerals are generally > preferred for use as primary keys, but letters can be > used. I'd greatly prefer to use letters, because it's > much more easier for me to match them with their > respective countries and continents when working on > related tables. > > But if this experiment fizzles out, too, I may abandon > abbreviations and just use numerals for my primary > keys. > > At any rate, I want to join the continents and nations > tables via a shared column named "CCode." In the > continents table, it's designated a foreign key, and > none of the codes repeat. In the nations table, some > of the values do repeat - obviously, Canada and the > U.S. both merit the abbreviation na (North America). > > If I understand correctly, I have to designate a > column a unique index BEFORE I can make it a foreign > key. I tried designating the CCode field on the table > "nations" a unique index, without success. Is that > because of the repeating values? I then tried to > designate a column with non-repeating numerals a > unique index, but that didn't work, either. > > I finally learned how to create a foreign key with > phpMyAdmin, but that doesn't work, either. I put > screenshots of my trials online at > http://www.geoworld.org/fk.gif > > (The second table was then named nations2, but I've > since renamed it to nations.) > > If the only solution is to make all my primary keys > non-repeating numerals, then I'll go that route. If > there are other problems, then I'd like to know if > anyone on this list knows how to designate foreign > keys with either MySQL-Front or SQLyog. I've used them > as workarounds for other problems I have with > phpMyAdmin, but their Help sections are worthless for > foreign keys. > > Thanks. > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Movies - Buy advance tickets for 'Shrek 2' > http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
