REFERENCES keyword in SQLite3

Question

I was hoping someone could explain to me the purpose of the SQL keyword REFERENCES

CREATE TABLE wizards(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  age INTEGER
, color TEXT);

CREATE TABLE powers(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name STRING,
  damage INTEGER,
  wizard_id INTEGER REFERENCES wizards(id)
);

I've spent a lot of time trying to look this up and I initially thought that it would constrain the type of data you can enter into the powers table (based on whether the wizard_id ) However, I am still able to insert data into both columns without any constraint that I have noticed.

So, is the keyword REFERENCES just for increasing querying speed? What is its true purpose?

Thanks

Answer 1

It creates a Foreign Key to the other table. This can have performance benefits, but foreign keys are mostly about data integrity. It means that (in your case) the wizard_id filed of powers must have a value that exists in the id field of the wizards table. In other words, powers must refer to a valid wizard. Many databases also use this information to propagate deletions or other changes, so the tables stay in sync.

Just noticed this. A reason that you're able to bypass the key constraint may be that foreign keys aren't enabled. See Enabling foreign keys in the SQLite3 documentation.