Sqlite REFERENCES keyword

Does anyone know what the REFERENCES keyword does in sqlite? For example if I had the followi开发者_运维百科ng statement: fr int REFERENCES contacts(id) what does that mean and how do I use it if I wanted to SELECT count(fr) from TABLE WHERE I know a contact's id?

In other words I want to create a SELECT statement that counts the number of fr where the contact id is equal to some number.

to answer the other part of the question, you would do:

select count(*) from tablename where fr = contactid;

since the "references" indicates that the fr field contains values from the id field in contacts.

some extra details: you have two tables: contacts and "tablename". in contacts you have details of various people, i guess, and each has an id. in "tablename" you have some more information, where each line in the table is "about" or "belongs to" someone in contacts. so each line in "tablename" has an entry in "fr" that identifies the person. it does that by containing a copy of the id value for that person (presumably each person has a different id). this is what the "references" is telling you - that "fr" contains a copy of "id".

so what you want to do is find the count of the things in "tablename" for each person. often that means joining the two tables, but in this case you don't have to because there's already a local copy of the person's id in "tablename". so all you have to do is find those rows in "tablename" that have the person's id, and count them. which means you just need to find those rows where "fr" is equal to the id (because you know that's a copy of id from contacts).

It creates a foreign key that points to id field in contacts table. I don't remember if it can be used after field like that, though. You can do e.g.

CREATE TABLE foo (
    id int,
    fr int,
    FOREIGN KEY(fr) REFERENCES bar(id)
);