Skip to main content
deleted 22 characters in body
Source Link
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
);

insert into users(name, password) values ('bob', 'passwd');

CREATE TABLE `stock` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (stock_code)
);

insert into stock(stock_code) values('AABC'), ('BBCC'), ('EEFF');

-- a user can have 0 or more stocks, a stock can have 0 or more1 usersuser 
create table user_stock (
    user_id int(10) unsigned NOT NULL,
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
    PRIMARY KEY (user_id, stock_code),
    constraint fk1_user_stock foreign key (user_id)
        references `users` (id)
            on delete cascade
            on update cascade,
    constraint fk2_user_stock foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade
);

insert into user_stock(stock_code, value, user_id) 
values('AABC', 10, 1), ('BBCC', 4.5, 1), ('EEFF', 7, 1);

CREATE TABLE `sector_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE(name)
);

insert into sector_a(name) values('FOOD'), ('CAR'), ('BUILDING');

-- a stock can belong to 0 or more sectors, a sector can have 0 or more stocks
CREATE TABLE `stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `sector_id` int(10) unsigned not NULL,
    PRIMARY KEY (stock_code, sector_id),
    constraint fk1_stock_sector_a foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade,
    constraint fk2_stock_sector_a foreign key (sector_id)
        references sector_a (id)
            on delete cascade
            on update cascade
);

insert into stock_sector_a(stock_code, sector_id)
values('AABC', 1), ('AABC', 2), ('AABC', 3), ('BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from user_stock;
+---------+------------+-------+
| user_id | stock_code | value |
+---------+------------+-------+
|       1 | BBCC       |   4.5 |
|       1 | EEFF       |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from stock_sector_a;
+------------+-----------+
| stock_code | sector_id |
+------------+-----------+
| BBCC       |         2 |
| BBCC       |         3 |
+------------+-----------+
2 rows in set (0.00 sec)
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
);

insert into users(name, password) values ('bob', 'passwd');

CREATE TABLE `stock` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (stock_code)
);

insert into stock(stock_code) values('AABC'), ('BBCC'), ('EEFF');

-- a user can have 0 or more stocks, a stock can have 0 or more users 
create table user_stock (
    user_id int(10) unsigned NOT NULL,
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
    PRIMARY KEY (user_id, stock_code),
    constraint fk1_user_stock foreign key (user_id)
        references `users` (id)
            on delete cascade
            on update cascade,
    constraint fk2_user_stock foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade
);

insert into user_stock(stock_code, value, user_id) 
values('AABC', 10, 1), ('BBCC', 4.5, 1), ('EEFF', 7, 1);

CREATE TABLE `sector_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE(name)
);

insert into sector_a(name) values('FOOD'), ('CAR'), ('BUILDING');

-- a stock can belong to 0 or more sectors, a sector can have 0 or more stocks
CREATE TABLE `stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `sector_id` int(10) unsigned not NULL,
    PRIMARY KEY (stock_code, sector_id),
    constraint fk1_stock_sector_a foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade,
    constraint fk2_stock_sector_a foreign key (sector_id)
        references sector_a (id)
            on delete cascade
            on update cascade
);

insert into stock_sector_a(stock_code, sector_id)
values('AABC', 1), ('AABC', 2), ('AABC', 3), ('BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from user_stock;
+---------+------------+-------+
| user_id | stock_code | value |
+---------+------------+-------+
|       1 | BBCC       |   4.5 |
|       1 | EEFF       |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from stock_sector_a;
+------------+-----------+
| stock_code | sector_id |
+------------+-----------+
| BBCC       |         2 |
| BBCC       |         3 |
+------------+-----------+
2 rows in set (0.00 sec)
CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
);

insert into users(name, password) values ('bob', 'passwd');

CREATE TABLE `stock` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (stock_code)
);

insert into stock(stock_code) values('AABC'), ('BBCC'), ('EEFF');

