Partial Transactions in MYSQL

[Ravi Tejas]

Dear All,
 
I have problem with partial transactions. I am using MYSQL.NET data provider(MySQL
Connector Net 1.0.2) for 1.1 .net framework. the database version is 5.02 . the table
structure is like this 
 

CREATE TABLE CLIENT_MAIN

(

IR_CLIENT_ID INT NOT NULL,

CLIENT_USERID VARCHAR(20) NOT NULL,

CLIENT_PASSWORD VARCHAR(20) NOT NULL, 

REGISTRATION_DATE DATE NULL,

REGISTRATION_EXPIRY_DATE DATE NULL,

COMPANY_ID VARCHAR(20) NOT NULL,

MANAGER_ID INT NULL,

PRIMARY KEY (IR_CLIENT_ID)

) TYPE = INNODB;

 

 

 

CREATE TABLE CLIENT_PERSONAL_DATA

(

PERSON_ID INT NOT NULL ,

IR_CLIENT_ID INT NOT NULL ,

PERSON_DATA_TYPE VARCHAR(10) NOT NULL,

TITLE VARCHAR(5) NULL,

LAST_NAME VARCHAR(50) NULL,

FIRST_NAME VARCHAR(50) NULL,

MIDDLE_INITIAL VARCHAR(1) NULL,

MARITAL_STATUS VARCHAR(10) NULL,

DATE_OF_BIRTH DATE NULL,

SEX VARCHAR(10) NULL,

EMAIL VARCHAR(50) NULL,

WORK_PHONE_NUMBER VARCHAR(15) NULL,

HOME_PHONE_NUMBER VARCHAR(15) NULL,

CELL_PHONE_NUMBER VARCHAR(15) NULL,

ADDRESS_1 VARCHAR(80) NULL,

ADDRESS_2 VARCHAR(80) NULL,

STATE VARCHAR(20) NULL,

ZIP VARCHAR(20) NULL,

CITY VARCHAR(20) NULL,

SOCIAL_SECURITY_NUMBER VARCHAR(12) NULL,

CLIENT_STATUS VARCHAR(20) NULL ,

DATE_CREATED DATE,

DATE_LAST_MODIFIED DATE,

PRIMARY KEY(PERSON_ID),

INDEX (IR_CLIENT_ID),

FOREIGN KEY (IR_CLIENT_ID) REFERENCES CLIENT_MAIN (IR_CLIENT_ID) 

)TYPE = INNODB;

 

 

i am using another table called 

CREATE TABLE PRIMARY_KEY_GENERATOR

(

TABLE_ID VARCHAR(80) NOT NULL,

LAST_NUMBER INT NOT NULL,

PRIMARY KEY(TABLE_ID)

) TYPE = INNODB; to strore primary keys in this table. 

 

the operation is insert. first i insert into the master table client_main and then insert
a row in client_personal_data. during insert i get an error 

cannot add or update . foreign key constraint error #23000. for primary key generation
commit is explicticly called in the code  where as for other tables it is one time commit
means the data inserts in two tables . 

can anybody resolve this . i am new to mysql . i have tested the same operation on
sqlserver 2000 and oracle 9.2 which works peacefully . 

 

 


		
---------------------------------
Do you Yahoo!?
 The all-new My Yahoo!