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 keys. This is related both to table definition merge and for table data merge.

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 a table that depends on tables with max(DL) = N-1.

As we can see, using such DL definition we can't assign DL to the tables file and user because they depend on each other. Table useraddress also has no DL because it depends on tables with unassigned DL.

Now if we try to import table definitions to the blank database ordered by DL then by name then we'll be able to import tables country, city and address - that's better than if we try to import tables ordered just by name. And that's how table definition import merge works in the Object list tab. Tables without DL are processed last.

Table definitions merge for file, user and useraddress would require two steps: 1) import these tables without foreign keys and 2) create foreign keys separately. This scenario is currently not supported by the Object list and that's the subject of further improvements. However we can merge these tables one by one without foreign keys using Table structure diff tab and then merge remaining changes from Object list or Table structure diff.

DL can be useful for the data merge as well. Data with lower DL should be merged first, because higher DL can depend 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 deletion may fail if foreign key has no cascade action.

Last updated: 2023-10-06