SQLite, how to entities associations without foreign keys?

I am trying to figure out the way of creating table without a foreign key under sqlite. I would like to avoid the use of foreign key due its incompatibility under some applications I am working now.

Could anyone please show a simple example with maybe two tables? Thank you.

Simple example: How do I select all tracks from one specific artist?

CREAT开发者_开发知识库E TABLE artist(
  artistid    INTEGER PRIMARY KEY, 
  artistname  TEXT
);

CREATE TABLE track(
  trackid     INTEGER, 
  trackname   TEXT, 
  trackartist INTEGER
);

---

You can have relationships between tables without declaring a foreign key. You simply do

SELECT a.*, t.* FROM artist a INNER JOIN track t ON a.artistid = t.trackartist;

(substituting the actual columns you want in place of a.*, t.*).

You can do this without declaring that trackartist is a foreign key to artist(artistid). If you do make that declaration, the difference is that SQLite will ensure that you never put a value in trackartist that is not a valid artistid and that you never change or remove an artistid that is used in trackartist.

Foreign keys are a mechanism to maintain the integrity of the association between tables but you can "create" any association you want in a SELECT statement independent of any primary or foreign keys declared in the database.

---

If you don't specify a foreign key, then there is no foreign key.

SELECT t.trackname, t.trackid
  FROM track t
 INNER JOIN artist a
    ON a.artistid = t.trackartist
 WHERE a.artistname = 'Alex'