-1

In my design, I have many tables which use FKs. The issue is because certain records will be deleted and re-added at various points of time as they are linked to specific project files, the references will be always be inaccurate if I rely on the traditional auto-incrementing ID (because each time they are re-added they will be given a new ID).

I previously asked a question (Sqlite - composite PK with two auto-incrementing values) as to whether I can create a composite auto-incrementing ID however it appears to not be possible as answered by the question I was linked.

The only automatic value I can think of that'll always be unique and never repeated is a full date value, down to the second - however the idea of using a date for the tables' IDs feels like bad design. So, if I instead place a full date field in every table and use these as the FK reference, am I looking at any potential issues down the line? And am I correct in thinking it would be more efficient to store it as integer rather than a text value?

Thanks for the help

Update To clarify, I am not looking asking in regards to Primary Keys. The PK will be standard auto-incrementing ID. I am asking in regards to basing hundreds of FKs on dates.

Thank you for the replies below, the difficulty i'm having is I can't find a similar model to learn from. The end result is i'd like the application to use project files (like Word has their docx files) to import data into the database. Once a new project is loaded, the previous project's records are cleared but their data is preserved in the project file (the application's custom file format / a txt file) so they can be added once again. The FKs will all be project-based so they will only be referencing records that exist at the time in the database. For example, as it's a world-building application, let's say a user adds a subject type that would be relevant to any project (e.g. mathematics), due to the form it's entered on in the application, the record is given a_type number of 1, meaning it’s something that persists regardless of the project loaded. Another subject type however may be Demonology which only applies to the specific project loaded (e.g. a fantasy world). A school_subject junction table needs both of these in the same table to reference as the FK. So let’s say Demonology is the second record in the subjects type table, it has an auto-increment value of 2 - thus the junction table records 2 as it’s FK value. The issue is, before this project is re-opened again, the user may have added 10 more subject types that are universal and persist, thus next time the project’s subject type records and school_subject records are added back, Demonology is now given the ID of 11. However, the school_subject junction table is re-recreated with the same record having 2 as its value. This is why I’d like a FK which will always remain the same. I don’t want all projects to be present in the database, because I want users to be able to backup and duplicate individual projects as well know that even if the application is deleted, they can re-download and re-open their project files.

Game Analysis
  • 135
  • 9
  • What has your research shown? How is this not a duplicate? See [ask], other [help] links & the voting arrow mouseover texts. (Since you should be following a design method, where do you hit a problem in following it?) – philipxy Feb 11 '20 at 11:21
  • My reason for asking is because I couldn't find a question asking about the long-term implications of using dates as FKs across many tables (such as affecting speed). And i'd like to know this because unfortunately, I can't find an example of an SQL project which matches my design to follow. I need a project/document-based design where certain records are tied to projects and routinely swapped in and out of the database, thus affecting their IDs. However i also can't relegate them to their own tables, they need to be integrated amongst records that persist regardless of the project. – Game Analysis Feb 11 '20 at 11:32
  • Does this answer your question? [Pros & Cons of Date Column as Part of Primary Key](https://stackoverflow.com/questions/50043071/pros-cons-of-date-column-as-part-of-primary-key) – philipxy Feb 11 '20 at 11:58
  • 1
    Your comment doesn't address my comment. I just googled re dates as PKs & posted one link. There are many, many SO questions re generating unique ids, etc etc. PS Please clarify via edits, not comments. – philipxy Feb 11 '20 at 12:01

1 Answers1

0

This is a bit long for a comment.

Something seems wrong with your design. When you delete a row in a table, there should be no foreign key references to that key. The entity is gone. Does not exist (as far as the database is concerned). Under most circumstances, you will get an error if you try to delete a row in one table where another row refers to that row using a foreign key reference.

When you insert a row into a table, the database becomes aware of that entity. There should not be references to it.

Hence, you have an unusual situation. It sounds like you have primary keys that represent something in the real world -- such as a social security number or vehicle identification number. If that is the case, you might want this id to be the primary key of the table.

Another option is soft deletion. Once one of these rows is inserted in the table, it cannot be deleted. However, you can set a flag that says that it is deleted. Then, foreign key references can stay to the "soft" deleted row.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786