github.com/quay/claircore@v1.5.28/datastore/postgres/migrations/indexer/04-foreign-key-cascades.sql (about)

     1  -- Add CASADEs on all foreign-key relations.
     2  -- This makes deleting manifests and layers much simpler.
     3  ALTER TABLE indexreport
     4  	DROP CONSTRAINT indexreport_manifest_id_fkey;
     5  ALTER TABLE indexreport
     6  	ADD CONSTRAINT indexreport_manifest_id_fkey
     7  	FOREIGN KEY (manifest_id)
     8  	REFERENCES manifest(id)
     9  	ON DELETE CASCADE;
    10  
    11  ALTER TABLE dist_scanartifact
    12  	DROP CONSTRAINT dist_scanartifact_dist_id_fkey;
    13  ALTER TABLE dist_scanartifact
    14  	ADD CONSTRAINT dist_scanartifact_dist_id_fkey
    15  	FOREIGN KEY (dist_id)
    16  	REFERENCES dist(id)
    17  	ON DELETE CASCADE;
    18  ALTER TABLE dist_scanartifact
    19  	DROP CONSTRAINT dist_scanartifact_layer_id_fkey;
    20  ALTER TABLE dist_scanartifact
    21  	ADD CONSTRAINT dist_scanartifact_layer_id_fkey
    22  	FOREIGN KEY (layer_id)
    23  	REFERENCES layer(id)
    24  	ON DELETE CASCADE;
    25  ALTER TABLE dist_scanartifact
    26  	DROP CONSTRAINT dist_scanartifact_scanner_id_fkey;
    27  ALTER TABLE dist_scanartifact
    28  	ADD CONSTRAINT dist_scanartifact_scanner_id_fkey
    29  	FOREIGN KEY (scanner_id)
    30  	REFERENCES scanner(id)
    31  	ON DELETE CASCADE;
    32  
    33  ALTER TABLE manifest_index
    34  	DROP CONSTRAINT manifest_index_dist_id_fkey;
    35  ALTER TABLE manifest_index
    36  	ADD CONSTRAINT manifest_index_dist_id_fkey
    37  	FOREIGN KEY (dist_id)
    38  	REFERENCES dist(id)
    39  	ON DELETE CASCADE;
    40  ALTER TABLE manifest_index
    41  	DROP CONSTRAINT manifest_index_manifest_id_fkey;
    42  ALTER TABLE manifest_index
    43  	ADD CONSTRAINT manifest_index_manifest_id_fkey
    44  	FOREIGN KEY (manifest_id)
    45  	REFERENCES manifest(id)
    46  	ON DELETE CASCADE;
    47  ALTER TABLE manifest_index
    48  	DROP CONSTRAINT manifest_index_package_id_fkey;
    49  ALTER TABLE manifest_index
    50  	ADD CONSTRAINT manifest_index_package_id_fkey
    51  	FOREIGN KEY (package_id)
    52  	REFERENCES package(id)
    53  	ON DELETE CASCADE;
    54  ALTER TABLE manifest_index
    55  	DROP CONSTRAINT manifest_index_repo_id_fkey;
    56  ALTER TABLE manifest_index
    57  	ADD CONSTRAINT manifest_index_repo_id_fkey
    58  	FOREIGN KEY (repo_id)
    59  	REFERENCES repo(id)
    60  	ON DELETE CASCADE;
    61  
    62  ALTER TABLE manifest_layer
    63  	DROP CONSTRAINT manifest_layer_layer_id_fkey;
    64  ALTER TABLE manifest_layer
    65  	ADD CONSTRAINT manifest_layer_layer_id_fkey
    66  	FOREIGN KEY (layer_id)
    67  	REFERENCES layer(id)
    68  	ON DELETE CASCADE;
    69  ALTER TABLE manifest_layer
    70  	DROP CONSTRAINT manifest_layer_manifest_id_fkey;
    71  ALTER TABLE manifest_layer
    72  	ADD CONSTRAINT manifest_layer_manifest_id_fkey
    73  	FOREIGN KEY (manifest_id)
    74  	REFERENCES manifest(id)
    75  	ON DELETE CASCADE;
    76  
    77  ALTER TABLE package_scanartifact
    78  	DROP CONSTRAINT package_scanartifact_layer_id_fkey;
    79  ALTER TABLE package_scanartifact
    80  	ADD CONSTRAINT package_scanartifact_layer_id_fkey
    81  	FOREIGN KEY (layer_id)
    82  	REFERENCES layer(id)
    83  	ON DELETE CASCADE;
    84  ALTER TABLE package_scanartifact
    85  	DROP CONSTRAINT package_scanartifact_package_id_fkey;
    86  ALTER TABLE package_scanartifact
    87  	ADD CONSTRAINT package_scanartifact_package_id_fkey
    88  	FOREIGN KEY (package_id)
    89  	REFERENCES package(id)
    90  	ON DELETE CASCADE;
    91  ALTER TABLE package_scanartifact
    92  	DROP CONSTRAINT package_scanartifact_scanner_id_fkey;
    93  ALTER TABLE package_scanartifact
    94  	ADD CONSTRAINT package_scanartifact_scanner_id_fkey
    95  	FOREIGN KEY (scanner_id)
    96  	REFERENCES scanner(id)
    97  	ON DELETE CASCADE;
    98  ALTER TABLE package_scanartifact
    99  	DROP CONSTRAINT package_scanartifact_source_id_fkey;
   100  ALTER TABLE package_scanartifact
   101  	ADD CONSTRAINT package_scanartifact_source_id_fkey
   102  	FOREIGN KEY (package_id)
   103  	REFERENCES package(id)
   104  	ON DELETE CASCADE;
   105  
   106  ALTER TABLE repo_scanartifact
   107  	DROP CONSTRAINT repo_scanartifact_layer_id_fkey;
   108  ALTER TABLE repo_scanartifact
   109  	ADD CONSTRAINT repo_scanartifact_layer_id_fkey
   110  	FOREIGN KEY (layer_id)
   111  	REFERENCES layer(id)
   112  	ON DELETE CASCADE;
   113  ALTER TABLE repo_scanartifact
   114  	DROP CONSTRAINT repo_scanartifact_repo_id_fkey;
   115  ALTER TABLE repo_scanartifact
   116  	ADD CONSTRAINT repo_scanartifact_repo_id_fkey
   117  	FOREIGN KEY (repo_id)
   118  	REFERENCES repo(id)
   119  	ON DELETE CASCADE;
   120  ALTER TABLE repo_scanartifact
   121  	DROP CONSTRAINT repo_scanartifact_scanner_id_fkey;
   122  ALTER TABLE repo_scanartifact
   123  	ADD CONSTRAINT repo_scanartifact_scanner_id_fkey
   124  	FOREIGN KEY (scanner_id)
   125  	REFERENCES scanner(id)
   126  	ON DELETE CASCADE;
   127  
   128  ALTER TABLE scanned_layer
   129  	DROP CONSTRAINT scanned_layer_layer_id_fkey;
   130  ALTER TABLE scanned_layer
   131  	ADD CONSTRAINT scanned_layer_layer_id_fkey
   132  	FOREIGN KEY (layer_id)
   133  	REFERENCES layer(id)
   134  	ON DELETE CASCADE;
   135  ALTER TABLE scanned_layer
   136  	DROP CONSTRAINT scanned_layer_scanner_id_fkey;
   137  ALTER TABLE scanned_layer
   138  	ADD CONSTRAINT scanned_layer_scanner_id_fkey
   139  	FOREIGN KEY (scanner_id)
   140  	REFERENCES scanner(id)
   141  	ON DELETE CASCADE;
   142  
   143  ALTER TABLE scanned_manifest
   144  	DROP CONSTRAINT scanned_manifest_manifest_id_fkey;
   145  ALTER TABLE scanned_manifest
   146  	ADD CONSTRAINT scanned_manifest_manifest_id_fkey
   147  	FOREIGN KEY (manifest_id)
   148  	REFERENCES manifest(id)
   149  	ON DELETE CASCADE;
   150  ALTER TABLE scanned_manifest
   151  	DROP CONSTRAINT scanned_manifest_scanner_id_fkey;
   152  ALTER TABLE scanned_manifest
   153  	ADD CONSTRAINT scanned_manifest_scanner_id_fkey
   154  	FOREIGN KEY (scanner_id)
   155  	REFERENCES scanner(id)
   156  	ON DELETE CASCADE;
   157  
   158  ALTER TABLE scannerlist
   159  	DROP CONSTRAINT scannerlist_scanner_id_fkey;
   160  ALTER TABLE scannerlist
   161  	ADD CONSTRAINT scannerlist_scanner_id_fkey
   162  	FOREIGN KEY (scanner_id)
   163  	REFERENCES scanner(id)
   164  	ON DELETE CASCADE;
   165