CODEWITHSUNDEEP
objective-csqlite
user2552751
user2552751

Reputation: 239

how to create foreign key in sqlite using sqlite manager

I have a table named Categories.

It has 4 columns: 

CategoryID - INT(primary Key),
CategoryName - NVARCHAR(32),
CategoryImage -  NVARCHAR(32),
LanguageCode - NVARCHAR(8)(foreign key).

It has one more table Languages.It has 2 columns: 

LanguageCode - NVARCHAR(8)(primary key),
LanguageName - NVARCHAR(16).

how can I create a foreign key for LanguageCode in sqlite using sqlite manager which can be downloaded from addons in firefox

Upvotes: 2

Views: 8165

Answers (1)

Mettek
Mettek

Reputation: 409

for do that you can use this example in the official 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!
);

For table track you can create foreignKey in this way:

CREATE TABLE track(
trackid     INTEGER, 
trackname   TEXT, 
trackartist INTEGER,
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);

SQLite Foreign Key Support

I also suggest you to use CoreData if you developing on MacOSX or iOS.

UPDATE:

In SQliteManager documentation you can find your answer:

1. Enabling Foreign Key Support

Foreign Keys can be enabled manually for each database session by going to the DB Settings tab and setting the Foreign Keys switch to ON.

To enable Foreign Key support automatically when a database is opened.

Go to the Tools drop down menu in the Menu bar and select Open On-connect SQL Tab. At the On-Connect SQL Tab enter the following line to the text box on the tab.

PRAGMA foreign_keys=ON;

2. Add a Foreign Key Constraint to an Existing Table

Ideally, any necessary Foreign Key constraint should have been included when a table was created.

That being said, it is possible at the present time, to use SQLite Manager to "shoehorn" a foreign key into a table where none exists by the following method.

Important - Before attempting to ALTER the structure of any table that contains valuable data,make a back up copy of that table first.

SQLite has only limited ALTER TABLE support which means that whenever SQLite Manager is making any changes to a table other than renaming it or adding a column, it is essentially dropping the table and recreating it. Also to successfully add a Foreign Key to an existing table that already contains data, the table must not contain any orphan records which will conflict with this constraint.

In the Columns text box, right click on the last column name listed to bring up the context menu and select Edit Column.

Note that if the last column in the TABLE definition is the PRIMARY KEY then it will be necessary to first add a new column and then edit the column type of the new column in order to add the FOREIGN KEY definition.

Within the Column Type box , append a comma and the FOREIGN KEY definition after data type. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.

Foreign Keys Support in SQLite Manager

Upvotes: 3

Related Questions