MySQL Forums
Forum List  »  InnoDB

FOREIGN KEY, STRANGE BEHAVIOUR
Posted by: davide luppi
Date: October 03, 2008 02:21AM

Hy all


MysqlServer version:5.1.28-rc.

I develop a .net(c#) application that create a Db with 3 table
This is the sql used to create DB:

theCreateDatabaseString =
"SET FOREIGN_KEY_CHECKS = 0;" +
"CREATE DATABASE IF NOT EXISTS `{0}` " +
"DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;" +
"USE `{0}`;" +
"DROP TABLE IF EXISTS `anagrafica`;" +
"DROP TABLE IF EXISTS `costi_fissi`;" +
"DROP TABLE IF EXISTS `costi_variabili`;" +
"CREATE TABLE `anagrafica` ( " +
" `ANA_ID` int(11) NOT NULL AUTO_INCREMENT," +
" `COD_CLIENTE` varchar(20) NOT NULL," +
" `NUMERODITELEFONO` varchar(15) NOT NULL," +
" `ANNO` varchar(4) NOT NULL," +
" `DIREZIONE` varchar(100) DEFAULT NULL," +
" `SETTORE` varchar(100) DEFAULT NULL," +
" PRIMARY KEY (`ANA_ID`)," +
" KEY `IDX_DIREZIONE` (`NUMERODITELEFONO`,`ANNO`,`DIREZIONE`)," +
" KEY `IDX_NUMTEL` (`NUMERODITELEFONO`)" +
") ENGINE=InnoDB DEFAULT CHARSET=latin1;" +
"" +
"CREATE TABLE `costi_fissi` (" +
" `CFS_ID` int(11) NOT NULL AUTO_INCREMENT," +
" `ANA` int(11) NOT NULL," +
" PRIMARY KEY (`CFS_ID`)" +
") ENGINE=InnoDB DEFAULT CHARSET=latin1;" +
""+
"CREATE TABLE `costi_variabili` (" +
" `CVS_ID` int(11) NOT NULL AUTO_INCREMENT," +
" `ANA` int(11) NOT NULL," +
" PRIMARY KEY (`CVS_ID`) " +
") ENGINE=InnoDB DEFAULT CHARSET=latin1;";


First i create DB using this c# code:

theMySqlCommand = new MySqlCommand(theCreateDatabaseString, theMySqlConnection);
theMySqlCommand.CommandType = CommandType.Text;
theMySqlCommand.ExecuteNonQuery();

Then when i try to add some foreign key i have same problem (errno 121, errno 150) already describe in forum.

I have solved executing this sql.

strCreateConstraint = @"ALTER TABLE `costi_fissi` ADD FOREIGN KEY (`ANA`) REFERENCES anagrafica (`ANA_ID`) ON UPDATE RESTRICT ON DELETE RESTRICT; ALTER TABLE `costi_variabili_sim` ADD FOREIGN KEY (`ANA`) REFERENCES `anagrafica` (`ANA_ID`) ON UPDATE RESTRICT ON DELETE RESTRICT;";

c# code used:
theMySqlCommand = new MySqlCommand(strCreateConstraint, theMySqlConnection);
theMySqlCommand.CommandType = CommandType.Text;
theMySqlCommand.ExecuteNonQuery();

THE STRANGE BEHAVIOR IS:

TABLE 'costi_fissi' have two equals definition of FOREIGN KEY : costi_fissi_ibfk_1 and costi_fissi_ibfk_2 both reference field 'ANA' of table anagrafica.
TABLE 'costi_variabili' have correctly only one definition of foreign key.

HOW i fix this problem?

thank's
Davide

Options: ReplyQuote


Subject
Views
Written By
Posted
FOREIGN KEY, STRANGE BEHAVIOUR
2925
October 03, 2008 02:21AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.