...

View Full Version : Resolved Relational database foreign key



calebandchels
12-08-2011, 09:13 PM
I am new to trying to link databases together, so help me out if you can please.

I need to make the id in this table a foreign key to the primary key id in my other table called gig_user. I searched google and came up with my engine would have to be an innodb. Is the correct? Please advise and thanks in advance.


CREATE TABLE `gig_profile` (
`id` int(5) NOT NULL,
`band_logo` varchar(75) NOT NULL,
`genre` int(2) NOT NULL,
`bio` text NOT NULL,
`equipment` int(1) NOT NULL,
`link` varchar(200) NOT NULL,
`youtube` varchar(300) NOT NULL,
`reference` varchar(300) NOT NULL,
`email` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Old Pedant
12-08-2011, 09:34 PM
CREATE TABLE `gig_profile` (
`id` int(5) NOT NULL REFERENCES gig_user(id),
`band_logo` varchar(75) NOT NULL,
`genre` int(2) NOT NULL,
`bio` text NOT NULL,
`equipment` int(1) NOT NULL,
`link` varchar(200) NOT NULL,
`youtube` varchar(300) NOT NULL,
`reference` varchar(300) NOT NULL,
`email` varchar(100) NOT NULL
)


*********

The engine doesn't have to be INNODB, but MyISAM will not *ENFORCE* referential integrity. That is, if you put a number into the id field of this table that doesn't exist in the gig_user table, MyISAM won't care. INNODB will. If referential integrity is important to you, then yes, you should use INNODB.

And as for googling: Why? All fo this is clearly laid out in the basic MySQL docs.

http://dev.mysql.com/doc/refman/5.5/en/index.html

calebandchels
12-08-2011, 09:57 PM
And as for googling: Why? All fo this is clearly laid out in the basic MySQL docs.

http://dev.mysql.com/doc/refman/5.5/en/index.html

Because I am fairly new to this that site just seems to be overwhelming when compared to the explanations given on others.

Old Pedant
12-08-2011, 10:21 PM
FWIW, the only reason to use MyISAM with MySQL is if you have tons of data and you need the fastest possible access to it. If your DB is under, say, a couple of gigabytes total size, I would stick with INNODB. (I might stick with it even in bigger DBs, but then it becomes more a matter of weighing the tradeoffs. For smaller DBs, the performance difference isn't enough to matter, and INNODB's advantages make it by far the better choice.

Yeah, I know the MySQL docs can take some getting used to, but they are more complete than most docs for open source products and really do have all the info if you dig a bit. In the long run, if you can take a bit of time to read through them, you'll be better off. In the short run...I feel your pain.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum