SQL Server delete data with linked Table ?

[dongtrien]

Suppose you have 3 tables: TABHDBH, TABHDBHCHUYEN and TABHDBHCT
Table TABHDBH has the following fields:
NGAY DateTime;
IDHDBH int
...

Table IDHDBHCT has the following fields:
MAHDBH int
...

TABHDBHCHUYEN
NGAYCHUYEN datetime
MAHDBH int
...

RELATIONSHIP OF 3 TABLE
TABHDBHCHUYEN.MAHDBH head n
TABHDBH.IDHDBH head 1
TABHDBHCT.MAHDBH head n

DELETE T1
FROM TABHDBHCHUYEN T1
INNER JOIN TABHDBH T2 ON T1.MAHDBH = T2.IDHDBH INNER JOIN
           TABHDBHCT T3 ON T2.IDHDBH = T3.MAHDBH
WHERE (CONVERT(VARCHAR, NGAYCHUYEN, 101) <= '01/07/2017')

When running the query without error only delete the 1 record in TABHDBHCHUYEN and the records in the other 2 TABLE not delete record, you see help me wrong? Created a relational table for 3 tables to Delete Rule mode to Cascade but not delete.

[AndrisP]

Make foreign key constraints with clause on delete cascade on the tables 2 and 3 referenced to table 1 and dont use join for deleting.

[dongtrien]

In relationship. I got into the INSERT AND UPDATE Specification of the foreign key that set Delete Rule to Cascade but it still does not delete.

[Santanu.Das]

With reference from @AndrisP you will have to use the clause on delete cascade. But you have no need to use joining between three tables.
Simply delete record(s) from the table TABHDBHCHUYEN.

DELETE FROM TABHDBHCHUYEN WHERE (CONVERT(VARCHAR, NGAYCHUYEN, 101) <= '01/07/2017')

which can delete all records from TABHDBH & TABHDBHCT tables referrenced from TABHDBHCHUYEN table.

Because of the cascade delete when a record in the TABHDBHCHUYEN table is deleted, all records in TABHDBH & TABHDBHCT tables will also be deleted related with the specific foriegn key.

[AndrisP]

... and referenced column should be unique, in next example all working perfectly

drop table if exists `TABHDBHCHUYEN` cascade;
drop table if exists `TABHDBHCT` cascade;
drop table if exists `IDHDBHCT` cascade;

create table `IDHDBHCT` (
    `ID` int primary key not null auto_increment,
    `MAHDBH` int not null,
    constraint `UNIQUE_MAHDBH` unique key (`MAHDBH`)
);
create table `TABHDBHCHUYEN` (
    `ID` int primary key not null auto_increment,
    `NGAYCHUYEN` datetime,
    `MAHDBH` int not null,
    constraint `DBH_MAHDBH` foreign key (`MAHDBH`)
        references `IDHDBHCT`(`MAHDBH`) on delete cascade
);
create table `TABHDBHCT` (
    `ID` int primary key not null auto_increment,
    `MAHDBH` int not null,
    `IDHDBH` int not null,
    constraint `HCT_MAHDBH` foreign key (`MAHDBH`)
        references `IDHDBHCT`(`MAHDBH`) on delete cascade
);

insert into `IDHDBHCT`(`MAHDBH`)
    values (123),(234),(345);
insert into `TABHDBHCHUYEN`(`NGAYCHUYEN`,`MAHDBH`)
    values (now(),123)
    ,(date_add(now(), interval 1 day),234)
    ,(date_sub(now(), interval 1 day),345);
insert into `TABHDBHCT`(`IDHDBH`,`MAHDBH`)
    values (11,123),(22,234),(33,345);

check inserts

SELECT * FROM `IDHDBHCT`;
SELECT * FROM `TABHDBHCHUYEN`;
SELECT * FROM `TABHDBHCT`;

delete row 2 (affected all tables)

delete from `IDHDBHCT` where `ID` = 2;

check all tables

SELECT * FROM `IDHDBHCT`;
SELECT * FROM `TABHDBHCHUYEN`;
SELECT * FROM `TABHDBHCT`;

[dongtrien]

Hi AndrisP !
I delete records. you use the drop table command, I think unreasonable.

[AndrisP]

ignore drop - I use it only in my test base before create tables