Create a foreign key in SQLite database browser
SqliteSqlite Problem Overview
Sorry for novice question.
I have created my tables using SQLite database browser, but:
-
I do not know how can I specify my foreign keys using the application?
-
How can I create a relationship diagram between tables?
Sqlite Solutions
Solution 1 - Sqlite
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.
Solution 2 - Sqlite
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
Solution 3 - Sqlite
Go to edit table definition window
Click on Add field
Name it with Type : Integer
Scroll right and find Foreign Key column
Double click under Foreign Key column in new row
Select master table and its id field
Click OK
Click Write Changes
Solution 4 - Sqlite
From the SQLite Documentation :
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
);
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER -- Must map to an artist.artistid!
);
and in the end :
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
In the DB Browser for SQLite Environment (v 3.8.0 - Sqlite v 3.9.2) when you add the DB fields for the track
table along with
the PK ,AI and other columns you can find a Foreign Key Column.
In there , and for this example, you just add artist(artistid)
in the trackartist
row.
Then the foreign key constraint is created.
Solution 5 - Sqlite
In DB Browser, in the Edit Table Definition window, you can double click the blank area of Foreign Key and a text box will activate. You can add your Foreign Key there.
Solution 6 - Sqlite
it's really very easy , just do this Go to edit table definition window
Right click on the table that you want to relate ( foreign table )
choose modify table
on Constraints tab select add constraints button and choose foreign key you can relate tables here and then back to fields tab and do
Name it with Type : Integer
Scroll right and find Foreign Key column
Double click under Foreign Key column in new row
Select master table and its id field
Click OK
Click Write Changes
Solution 7 - Sqlite
Triggers in SQLite3 enforces foreign key constraints. Link https://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers would help you out to solve your first question.
Solution 8 - Sqlite
I am not sure whether this is entirely right but here's what I did:
- I added the variable "UserID" in the fields tab an checked the box "primary key"
- The I went to the constraints tab and added a foreign key Type constraint on the "UserID"
- Then I went back to fields tab and double clicked on the foreign key field that opened and added the name of the table where that key is and the name of the variable