Foreign Key constraint one to many tables while on delete ,on update cascade rule

Question

I'm working on a web application where i have database problem.

I have three tables : are as follows

Table 1:

CREATE TABLE mydb.emp(  
    eID INT NOT NULL,  
    eName VARCHAR(45) NULL,  
    PRIMARY KEY(eID)  
);

Table 2:

CREATE TABLE mydb.empLocation(  
    eLocateID INT NOT NULL,
    eArea VARCHAR(45) NULL,    
    eCity VARCHAR(45) NULL,  
    eZipcode VARCHAR(45) NULL,
    eID INT NULL,  
    PRIMARY KEY(eLocateID),
    CONSTRAINT eID
        FOREIGN KEY (eID)
        REFERENCES mydb.emp(eID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Table 3:

CREATE TABLE mydb.empLogin(  
    eLoginID INT NOT NULL,
    eTimeIn TIMESTAMP NULL,    
    eTimeOut TIMESTAMP NULL,  
    eID INT NULL,  
    PRIMARY KEY(eLoginID),
    CONSTRAINT eID
        FOREIGN KEY (eID)
        REFERENCES mydb.emp(eID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

While I am creating the table 3 there is a problem i can not insert the table because on delete and update cascade.

I want the cascade because when i delete a row in my emp table the data from the other tables should also be removed.

Please give me any solution for this problem or any alternate solution for this use case, Thanks in advance.

Are you sure that you don't need a many-to-many relationship between `emp` and `empLocation`? To me is seems like a location can exist regardless of possible employees..? Also, with your existing schema, why is eID allowed to be null in `empLocation` and `empLogin`? — user1429080, Jan 27 '17 at 12:35
Actually this one of the use case i was trying for many - to- many relationship. — Saikrishna, Jan 30 '17 at 10:52

score 1 · Accepted Answer · answered Jan 27 '17 at 11:49

Please try the following:

Table 1

CREATE TABLE emp(  
       eID INT NOT NULL,  
       eName VARCHAR(45) NULL,  
       PRIMARY KEY(eID)  
);

Table 2

CREATE TABLE empLocation(  
       eLocateID INT NOT NULL,
       eArea VARCHAR(45) NULL,    
       eCity VARCHAR(45) NULL,  
       eZipcode VARCHAR(45) NULL,
       eID INT NULL,  
       PRIMARY KEY(eLocateID),
       CONSTRAINT eID
           FOREIGN KEY (eID)
           REFERENCES emp(eID)
              ON DELETE CASCADE
              ON UPDATE CASCADE
);

Table 3

CREATE TABLE empLogin(  
       eLoginID INT NOT NULL,
       eTimeIn TIMESTAMP NULL,    
       eTimeOut TIMESTAMP NULL,  
       eID INT NULL,  
       PRIMARY KEY(eLoginID)
);

ALTER TABLE empLogin ADD FOREIGN KEY (eID) REFERENCES emp(eID) 
            ON DELETE CASCADE 
            ON UPDATE CASCADE;

Then,

INSERT INTO emp VALUES(1, 'John'), (2, 'Paul');
INSERT INTO empLogin VALUES
    (1, '2017-01-27 09:00:00', '2017-01-27 18:00:00', 1),
    (2, '2017-01-27 09:30:00', '2017-01-27 18:30:00', 2);

Seems to work fine this way.

Foreign Key constraint one to many tables while on delete ,on update cascade rule

1 Answers1