ansaurus

Question

MySQL FK syntax: insert column called practice into table cred_insurances that is FK to table practices

Answer 1

+2 A:

You will need to ensure that your MySQL table is using the InnoDB engine, by running the following from the mysql prompt.

show create table cred_insurances

the output will include (towards the bottom) the text ENGINE=.... If it is not InnodDB, then you will first need to convert it using the following SQL. You may need to do this to the parent table as well.

ALTER TABLE cred_insurances ENGINE=InnoDB

Then you can add a column and a foreign key constraint with the following command:

ALTER TABLE cred_insurances
    ADD practice INT,
    ADD CONSTRAINT fk_practice
    FOREIGN KEY (practice) REFERENCES practices (ID)

If you are having difficulties with errors whilst adding a foreign key, try the following command to get more detailed information on the error.

SHOW ENGINE INNODB STATUS

ar 2010-10-22 17:20:40

@ar: I altered both tables to InnoDB ok, but I keep getting "ERROR 1005 (HY000): Can't create table './credentialing1/#sql-8e1_58.frm' (errno: 150)" when I run the command. Advice?

Captain Claptrap 2010-10-23 12:55:23

@ar: I was able to execute your command after changing back to MyISAM, but how to verify it took?

Captain Claptrap 2010-10-23 15:20:45

@ar: I was able to do what I need without any ISAM to InnoDB change. Why did you feel it necessary to add that complexity? There must be some compelling reason, no?

Captain Claptrap 2010-10-25 17:17:56

@Captain Claptrap, MyISAM doesn't currently support foreign key constraints. See here: http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html

ar 2010-10-26 09:53:23

@ar: I'm confused now. Your link (and another I found) seem to prove what your saying, yet I think it is working. Here is the output of mysql> show create table cred_insurances;

Captain Claptrap 2010-10-26 14:03:35

| Table | Create Table | cred_insurances | CREATE TABLE `cred_insurances` ( `ID` int(11) NOT NULL auto_increment, `ProviderName` tinytext, `Website(s)` text, `Notes` text, `practice` int(11) default NULL, PRIMARY KEY (`ID`), KEY `fk_practice` (`practice`)) ENGINE=MyISAM AUTO_INCREMENT=277 DEFAULT CHARSET=latin1 |1 row in set (0.00 sec)

Captain Claptrap 2010-10-26 14:04:18

Doesn't the line "KEY `fk_practice` (`practice`)" indicate the FK as desired? My application is working as if this is indeed a foreign key. Maybe I am misunderstanding FK's and constraints?

Captain Claptrap 2010-10-26 14:05:41

No, that line just tells you that there is a non-unique index (called fk_practice) on the practice column.

ar 2010-10-26 14:30:42

Obviously I have a lot to learn. When I made a new db and ran as you originally instructed I was able to do it as well as show the constraint in a show command (seeing it like that is very important to me). It showed ---CONSTRAINT `fk_practice` FOREIGN KEY (`practice`) REFERENCES `practices` (`ID`)--- which gives me peace of mind that it is now done right. I'm not sure why my first attempt failed with the 1005 error. Anyway, Thank you.

Captain Claptrap 2010-10-26 14:37:52

ansaurus

tags:

views:

answers:

MySQL FK syntax: insert column called practice into table cred_insurances that is FK to table practices

related questions