CODEWITHSUNDEEP
databasesqlite
John Doe
John Doe

Reputation: 125

SQLite, Many-to-Many Tables, relative references

Let's say I have a table eventsInHistory (sorry, I don't know a pretty way to make a table-like structure):

number | -date- | name

1----------------17760704--Declaration of Independence Signing

2----------------19551105--Doc Brown invents time travel

Now I have a second table, peopleInHistory:

number | name

1----------------Thomas Jefferson

2----------------Doc Brown

...and a third table, peopleAtEvents:

personNumber | eventNumber

1-------------------------------1

2-------------------------------2

Now let's say I learn of a new event where Doc Brown went back in time and met Thomas Jefferson. Is there a way in SQLite (and/or with DBrowser) to autoincrement to keep the events numbered in chronological order but also increment any links they may have; in other words, turn the eventsInHistory and peopleAtEvents tables automatically into the following:

number | -date- | name

1----------------17760704--Declaration of Independence Signing

2----------------17760706--Doc Brown meets Thomas Jefferson

3----------------19551105--Doc Brown invents time travel

personNumber | eventNumber

1-------------------------------1

1-------------------------------2

2-------------------------------2

2-------------------------------3

I'm fine if I need to add the new links to the table that relates the events and the people, but what I'm looking for is tying entries to positions rather than to numbers such that it preserves existing links when I go and add something above where I started.

Upvotes: 0

Views: 57

Answers (1)

Oliver Jakoubek
Oliver Jakoubek

Reputation: 451

As far as I have understood your problem - the answer is no. An autoincremented value is "gone" and you will not have the autoincremented values reordered - as long as you don't recreate the table with the new data.

Are you sure you really need that reordering? If the values are "meaningful" you can always query the tables and get the data in the way you like:

You want the eventsInHistory table in chronological order? Just query it:

SELECT * FROM eventsInHistory ORDER BY date;
SELECT * FROM peopleAtEvents ORDER BY personNumber, eventNumber;

If you would like to access the data in that way without having to enter that query every time then just create a view:

CREATE VIEW V_EventsOrderer AS
SELECT * FROM eventsInHistory ORDER BY date;

Upvotes: 1

Related Questions