'Django Rest Framework - Way to make specific values within two columns in parent table a constraint to be only used together in child table as fk?

not sure I worded my question right in the title, let me try and explain better below...

Let's say a parent table called Share has two columns, a ShareCode Charfield(primary key) and a ShareName unique Charfield (string). This means we'll have a unique ShareCode and ShareName paired on each row of this table. Code for this below.

class Share(models.Model):
    ShareCode = models.CharField(max_length=100, primary_key=True, default="N/A")
    ShareName = models.CharField(max_length=100, unique=True, default="N/A")

Now let's say we have a child table, Fund. This table will consist of a FundID Autofield(primary key), a FundCode Charfield, and a FundName CharField. It will also have two foreign keys that reference the Share table, ShareCode and Sharename. Finally, it will have two unique_together constraints, FundCode and ShareCode as the first constraint, and FundCode and ShareName as the second. Code below.

class Fund(models.Model):
    FundID = models.AutoField(primary_key=True)
    FundCode = models.CharField(max_length=100, default="N/A")
    FundName = models.CharField(max_length=100, default="N/A")
    ShareCode = models.ForeignKey(Share, to_field='ShareCode', related_name='fundsShareCode',
                                  on_delete=models.CASCADE)
    ShareName = models.ForeignKey(Share, to_field='ShareName', related_name='fundsShareName',
                                  on_delete=models.CASCADE)

    class Meta:  
        unique_together = ('FundCode', 'ShareCode'),
        unique_together = ('FundCode', 'ShareName')

Couple of things to note at this point, let's imagine that every share that we could possibly need is already in the Share table. The fund table's data however is being manually input by someone before being added to the database. The ShareName fk column has thus been added to the Fund table to make it easier to interpret, even if the ShareCode and FundCode columns would have sufficed for functionality by themselves.

Onto the problem now though. Let's say the person entering data into this table is adding multiple shares to a particular fund. So the FundCode 'FND123ABC' is spread across 3 rows, with ShareCode 'SHR1', 'SHR2', and SHR3' being the other half of the first unique_together constraint for those 3 rows.

They also need to satisfy the other constraint though. 'FND123ABC' is already provided for the second constraint, but the other half, ShareName, is being input by the user. The 3 shares that are being input are 'AbcCompany' (assigned to SHR1 in Share table), 'DefCompany' (assigned to SHR2 in Share table), and 'EfgCompany' (assigned to SHR3 in Share table). The user inputs in the Fund table for this second constraint the following (imagine that the input below is proper syntax):

Row 1...

FundCode: "FND123ABC",
ShareCode: "SHR1",
ShareName: "AbcCompany"

Row 2...

FundCode: "FND123ABC",
ShareCode: "SHR3"
ShareName: "DefCompany"

Row 3...

FundCode: "FND123ABC",
ShareCode: "SHR2"
ShareName: "EfgCompany"

Looking at those inputs above, Row 1 is fine. ShareCode 'SHR1' is matched with ShareName 'AbcCompany' in the Share table, so there's nothing wrong or inconsistent there. Rows 2 and 3 are a problem though. Row 2's ShareCode, 'SHR3' is assigned to ShareName 'DefCompany', whereas in the Share table, 'SHR3' is associated with the ShareName 'EfgCompany'. The final row has the same problem, with 'SHR2' being assigned to 'EfgCompany', when in the Share table 'SHR2' is associated with 'DefCompany' instead.

So with this informaton in mind, is there a way to prevent the user accidentally making a mistake like this? Removal of the ShareName field in the Fund table is one option, but I want to keep it there so the Fund table is much easier to read rather than a hard to read assortment of fund and share codes. So if we keep it, is there a way, a constraint or something, that can make sure that if ShareCode 'SH2' is being used for example, then it MUST be paired with its Share table associated field, 'DefCompany'?

Thank you for reading to the end, and I appreciate your assistance here.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source