CODEWITHSUNDEEP
mysqlreferential-integrity
Sergey Chepurnov
Sergey Chepurnov

Reputation: 1447

MySQL, referential integrity doesn't work

I use MySQL Server 5.5; MySQL Workbench 5.2 CE; WinXp SP3; I created 2 tables:

CREATE  TABLE IF NOT EXISTS `mydb`.`Address` (
  `AddressID` INT NOT NULL AUTO_INCREMENT ,
  `Country` VARCHAR(45) NOT NULL ,
  `City` VARCHAR(45) NOT NULL ,
  `Region` VARCHAR(45) NOT NULL ,
  `PostalCode` VARCHAR(12) NOT NULL ,
  `Address1` VARCHAR(100) NOT NULL ,
  PRIMARY KEY (`AddressID`) )
ENGINE = InnoDB;


CREATE  TABLE IF NOT EXISTS `mydb`.`Customers` (
  `CustomerID` INT NOT NULL AUTO_INCREMENT ,
  `FirstName` VARCHAR(45) NOT NULL ,
  `LastName` VARCHAR(45) NOT NULL ,
  `Email` VARCHAR(500) NOT NULL ,
  `Password` VARCHAR(500) NOT NULL ,
  `RegistrationDate` TIMESTAMP NULL ,
  `CustomerCellPhone` VARCHAR(20) NULL ,
  `AddressID` INT NULL ,
  PRIMARY KEY (`CustomerID`) ,
  INDEX `AddressID_idx` (`AddressID` ASC) ,
  UNIQUE INDEX `Email_UNIQUE` (`Email` ASC) ,
  CONSTRAINT `CustomerAddressID`
    FOREIGN KEY (`AddressID` )
    REFERENCES `mydb`.`Address` (`AddressID` )
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Basically, the table ‘Customers’ contains foreign key constraint and it doesn't work. Theoretically, if I insert the data into two tables, I won’t delete ‘Address’ because ‘Customers’ depends on it:

insert into Address SET Country = 'Zimbabwe',
                        City = 'Harare',
                        Region = 'Mashonaland East Province',
                        PostalCode = '777',
                        Address1 = 'square 777 - 777';

insert into Customers SET FirstName = 'John',
                          LastName ='Doe',
                          Email = '[email protected]',
                          Password = '12345',
                          RegistrationDate = now(),
                          CustomerCellPhone = 123456789,
                          AddressID = 1;

'Customers'

CustomerID    FirstName   AddressID
1             John        1

'Address'

AddressID         Country          City   
1                 Zimbabwe         Harare

But, I can delete the address of my customer by:

DELETE FROM Address WHERE AddressID=1;

the table 'Customers' refer to nothing (dangling pointer)

CustomerID        FirstName       AddressID
1                 John            1

the empty table 'Address'

AddressID         Country         City    
---               ---             ---

The problem is ‘Customers’ refer to NULL into ‘Address’, because ‘Address’.AddressID = 1 doesn’t exist. It doesn't give me any errors. What I can do to repair this bug?

Upvotes: 1

Views: 633

Answers (1)

Sergey Chepurnov
Sergey Chepurnov

Reputation: 1447

Phew, restart the MySQL Server helps me.

Upvotes: 1

Related Questions