Table DL

DL stands for Dependency Level, or foreign key dependency level. This number is introduced by KS DB Merge Tools applications to minimize merge failures caused by foreign key.

Let's consider an example of the following database. Column data types are skipped because they are not relevant here:

CREATE TABLE address (address_id PRIMARY KEY, country_id FOREIGN KEY REFERENCES country(country_id), city_id FOREIGN KEY REFERENCES city(city_id), addressline);
CREATE TABLE city (city_id PRIMARY KEY, country_id FOREIGN KEY REFERENCES country(country_id), name);
CREATE TABLE country (country_id PRIMARY KEY, name);
CREATE TABLE file (file_id PRIMARY KEY, path, author_user_id FOREIGN KEY REFERENCES user(user_id));
CREATE TABLE user (user_id PRIMARY KEY, email, photo_file_id FOREIGN KEY FERERENCES file(file_id));
CREATE TABLE useraddress (useraddress_id PRIMARY KEY, user_id FOREIGN KEY REFERENCES user(user_id), address_id FOREIGN KEY REFERENCES address(address_id), isprimary);

Such a database can't be created from scratch because of circular references, but it can be the current state of some database evolution process, foreign keys could be added one by one during application development. If we try to import such table definitions ordered by name into the blank database then the only table that can be imported is country because all other tables have references to tables which are not created yet. We consider table country as having DL = 0, it does not depend on any other table. DL = 1 is assigned to all tables that have dependencies on DL = 0 - that's city in our example. DL = 2 is assigned to tables depending on DL = 0 and DL = 1 - that's address which depends on country with DL = 0 and city with DL = 1. In general, DL = N is assigned to table that depends on tables with max(DL) = N-1.

Data with lower DL should be merged first, because higher DL can denend on the data which is missing in the target database and that can cause foreign key constraint violation errors. And when we delete records, data with higher DL should be processed first because lower DL delition may fail if foreign key has no cascade action.

Last updated: 2023-04-07