-- a user can have 0 or more stocks, a stock have 1 user 
create table user_stock (
    user_id int(10) unsigned NOT NULL,
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
    PRIMARY KEY (stock_code),
    constraint fk1_user_stock foreign key (user_id)
        references `users` (id)
            on delete cascade
            on update cascade,
    constraint fk2_user_stock foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade
);

insert into user_stock(stock_code, value, user_id) 
values('AABC', 10, 1), ('BBCC', 4.5, 1), ('EEFF', 7, 1);

CREATE TABLE `sector_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE(name)
);

insert into sector_a(name) values('FOOD'), ('CAR'), ('BUILDING');

-- a stock can belong to 0 or more sectors, a sector can have 0 or more stocks
CREATE TABLE `stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `sector_id` int(10) unsigned not NULL,
    PRIMARY KEY (stock_code, sector_id),
    constraint fk1_stock_sector_a foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade,
    constraint fk2_stock_sector_a foreign key (sector_id)
        references sector_a (id)
            on delete cascade
            on update cascade
);

insert into stock_sector_a(stock_code, sector_id)
values('AABC', 1), ('AABC', 2), ('AABC', 3), ('BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from user_stock;
+---------+------------+-------+
| user_id | stock_code | value |
+---------+------------+-------+
|       1 | BBCC       |   4.5 |
|       1 | EEFF       |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from stock_sector_a;
+------------+-----------+
| stock_code | sector_id |
+------------+-----------+
| BBCC       |         2 |
| BBCC       |         3 |
+------------+-----------+
2 rows in set (0.00 sec)
added 707 characters in body
Source Link

Not sure I understand your scenarioI'm doing a fair amount of guessing here, if idbut this is primary key in tableA there can never be more than one idhow I imagine that your model should look like. In a couple of eachtables I removed id. Assuming columns to make it clearer, you may want to put them back in case the followingkeys I have chosen is not stable enough:

insertCREATE intoTABLE tableA`users` (id
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
 user_id `name` varchar(100),
 values `password` varchar(1255) NOT NULL,1
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
); 

insert into tableB users(idname, user_idpassword) values (1'bob',2 'passwd');

If you delete from tableA and want referential integrity you by necessity have to cascade the row from tableB, or prevent the delete.

If on the other hand (id, user_id) is primary key (or a unique constraint) in tableA you can create a foreign key against that:

ALTER
CREATE TABLE TABLEA`stock` ADD(
 CONSTRAINT AK1_TABLEA UNIQUE stock_code varchar(user_id100) COLLATE utf8_unicode_ci NOT NULL, 
 id   PRIMARY KEY (stock_code)
);
ALTER TABLE
insert TABLEBinto ADDstock(stock_code) CONSTRAINTvalues('AABC'), FK1_TABLEB('BBCC'), ('EEFF');

-- a user can FOREIGNhave KEY0 or more stocks, a stock can have 0 or more users 
create table user_stock (
    user_id int(10) unsigned NOT NULL, 
 id   stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
 REFERENCES TABLEA  PRIMARY KEY (user_id, idstock_code),
    constraint fk1_user_stock foreign key (user_id)
    ON DELETE CASCADE  references `users` (id)
            ONon UPDATEdelete CASCADE;cascade

Now the insert I did above is illegal since there is no (1,2) in tableA. However:

insert into tableA (id         on update cascade, 
 user_id) values  constraint fk2_user_stock foreign key (1,1stock_code);
insert into tableA (id, user_id) values   references stock (1,2stock_code);
insert into tableB (id, user_id) values (1,1      on delete cascade
            on update cascade
); 

insert into tableB user_stock(idstock_code, value, user_id)  
values (1'AABC',2 10, 1);

is valid, and if you delete:

delete, from('BBCC', tableA4.5, where1), id('EEFF', =7, 1 and);

CREATE user_idTABLE =`sector_a` 2(

the corresponding row in tableB will be cascade deleted. Example:

MariaDB [test]> create table ta`id` int(id10) intunsigned notNOT nullNULL AUTO_INCREMENT, 
 user_id int not null
 `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`), 
    constraintUNIQUE(name)
);

insert pk_tainto uniquesector_a(name) values(id'FOOD'), user_id)('CAR'), engine=innodb;('BUILDING');
Query
-- OK,a 0stock rowscan affectedbelong (to 0.01 sec)

