HBsMcX5a

1Src smallint(5) unsigned NOT NULL,
2Dst smallint(5) unsigned NOT NULL,
3other fields
4	
5src    dst
61      354
7666    1
8	
9# variante
10Create TABLE `productsRelationships3` (
11  `relSrc` smallint(5) unsigned NOT NULL,
12  `relDst` smallint(5) unsigned NOT NULL,
13  PRIMARY KEY `src-dst-3` (relSrc, relDst),
14  UNIQUE  `src-3` (relSrc)
15) ENGINE=InnoDB DEFAULT CHARSET=utf8;
16
17# this is the import
18INSERT INTO productsRelationships3 SELECT relSrc, relDst FROM  productsRelationships WHERE relType=3;
19DELETE FROM productsRelationships WHERE relType=3;
20
21#this is the retrieval. The dummy rows are there because I do a UNION
22#SELECT relSrc, relDst, 3 as relType, relTypeDesc, 0 as fracQty, 28281 as source FROM productsRelationships3 LEFT JOIN productsRelationshipsDesc on 3=relTypeID WHERE relDst=28281 OR relSrc=28281;
23
24
25
26# fraccion
27#relType is from the old 1-table schema. It's going to be deleted
28Create TABLE `productsRelationships6` (
29  `relSrc` smallint(5) unsigned NOT NULL,
30  `relType` tinyint(2) unsigned NOT NULL DEFAULT 6,
31  `fracQty` int(2) unsigned NOT NULL,
32  `relDst` smallint(5) unsigned NOT NULL,
33  PRIMARY KEY `src-dst-6` (relSrc, relDst),
34  UNIQUE  `src-6` (relSrc),
35  UNIQUE  `dst-6` (relDst),
36  CONSTRAINT `fk_type_desc_6` FOREIGN KEY (`relType`) REFERENCES `productsrelationshipsdesc` (`relTypeID`) ON DELETE CASCADE
37) ENGINE=InnoDB DEFAULT CHARSET=utf8;
38
39#import
40INSERT INTO productsRelationships6 SELECT relSrc, relType, fracQty, relDst FROM  productsRelationships WHERE relType=6;
41	
42CREATE TABLE PointType
43( PointTypeID tinyint unsigned NOT NULL,
44  TypeDescription CHAR(20) NOT NULL,
45  PRIMARY KEY (PointTypeID),
46  UNIQUE (TypeDescription)
47) ;
48
49INSERT INTO PointType 
50  (PointTypeID, TypeDescription)
51VALUES
52  (1, 'Source'), 
53  (2, 'Destination') ;
54
55CREATE TABLE PointUsageQuota
56( RouteID int unsigned NOT NULL,
57  PointTypeID tinyint unsigned NOT NULL,
58  PointID smallint(5) unsigned NOT NULL,
59  PRIMARY KEY (PointID),     -- and this is what all the fuss is about
60  UNIQUE (RouteID , PointID, PointTypeID), -- target for the foreign keys
61  FOREIGN KEY (PointTypeID)
62    REFERENCES PointType (PointTypeID)
63) ;
64
65CREATE TABLE Route
66( RouteID int unsigned NOT NULL,
67  SourcePointID smallint(5) unsigned NOT NULL,
68  SourceTypeID tinyint unsigned NOT NULL, 
69      -- CHECK(SourceTypeID = 1),
70  FOREIGN KEY (RouteID , SourcePointID, SourceTypeID) 
71    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
72  DestinationPointID smallint(5) unsigned NOT NULL,
73  DestinationTypeID tinyint unsigned NOT NULL, 
74      -- CHECK(DestinationTypeID = 2),
75  FOREIGN KEY (RouteID , DestinationPointID, DestinationTypeID) 
76    REFERENCES PointUsageQuota (RouteID , PointID, PointTypeID) ,
77  --  other fields
78  PRIMARY KEY (RouteID)
79) ;
80	
81insert into PointUsageQuota
82values(1,1,666),(1,2,354);
83
84INSERT INTO Route VALUES (1, 666, 1, 354, 2);
85-- this fails:
86INSERT INTO Route VALUES (2, 666, 1, 354, 2);
87-- this fails too:
88INSERT INTO Route VALUES (2, 354, 1, 666, 2);
89	
90CREATE TABLE Route
91( RouteID int unsigned NOT NULL,
92  Src smallint(5) unsigned NOT NULL,
93  Dst smallint(5) unsigned NOT NULL,
94  -- other fields
95  PRIMARY KEY (RouteID)
96) ;
97	
98CREATE TABLE Route
99( RouteID int unsigned NOT NULL,
100  --- other fields
101  PRIMARY KEY (RouteID)
102) ;
103
104CREATE TABLE PointType
105( PointTypeID tinyint unsigned NOT NULL,
106  TypeDescription CHAR(20) NOT NULL,
107  PRIMARY KEY (PointTypeID),
108  UNIQUE (TypeDescription)
109) ;
110
111INSERT INTO PointType 
112  (PointTypeID, TypeDescription)
113VALUES
114  (1, 'Source'), 
115  (2, 'Destination') ;
116
117CREATE TABLE RoutePoint
118( RouteID int unsigned NOT NULL,
119  PointTypeID tinyint unsigned NOT NULL,
120  SrcDst smallint(5) unsigned NOT NULL,
121  PRIMARY KEY (RouteID, PointTypeID),
122  UNIQUE (SrcDst),                       -- and this is what all the fuss is about
123  FOREIGN KEY (RouteID)
124    REFERENCES Route (RouteID)
125      ON DELETE CASCADE ON UPDATE CASCADE,
126  FOREIGN KEY (PointTypeID)
127    REFERENCES PointType (PointTypeID)
128) ;
129	
130CREATE TRIGGER Route_bi BEFORE INSERT ON Route FOR EACH ROW
131  BEGIN
132      DECLARE err_msg VARCHAR(128) DEFAULT NULL; 
133
134      IF EXISTS(SELECT x.Dst FROM Route x WHERE x.Dst = NEW.Src) THEN
135          SET err_msg = CONCAT_WS('','cannot insert Src value ',NEW.Src,'; already exists as a Dst');
136          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
137      END IF;
138
139      IF EXISTS(SELECT x.Src FROM Route x WHERE x.Src = NEW.Dst) THEN
140          SET err_msg = CONCAT_WS('','cannot insert Dst value ',NEW.Dst,'; already exists as a Src');
141          SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = err_msg;
142      END IF;
143  END;
144	
145smaller SMALLINT NOT NULL,
146larger SMALLINT NOT NULL,
147UNIQUE(smaller), UNIQUE(larger)
148	
149INSERT ... (src, dst, smaller, larger)
150    VALUES ($src, $dst, LEAST($src, $dst), GREATEST($src, $dst)