eNAL5bHW

1-- test tables and data
2-- parent tables
3create table t_numbers ( number_ integer primary key ) ;
4create table t_letters ( letter_ varchar(1) primary key );
5
6
7drop procedure if exists populate_n_l;
8delimiter //
9
10create procedure populate_n_l()
11begin
12  declare v1 int default 1 ;
13
14  while v1 <= 26 do
15    insert into t_numbers ( number_ ) values ( v1 ) ;
16    insert into t_letters ( letter_ ) values ( char( v1 + 64 ) ) ;
17    set v1 := v1 + 1 ;
18  end while;
19
20end//
21
22delimiter ;
23
24call populate_n_l() ;
25	
26-- intersection
27create table t_intersect (
28  inumber integer
29, iletter varchar(1)
30-- constraints
31, constraint fkey_number 
32     foreign key( inumber ) references t_numbers( number_ )
33, constraint fkey_letter
34     foreign key( iletter ) references t_letters( letter_ )
35, constraint pkey_combination 
36     primary key ( inumber, iletter )
37) Engine=InnoDB;
38	
39-- okay (valid combination)
40  insert into t_intersect ( inumber, iletter )
41    values ( 1, 'A' ) ;
42
43-- does NOT work ( as expected! - number/letter combinations must be unique ... )
44  insert into t_intersect ( inumber, iletter )
45    values ( 1, 'A' ) ;
46-- ERROR 1062 (23000): Duplicate entry '1-A' for key 'PRIMARY'
47
48  insert into t_intersect ( inumber, iletter )
49    values ( 1, '1' ) ;
50-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...
51
52  insert into t_intersect ( inumber, iletter )
53    values ( 0, 'D' ) ;
54-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ...
55
56
57-- fine
58  insert into t_intersect ( inumber, iletter )
59    values ( 1, 'B' ) ;
60  insert into t_intersect ( inumber, iletter )
61    values ( 1, 'C' ) ;
62
63
64mysql> select * from t_intersect;
65+---------+---------+
66| inumber | iletter |
67+---------+---------+
68|       1 | A       |
69|       1 | B       |
70|       1 | C       |
71+---------+---------+