It sounds like you have a "One True Lookup Table" (OTLT) anti-pattern and you are mixing entities in this table. You've found why it isn't a good idea:
- can't have filtered foriegn keys
- can't FK to constants
- can't have multiple parents
Your sample code above is confusing (you have multiple parents for the same Code column) so I'll give you what I understand
CREATE TABLE OutlineFilesCostCentre (
CostCentreCode ...NOT NULL --PK
...
)
CREATE TABLE OutlineFilesLocations (
LocationCode ... NOT NULL --PK
...
)
CREATE TABLE Assets (
...
CostCentreCode ... NOT NULL,
LocationCode ... NOT NULL,
...
CONSTRAINT FK_Assets_CostCentre" FOREIGN KEY ("CostCentreCode")
REFERENCES "dbo"."OutlineFilesCostCentre" ("CostCentreCode"),
CONSTRAINT FK_Assets_Locations" FOREIGN KEY ("LocationCode")
REFERENCES "dbo"."OutlineFilesLocations" ("LocationCode")
...)
If you have 30 codes to lookup you will have 30 lookup tables: this is correct.
If you insist on OTLT you'll have to add extra columns to store type in Assets and FK them to your OTLT. I wouldn't do this.
...
CostCentreType char(2) NOT NULL,
CostCentreCode ... NOT NULL,
LocationType char(2) NOT NULL,
LocationCode ... NOT NULL,
...
Or use triggers to maintain the correct codes. I wouldn't do this either.
Neither SQL nor the relational model are disturbed by foreign keys that reference a natural key. In fact, referencing natural keys often dramatically improves performance. You'd be surprised how often the information you need is completely contained in a natural key; referencing that key trades a join for a wider table (and consequently reduces the number of rows you can store in one page).
By definition, the information you need is always completely contained in the natural key of every "lookup" table. (The term lookup table is informal. In the relational model, all tables are just tables. A table of US postal codes might have rows that look like this: {AK, Alaska}, {AL, Alabama}, {AZ, Arizona}, etc. Most people would call that a lookup table.)
On big systems, it's not unusual to find tables that have more than one candidate key. It's also not unusual for tables that serve one part of the enterprise to reference one candidate key, and tables that serve another part of the enterprise to reference a different candidate key. This is one of the strengths of the relational model, and it's a part of the relational model that SQL supports pretty well.
You'll run into two problems when you reference natural keys in tables that also have a surrogate key.
First, you'll surprise people. Although I usually lobby strongly for the Principle of Least Surprise, this is one situation where I don't mind surprising people. When the problem is that developers are surprised by the logical use of foreign keys, the solution is education, not redesign.
Second, ORMs aren't generally designed around the relational model, and they sometimes embody assumptions that don't reflect best practice. (In fact, they often seem to be designed without ever having input from a database professional.) Requiring an ID number in every table is one of those assumptions. Another one is assuming that the ORM application "owns" the database. (So it's free to create, drop, and rename tables and columns.)
I have worked on a database system that served data to hundreds of application programs written in at least two dozen languages over a period of 30 years. That database belongs to the enterprise, not to an ORM.
A fork that introduces breaking changes should be a show-stopper.
I measured performance with both natural keys and surrogate keys at a company I used to work at. There's a tipping point at which surrogate keys begin to outperform natural keys. (Assuming no additional effort to keep natural key performance high, like partitioning, partial indexes, function-based indexes, extra tablespaces, using solid-state disks, etc.) By my estimates for that company, they'll reach that tipping point in about 2045. In the meantime, they get better performance with natural keys.
Other relevant answers: In Database Schema Confusing
Best Answer
Assuming your surrogate keys are auto-incrementing integers marked as primary keys they will be the "rowid" for that table so you can read the last value added for a single insert using
SELECT last_insert_rowid(). This operates likescope_identityin other databases. You would need to read each one after each row insert as each will replace record of the previous one.sqlite doesn't seem to have an equivalent to the
OUTPUTclause found in SQL Server and theRETURNINGclause in Postgres & Oracle, so I don't know of a way to read multiple generated ID meaning that you'll have to do this one row at a time.