vitess.io/vitess@v0.16.2/java/jdbc/src/test/resources/extractForeignKeyForTableTestCases.sql (about)

     1  -- The items in the below lists correspond to the following column names:
     2  -- PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, PKCOLUMN_NAME, FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME
     3  -- KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME, DEFERRABILITY
     4  
     5  -- Four of those columns are integers. Three other columns should always be null.
     6  -- The first group has 3 integers, i.e. `1, 3, 3`. This represents KEY_SEQ, UPDATE_RULE, and DELETE_RULE, in order.
     7  -- KEY_SEQ should increment for each column in a key, so 1 for the first, 2 for the second, etc, within a single constraint.
     8  -- UPDATE_RULE and DELETE_RULE correspond to one of:
     9  --   DatabaseMetaData.importedKeyCascade (0), importedKeyRestrict (1), importedKeySetNull (2), or importedKeyNoAction (3)
    10  -- The fourth integer is the last value in the list. This should be DatabaseMetaData.importedKeyNotDeferrable (7) for all cases.
    11  
    12  -- name: single fk constraint no update/delete references
    13  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 3, 3, fk_testA, null, 7]]
    14  CREATE TABLE `testA` (
    15    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    16    `fIdOne` bigint(20) unsigned NOT NULL,
    17    `fIdTwo` bigint(20) unsigned NOT NULL,
    18    PRIMARY KEY (`id`),
    19    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    20    CONSTRAINT `fk_testA` FOREIGN KEY (`fIdOne`) REFERENCES `fTable` (`id`)
    21  ) ENGINE=InnoDB
    22  
    23  -- name: single fk reference different schema/catalog
    24  -- expected: [[otherCatalog, null, fTable, id, test, null, testA, fIdOne, 1, 3, 3, fk_testA, null, 7]]
    25  CREATE TABLE `testA` (
    26    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    27    `fIdOne` bigint(20) unsigned NOT NULL,
    28    `fIdTwo` bigint(20) unsigned NOT NULL,
    29    PRIMARY KEY (`id`),
    30    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    31    CONSTRAINT `fk_testA` FOREIGN KEY (`fIdOne`) REFERENCES `otherCatalog`.`fTable` (`id`)
    32  ) ENGINE=InnoDB
    33  
    34  -- name: single fk constraint delete cascade
    35  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 3, 0, fk_testA, null, 7]]
    36  CREATE TABLE `testA` (
    37    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    38    `fIdOne` bigint(20) unsigned NOT NULL,
    39    `fIdTwo` bigint(20) unsigned NOT NULL,
    40    PRIMARY KEY (`id`),
    41    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    42    CONSTRAINT `fk_testA` FOREIGN KEY (`fIdOne`) REFERENCES `fTable` (`id`) ON DELETE CASCADE
    43  ) ENGINE=InnoDB
    44  
    45  -- name: single fk constraint on update restrict
    46  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 1, 3, fk_testA, null, 7]]
    47  CREATE TABLE `testA` (
    48    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    49    `fIdOne` bigint(20) unsigned NOT NULL,
    50    `fIdTwo` bigint(20) unsigned NOT NULL,
    51    PRIMARY KEY (`id`),
    52    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    53    CONSTRAINT `fk_testA` FOREIGN KEY (`fIdOne`) REFERENCES `fTable` (`id`) ON UPDATE RESTRICT
    54  ) ENGINE=InnoDB
    55  
    56  -- name: single fk constraint update and delete
    57  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 2, 0, fk_testA, null, 7]]
    58  CREATE TABLE `testA` (
    59    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    60    `fIdOne` bigint(20) unsigned NOT NULL,
    61    `fIdTwo` bigint(20) unsigned NOT NULL,
    62    PRIMARY KEY (`id`),
    63    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    64    CONSTRAINT `fk_testA` FOREIGN KEY (`fIdOne`) REFERENCES `fTable` (`id`) ON DELETE CASCADE ON UPDATE SET NULL
    65  ) ENGINE=InnoDB
    66  
    67  -- name: no constraint name
    68  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 3, 3, not_available, null, 7]]
    69  CREATE TABLE `testA` (
    70    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    71    `fIdOne` bigint(20) unsigned NOT NULL,
    72    `fIdTwo` bigint(20) unsigned NOT NULL,
    73    PRIMARY KEY (`id`),
    74    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    75    FOREIGN KEY (`fIdOne`) REFERENCES `fTable` (`id`)
    76  ) ENGINE=InnoDB
    77  
    78  -- name: multiple fk constraints delete cascade
    79  -- expected: [[test, null, fTableOne, id, test, null, testA, fIdOne, 1, 3, 0, fk_testA_fTableTwo, null, 7], [test, null, fTableTwo, id, test, null, testA, fIdTwo, 1, 3, 0, fk_testA_fTableOne, null, 7]]
    80  CREATE TABLE `testA` (
    81    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    82    `fIdOne` bigint(20) unsigned NOT NULL,
    83    `fIdTwo` bigint(20) unsigned NOT NULL,
    84    PRIMARY KEY (`id`),
    85    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    86    CONSTRAINT `fk_testA_fTableTwo` FOREIGN KEY (`fIdOne`) REFERENCES `fTableOne` (`id`) ON DELETE CASCADE
    87    CONSTRAINT `fk_testA_fTableOne` FOREIGN KEY (`fIdTwo`) REFERENCES `fTableTwo` (`id`) ON DELETE CASCADE
    88  ) ENGINE=InnoDB
    89  
    90  -- name: multiple fk constraints and multiple columns in keys delete cascade
    91  -- expected: [[test, null, fTableOne, id, test, null, testA, fIdOne, 1, 3, 0, fk_testA_fTableOne, null, 7], [test, null, fTableOne, other, test, null, testA, fIdTwo, 2, 3, 0, fk_testA_fTableOne, null, 7], [test, null, fTableTwo, id, test, null, testA, fIdThree, 1, 3, 0, fk_testA_fTableTwo, null, 7]]
    92  CREATE TABLE `testA` (
    93    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    94    `fIdOne` bigint(20) unsigned NOT NULL,
    95    `fIdTwo` bigint(20) unsigned NOT NULL,
    96    PRIMARY KEY (`id`),
    97    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
    98    CONSTRAINT `fk_testA_fTableOne` FOREIGN KEY (`fIdOne`, `fIdTwo`) REFERENCES `fTableOne` (`id`, `other`) ON DELETE CASCADE
    99    CONSTRAINT `fk_testA_fTableTwo` FOREIGN KEY (`fIdThree`) REFERENCES `fTableTwo` (`id`) ON DELETE CASCADE
   100  ) ENGINE=InnoDB
   101  
   102  -- name: single fk constraint, multiple keys
   103  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 3, 3, fk_testA, null, 7], [test, null, fTable, other, test, null, testA, fIdTwo, 2, 3, 3, fk_testA, null, 7]]
   104  CREATE TABLE `testA` (
   105    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   106    `fIdOne` bigint(20) unsigned NOT NULL,
   107    `fIdTwo` bigint(20) unsigned NOT NULL,
   108    PRIMARY KEY (`id`),
   109    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
   110    CONSTRAINT `fk_testA` FOREIGN KEY (`fIdOne`, `fIdTwo`) REFERENCES `fTable` (`id`, `other`)
   111  ) ENGINE=InnoDB
   112  
   113  -- name: with index name
   114  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 3, 3, fk_testA, null, 7]]
   115  CREATE TABLE `testA` (
   116    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   117    `fIdOne` bigint(20) unsigned NOT NULL,
   118    `fIdTwo` bigint(20) unsigned NOT NULL,
   119    PRIMARY KEY (`id`),
   120    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
   121    CONSTRAINT `fk_testA` FOREIGN KEY `idx_fk_testA` (`fIdOne`) REFERENCES `fTable` (`id`)
   122  ) ENGINE=InnoDB
   123  
   124  -- name: double quote constraint name
   125  -- expected: [[test, null, fTable, id, test, null, testA, fIdOne, 1, 3, 3, fk_testA, null, 7]]
   126  CREATE TABLE `testA` (
   127    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   128    `fIdOne` bigint(20) unsigned NOT NULL,
   129    `fIdTwo` bigint(20) unsigned NOT NULL,
   130    PRIMARY KEY (`id`),
   131    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
   132    CONSTRAINT "fk_testA" FOREIGN KEY (fIdOne) REFERENCES fTable (id)
   133  ) ENGINE=InnoDB
   134  
   135  -- name: no quotes on constraint name
   136  -- expected: []
   137  -- constraint is required to be quoted, so no match found
   138  CREATE TABLE `testA` (
   139    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   140    `fIdOne` bigint(20) unsigned NOT NULL,
   141    `fIdTwo` bigint(20) unsigned NOT NULL,
   142    PRIMARY KEY (`id`),
   143    UNIQUE KEY `unique_testA` (`fIdOne`,`fIdTwo`),
   144    CONSTRAINT fk_testA FOREIGN KEY (fIdOne) REFERENCES fTable (id)
   145  ) ENGINE=InnoDB