MariaDBor [test]>more createsectors, tablea tbsector (idcan inthave not0 null,or user_idmore intstocks
CREATE notTABLE null`stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    ,`sector_id` int(10) unsigned not NULL,
 constraint fk1_tb foreign keyPRIMARY KEY (idstock_code, user_idsector_id) ,
      constraint fk1_stock_sector_a foreign key (stock_code)
        references tastock (id, user_idstock_code) 
            on delete cascade
       on delete cascade   on update cascade) engine=innodb;,
Query OK, 0 rows affectedconstraint (0.00fk2_stock_sector_a secforeign key (sector_id)
 
MariaDB [test]> insert into ta (id, user_id) values (1,1),references sector_a (1,2id);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings:on 0
delete cascade
MariaDB [test]>           on update cascade
);

insert into tbstock_sector_a(stock_code, sector_id)
values(id'AABC', user_id1), values('AABC', 2), (1'AABC',1 3), (1'BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 21 rowsrow affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb;user_stock;
+---------+------------+-------+
| iduser_id | user_idstock_code | value |
+---------+------------+-------+
|       1 | BBCC      1 |   4.5 |
|       1 | EEFF      2 |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> delete from ta where id = 1 and user_id = 2;
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from tb;stock_sector_a;
+------------+-----------+
| idstock_code | user_idsector_id |
+------------+-----------+
| BBCC 1      |       1  2 |
| BBCC       |         3 |
+------------+-----------+
12 rowrows in set (0.00 sec)

Not sure I understand your scenario, if id is primary key in tableA there can never be more than one id of each id. Assuming the following:

insert into tableA (id, user_id) values (1,1);
insert into tableB (id, user_id) values (1,2);

If you delete from tableA and want referential integrity you by necessity have to cascade the row from tableB, or prevent the delete.

If on the other hand (id, user_id) is primary key (or a unique constraint) in tableA you can create a foreign key against that:

ALTER TABLE TABLEA ADD CONSTRAINT AK1_TABLEA UNIQUE (user_id, id);
ALTER TABLE TABLEB ADD CONSTRAINT FK1_TABLEB 
    FOREIGN KEY (user_id, id)
        REFERENCES TABLEA (user_id, id)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

Now the insert I did above is illegal since there is no (1,2) in tableA. However:

insert into tableA (id, user_id) values (1,1);
insert into tableA (id, user_id) values (1,2);
insert into tableB (id, user_id) values (1,1);
insert into tableB (id, user_id) values (1,2);

is valid, and if you delete:

delete from tableA where id = 1 and user_id = 2

the corresponding row in tableB will be cascade deleted. Example:

MariaDB [test]> create table ta (id int not null, user_id int not null
             ,    constraint pk_ta unique (id, user_id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table tb (id int not null, user_id int not null
             ,    constraint fk1_tb foreign key (id, user_id) 
                  references ta (id, user_id) 
                     on delete cascade on update cascade) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> insert into ta (id, user_id) values (1,1),(1,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> insert into tb (id, user_id) values (1,1),(1,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb;
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
|  1 |       2 |
+----+---------+
2 rows in set (0.00 sec)

MariaDB [test]> delete from ta where id = 1 and user_id = 2;
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from tb;
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)

I'm doing a fair amount of guessing here, but this is how I imagine that your model should look like. In a couple of tables I removed id columns to make it clearer, you may want to put them back in case the keys I have chosen is not stable enough:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `name` varchar(100),
  `password` varchar(255) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE(`name`) -- questionable
); 

insert into users(name, password) values ('bob', 'passwd');

CREATE TABLE `stock` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL, 
    PRIMARY KEY (stock_code)
);

insert into stock(stock_code) values('AABC'), ('BBCC'), ('EEFF');

-- a user can have 0 or more stocks, a stock can have 0 or more users 
create table user_stock (
    user_id int(10) unsigned NOT NULL, 
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `value` double DEFAULT NULL,
    PRIMARY KEY (user_id, stock_code),
    constraint fk1_user_stock foreign key (user_id)
        references `users` (id)
            on delete cascade
            on update cascade, 
    constraint fk2_user_stock foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade
); 

insert into user_stock(stock_code, value, user_id)  
values('AABC', 10, 1), ('BBCC', 4.5, 1), ('EEFF', 7, 1);

CREATE TABLE `sector_a` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `name` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
    PRIMARY KEY (`id`), 
    UNIQUE(name)
);

insert into sector_a(name) values('FOOD'), ('CAR'), ('BUILDING');

-- a stock can belong to 0 or more sectors, a sector can have 0 or more stocks
CREATE TABLE `stock_sector_a` (
    stock_code varchar(100) COLLATE utf8_unicode_ci NOT NULL,
    `sector_id` int(10) unsigned not NULL,
    PRIMARY KEY (stock_code, sector_id),
    constraint fk1_stock_sector_a foreign key (stock_code)
        references stock (stock_code)
            on delete cascade
            on update cascade,
    constraint fk2_stock_sector_a foreign key (sector_id)
        references sector_a (id)
            on delete cascade
            on update cascade
);

insert into stock_sector_a(stock_code, sector_id)
values('AABC', 1), ('AABC', 2), ('AABC', 3), ('BBCC', 2), ('BBCC', 3);

MariaDB [test]> delete from stock where stock_code = 'AABC';
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from user_stock;
+---------+------------+-------+
| user_id | stock_code | value |
+---------+------------+-------+
|       1 | BBCC       |   4.5 |
|       1 | EEFF       |     7 |
+---------+------------+-------+
2 rows in set (0.00 sec)

MariaDB [test]> select * from stock_sector_a;
+------------+-----------+
| stock_code | sector_id |
+------------+-----------+
| BBCC       |         2 |
| BBCC       |         3 |
+------------+-----------+
2 rows in set (0.00 sec)
Source Link

Not sure I understand your scenario, if id is primary key in tableA there can never be more than one id of each id. Assuming the following:

insert into tableA (id, user_id) values (1,1);
insert into tableB (id, user_id) values (1,2);

If you delete from tableA and want referential integrity you by necessity have to cascade the row from tableB, or prevent the delete.

If on the other hand (id, user_id) is primary key (or a unique constraint) in tableA you can create a foreign key against that:

ALTER TABLE TABLEA ADD CONSTRAINT AK1_TABLEA UNIQUE (user_id, id);
ALTER TABLE TABLEB ADD CONSTRAINT FK1_TABLEB 
    FOREIGN KEY (user_id, id)
        REFERENCES TABLEA (user_id, id)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

Now the insert I did above is illegal since there is no (1,2) in tableA. However:

insert into tableA (id, user_id) values (1,1);
insert into tableA (id, user_id) values (1,2);
insert into tableB (id, user_id) values (1,1);
insert into tableB (id, user_id) values (1,2);

is valid, and if you delete:

delete from tableA where id = 1 and user_id = 2

the corresponding row in tableB will be cascade deleted. Example:

MariaDB [test]> create table ta (id int not null, user_id int not null
             ,    constraint pk_ta unique (id, user_id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> create table tb (id int not null, user_id int not null
             ,    constraint fk1_tb foreign key (id, user_id) 
                  references ta (id, user_id) 
                     on delete cascade on update cascade) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into ta (id, user_id) values (1,1),(1,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> insert into tb (id, user_id) values (1,1),(1,2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from tb;
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
|  1 |       2 |
+----+---------+
2 rows in set (0.00 sec)

MariaDB [test]> delete from ta where id = 1 and user_id = 2;
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from tb;
+----+---------+
| id | user_id |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)