Create a foreign key in SQLite database browser

Question

Sorry for novice question.

I have created my tables using SQLite database browser, but:

1. I do not know how can I specify my foreign keys using the application?

2. How can I create a relationship diagram between tables?

Answer 1

I know this question has been asked long ago but I found it. Its built right into GUI. You just need to drag and make those Name, Type tabs little bit small to make space for the Foreign Key tab. Place your mouse pointer at the end and drag the header.

My version of SQLite Browser is Version 3.7.0.

Answer 2

I couldn't find a way of defining foreign key constraints using the "Database Structure" tab. I'd strongly recommend defining table definitions and constraints using a script rather than building them using the graphical editor - it makes it much easier to create new databases and to track changes to the schema.

By way of an example, assume we have two tables: one defining file names and one specifying the method used for compression, we can add a foreign key constraint to the file_definition table when defining it.

CREATE TABLE [compression_state] (     [compression_state_id] INTEGER  PRIMARY KEY NOT NULL,     [value] TEXT  NOT NULL );  CREATE TABLE [file_definition] (     [file_id] INTEGER  NOT NULL  PRIMARY KEY AUTOINCREMENT,     [compression_state_id] INTEGER  NOT NULL,     [name] TEXT NOT NULL,     FOREIGN KEY(compression_state_id) REFERENCES compression_state(compression_state_id) ); 

However, by default, SQLite will not enforce the constraint, therefore every time you connect to the database, you must issue the following command to enable constraint checking.

PRAGMA foreign_keys = ON; 

Further details in the documentation.

If the tables already exist and you don't want to build a complete script then you're out of luck, SQLite doesn't support adding foreign keys once the table has been generated, see here: SQL Features That SQLite Does Not Implement