1

I have created a database to store data points in an ACID approach. One table holds the data points themselves and has a foreign key to the source of the data. There are multiple sources per geographic location, so I have another table for those locations. From the datasources I have a foreign key on those locations and everything is well.

Now my boss asked me to create a new column in the sensor data to also link to the location for easier debugging and performance reasons. But I still want the database to check that it won't be possible to insert a datapoint for a specific data source and a location where the source and the location don't match up.
It would be even nicer if the database would automatically generate the location ID based on the provided sourceid.

Data:

ID | SrcID | LocID | MoreColumns
---+-------+-------+------------
 1 |     1 |     1 |           X - OK
 2 |     1 |     2 |           X - Bad, DataSource is not at location 2
 3 |     4 |     4 |           X - Bad, Location 4 doesn't exist, but is caught by FK-Constraints

Src:

ID | LocID | MoreColumns
---+-------+------------
 1 |     1 |           X
 2 |     1 |           X
 3 |     2 |           X

Loc:

ID | MoreColumns
---+------------
 1 |           X
 2 |           X

How can I achieve data consistency here?
With FKs it checks if Loc and Src exist, but how can I make sure that the location that data points to is the same location that it's source points to?

FalcoGer
  • 2,278
  • 1
  • 12
  • 34
  • You can use more than one column in a constraint (it's debatable if this is an actual "Foreign Key"). Check out https://stackoverflow.com/questions/3178709/foreign-key-referencing-a-2-columns-primary-key-in-sql-server – Dave Brown Jul 09 '19 at 07:12
  • I implemented it as foreign key, for now. As far as I gathered a constraint only works on one table, so no joining to check of the new location id would match the location id in src. I was pondering views, since that's supposedly what they're there for, but my boss disagrees. – FalcoGer Jul 09 '19 at 07:26
  • @DaveBrown Are you suggesting I don't use two signle FKs to the src and loc tables, but instead use one compound FK to src? I have just tried that, and it gives me the error 'The columns of table "Src" don't match with the primary key or the unique constraint' (rough translation) – FalcoGer Jul 09 '19 at 07:29
  • Your SRC and Loc tables have PKs (one, int, ID) each. With hard foreign keys that doesn't give you the ability to say "i only want valid sources with valid locations" because a SrcID of 1 and a LocID of 2 both exist and are checked independantly. You need a further constraint to check from both a pure FK perspective and validity perspective. – Dave Brown Jul 09 '19 at 07:38
  • @DaveBrown yes, that's exactly the problem and the question. How do I do that? – FalcoGer Jul 09 '19 at 07:47
  • OK. Personally I'd make Loc (ID) a PK and only reference it from LOCID in SRC as an FK. Then I'd make both Src (ID) and SRC (LocID) a composite PK for Loc and then have a single FK of both columns from Data. Another option is to leave the PKS/FKs and add a Check Constraint between Data/Loc to ensure only valid combos exist. – Dave Brown Jul 09 '19 at 08:44
  • The problem with the Check Constraint is that you'd be able to add a combination multiple times in Loc, which is something I assume you want to avoid? If so then a Unique constraint will be needed (hence why I'd just make a composite PK on Loc). If this still doesn't make sense, let me know and I'll put more detail in as an answer. – Dave Brown Jul 09 '19 at 08:47
  • @DaveBrown I think I will go with the composite PK and unique index on Src and then have a composite FK on that PK. If you'd write an answer to that end, I'd be happy to accept it. – FalcoGer Jul 09 '19 at 09:52

1 Answers1

0

I solved the problem by making the primary key in Src a composite primary key (ID, LocID) and then having a foreign key from Data from (SrcID, LocID) to that primary key in Src (ID, LocID).
To make sure only unique IDs are given out for Src I created another unique index on ID in Src.
The foreign key from Src to Loc stays as it is.

FalcoGer
  • 2,278
  • 1
  • 12
  • 34