Cannot create custom constraint with foreign key in multi-table about sdv

[longngng]

It is just a legacy thing in the dataset I am working with. Thanks for the suggestion!

[npatki]

Hi @longngng, unfortunately primary and foreign keys are not supported for custom constraints.

We have added this info to the custom constraints docs and FAQs.

From looking at your code, it seems like the foreign key that you have is a deterministic combination of both the letter and number columns. Since the letter and number columns can be completely recreated from the foreign key, I would consider dropping these columns even before you bring the data into the SDV. You can recreate these columns yourself after you have sampled synthetic data.

[longngng]

I see. What if the other columns in data_2 have some more information that I would like to keep? For example

data_1 = pd.DataFrame({
    'key': ['a', 'b', 'c'],
})

data_2 = pd.DataFrame({
    'foreign_key': ['a', 'b', 'c, c'],
    'letter': ['a_20231212', 'b_20231213', 'c_20231214', 'c_20231215'],
    'other': [7, 8, 9, 10]
})

In this case, I would like to enforce the PK-FK relationship, while using sdtype timestamp on the letter column after removing the foreign_key prefix. Do you have any suggestions for this?

[npatki]

In the example above, the 'letter' column seems to be of mixed-type (half foreign key, half timestamp). The easiest solution will be to extract out only the timestamp ('20231212', '20231213', ...). This would be a standard way to denote a timestamp. SDV can model both columns (foreign key and timestamp) if they are represented separately.

I would like to understand your project a bit better though. Why does the 'letter' column contain the foreign key's prefix? Is this standard practice in the datasets you are working with? Perhaps if I could better understand why the database is set up this way, it will help us come up with a solution.

[npatki]

No problem @longngng. Happy to help.

I'm closing this issue off as out-of-scope since constraints were not designed to be working with foreign keys. But if we ever come up with a compelling use case for this -- one that cannot be done by some pre-processing -- then we can always open a new feature request to track.