FOREIGN KEY, STRANGE BEHAVIOUR
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