added 497 characters in body; edited body
Source Link
ypercubeᵀᴹ
  • 94.1k
  • 13
  • 198
  • 295

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne INT AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo INT AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.


This is another idea (untested, not really sure if it will work), to have one less column and no persistent columns:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, diff INT UNSIGNED AS ( id2 - id1 ) VIRTUAL 
         --- a computed column that will hopefully throw an error if id1 > id2
, PRIMARY KEY (id1, id2)
, ...                                   --- other FOREIGN KEY constraints, etc 
) ;

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne INT AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo INT AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.


This is another idea (untested, not really sure if it will work), to have one less column and no persistent columns:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, diff INT UNSIGNED AS ( id2 - id1 ) VIRTUAL 
         --- a computed column that will hopefully throw an error if id1 > id2
, PRIMARY KEY (id1, id2)
, ...                                   --- other FOREIGN KEY constraints, etc 
) ;
added 88 characters in body; added 7 characters in body
Source Link
ypercubeᵀᴹ
  • 94.1k
  • 13
  • 198
  • 295

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), through the VIRTUAL columns feature, this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.

Source Link
ypercubeᵀᴹ
  • 94.1k
  • 13
  • 198
  • 295

CHECK constraints are not available in MySQL (eg. ignored at table definition). As @horse_with_no_name commented, neither are expression based indices.

Since such indices are availabe in MariaDB (a MySQL fork), this may be useful:

CREATE TABLE TableX
( id1 INT NOT NULL
, id2 INT NOT NULL
, mapTypeId INT NOT NULL
, idOne AS LEAST(id1, id2) PERSISTENT
          --- a computed column
, idTwo AS GREATEST(id1, id2) PERSISTENT
          --- and another one
, PRIMARY KEY (id1, id2)
, UNIQUE (idOne, idTwo)
, ...                                    --- other FOREIGN KEY constraints, etc 
) ;

And you can probably drop the (id1, id2) key and use the (idOne, idTwo) as primary.