1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
|
set default_storage_engine=innodb;
create or replace table t (id int, x int, s date, e date, period for p(s,e),
unique(id, x, p without overlaps));
create or replace table s (id int, x int, s date, e date, period for fp(s,e),
foreign key(id, x, period fp)
references t(id, x, period p)
on delete restrict);
show create table s;
Table Create Table
s CREATE TABLE `s` (
`id` int(11) DEFAULT NULL,
`x` int(11) DEFAULT NULL,
`s` date NOT NULL,
`e` date NOT NULL,
PERIOD FOR `fp` (`s`, `e`),
KEY `id` (`id`,`x`,`s`,`e`),
CONSTRAINT `s_ibfk_1` FOREIGN KEY (`id`, `x`, `s`, `e`) REFERENCES `t` (`id`, `x`, `s`, `e`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
flush tables;
insert into t values(1, 1, '2017-01-03', '2017-01-20');
insert into t values(1, 1, '2017-01-20', '2017-01-25');
insert into t values(1, 1, '2017-01-25', '2017-01-26');
insert into t values(1, 1, '2017-01-26', '2017-01-30');
insert into s values(1, 1, '2017-01-03', '2017-01-20');
select * from t;
id x s e
1 1 2017-01-03 2017-01-20
1 1 2017-01-20 2017-01-25
1 1 2017-01-25 2017-01-26
1 1 2017-01-26 2017-01-30
select * from s;
id x s e
1 1 2017-01-03 2017-01-20
delete from t;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
select * from t;
id x s e
1 1 2017-01-03 2017-01-20
1 1 2017-01-20 2017-01-25
1 1 2017-01-25 2017-01-26
1 1 2017-01-26 2017-01-30
select * from s;
id x s e
1 1 2017-01-03 2017-01-20
delete from t where s = '2017-01-03' and e = '2017-01-20';
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
# no error
delete from t where s = '2017-01-20' and e = '2017-01-25';
insert into t values(1, 1, '2017-01-20', '2017-01-25');
insert into s values (1, 1, '2017-01-27', '2017-01-30');
delete from t where s = '2017-01-26' and e = '2017-01-30';
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
delete from t where s = '2017-01-25' and e = '2017-01-26';
insert into s values (1, 1, '2017-01-22', '2017-01-28');
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
insert into t values (1, 1, '2017-01-25', '2017-01-26');
select * from t;
id x s e
1 1 2017-01-03 2017-01-20
1 1 2017-01-20 2017-01-25
1 1 2017-01-25 2017-01-26
1 1 2017-01-26 2017-01-30
select * from s;
id x s e
1 1 2017-01-03 2017-01-20
1 1 2017-01-27 2017-01-30
insert into s values (1, 1, '2017-01-03', '2017-01-15');
insert into s values (1, 1, '2017-01-07', '2017-01-15');
insert into s values (1, 1, '2017-01-07', '2017-01-20');
insert into s values (1, 1, '2017-01-07', '2017-01-26');
insert into s values (1, 1, '2017-01-07', '2017-01-27');
insert into s values (1, 1, '2017-01-01', '2017-02-28');
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
insert into s values (1, 1, '2017-01-01', '2017-01-30');
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
select * from t;
id x s e
1 1 2017-01-03 2017-01-20
1 1 2017-01-20 2017-01-25
1 1 2017-01-25 2017-01-26
1 1 2017-01-26 2017-01-30
select * from s;
id x s e
1 1 2017-01-03 2017-01-15
1 1 2017-01-03 2017-01-20
1 1 2017-01-07 2017-01-15
1 1 2017-01-07 2017-01-20
1 1 2017-01-07 2017-01-26
1 1 2017-01-07 2017-01-27
1 1 2017-01-27 2017-01-30
update t set x= 2 where s='2017-01-03' and e='2017-01-20';
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
update s set x= 2 where s = '2017-01-03' and e = '2017-01-20';
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
update s set s= '2017-01-05' where s < '2017-01-05' and e > '2017-01-05';
update s set s= '2017-01-01' where s < '2017-01-26' and e > '2017-01-25';
ERROR 23000: Cannot add or update a child row: a foreign key constraint fails
# Free period ('2017-01-25', '2017-01-26') from references
update s set s= '2017-01-26', e= '2017-01-30' where s < '2017-01-26'
and e > '2017-01-25';
update t set x= 2 where s = '2017-01-25' and e = '2017-01-26';
update t set s= '2017-01-26', e= '2017-01-30' where s = '2017-01-25'
and e = '2017-01-26';
update s set x= 2 where s = '2017-01-26' and e = '2017-01-30';
update s set s= '2017-01-28', e = '2017-01-29' where x = 2;
select * from t;
id x s e
1 1 2017-01-03 2017-01-20
1 1 2017-01-20 2017-01-25
1 1 2017-01-26 2017-01-30
1 2 2017-01-26 2017-01-30
select * from s;
id x s e
1 1 2017-01-05 2017-01-15
1 1 2017-01-05 2017-01-20
1 1 2017-01-07 2017-01-15
1 1 2017-01-07 2017-01-20
1 1 2017-01-27 2017-01-30
1 2 2017-01-28 2017-01-29
1 2 2017-01-28 2017-01-29
update t set x= 2 where s='2017-01-03' and e='2017-01-20';
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
# Expand left
update t set s= '2017-01-01' where s = '2017-01-03' and e = '2017-01-20';
# Shrink left
update t set s= '2017-01-05' where e = '2017-01-20';
# Expand right
update t set e= '2017-02-10' where s = '2017-01-26' and e = '2017-01-30';
# Shrink right
update t set e= '2017-01-29' where s = '2017-01-26';
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
delete from s where s = '2017-01-27' and e = '2017-01-30';
update t set e= '2017-01-29' where s = '2017-01-26';
# Shrink both
update t set s= '2017-01-27', e= '2017-01-28' where x = 2;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
update t set s= '2017-01-28', e= '2017-01-29' where x = 2;
# Expand both
update t set s= '2017-01-20', e= '2017-02-05' where x = 2;
# Move right
update t set s= '2017-02-02', e= '2017-02-25' where x = 2;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
update t set s= '2017-01-28', e= '2017-02-25' where x = 2;
# Move left
update t set s= '2017-01-20', e= '2017-01-27' where x = 2;
ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails
update t set s= '2017-01-20', e= '2017-01-29' where x = 2;
create or replace table s (id int, x int, s date, e date, period for fp(s,e),
foreign key(id, x, period no_such_p)
references t(id, x, period p)
on delete restrict);
ERROR HY000: Period `no_such_p` is not found in table
drop database test;
create database test;
|
