Validate that relationship must be between a primary key and foreign key

[npatki]

Version: SDV 1.0 (In development)

Problem Description

Currently, the metadata allows you add a relationship between two primary keys, as shown in #1232. This should not be allowed.

If you have a column that refers to another table, it must be a foreign key, not primary key.

Expected behavior

Running the code from #1232, we should throw an Error if you are specifying a relationship between 2 primary keys.

table = pd.DataFrame({
    'pk': list(range(100)),
    'col1': np.random.random(100),
    'col2': np.random.choice(['a', 'b', 'c'], 100)
})
data = {'table': table, 'table2': table}

metadata = MultiTableMetadata()
metadata.detect_table_from_dataframe('table', table)
metadata.set_primary_key('table', 'pk')

metadata.detect_table_from_dataframe('table2', table)
metadata.set_primary_key('table2', 'pk')

metadata.add_relationship('table', 'table2', 'pk', 'pk')

Output:

Invalid Metadata Error: A relationship must be specified between a primary key and non-primary key.

Additional context

The full specification is: A relationship must be between a primary key and a non-primary key.

We should also validate this this in the MultiTableMetadata.validate method.