Pset 7 - create table (with foreign key) from command-line

Question

For pset7 - finance I am trying to create a new table to keep track of buy transactions. Since I want this table to have a foreign key (referencing the 'id' in the 'users' table) and I understood that this is not possible via the PhpLiteAdmin interface I want to create the table via the command-line.

This is an example line of code:

sqlite> CREATE TABLE portfolio (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, first TEXT NOT NULL, last TEXT NOT NULL);

And with schema I can see the table is created:

sqlite> .schema
CREATE TABLE portfolio (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, first TEXT NOT NULL, last TEXT NOT NULL);

However, the table does not appear in my finance.db file, so it is stored somewhere else.

Furthermore, when I try to do the following command, to specify that it should be a table within the database 'finance' it says that this database is unknown:

sqlite> CREATE TABLE finance.portfolio (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, first TEXT NOT NULL, last TEXT NOT NULL);
Error: unknown database finance

Does anybody have an idea where this is coming from and how to solve it?

Answer 1

I'll wager that you haven't connected to the database before executing the create table commamds, but that's just a guess on my part.

You may find some other commands useful:

.database will list the names of the databases that you have created.

To connect or disconnect to a particular db:

ATTACH DATABASE

DETACH DATABASE

I'll leave it to you to google the commands to get the exact usage. ;-)

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

Answer 2

You could .open finance.db, or start with sqlite3 finance.db to open that specific file. Without an open file, the tables created are all temporary.

I am not sure you got the term "foreign key" right. For example, I use a table

CREATE TABLE 'transactions' (
    'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    'user_id' INTEGER NOT NULL,
    [...],
    FOREIGN KEY('user_id') REFERENCES 'users'('id')
);

The id is a primary key for the transaction, so every transaction has one unique value there. It is in no way related to the field of the same name in another table. user_id is a field made reference field id in table users (not sure all of those apostrophes are necessary, but worked).