0

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?

3
  • How do you call sqlite3? – Blauelf Nov 22 '18 at 8:52
  • I don't understand what you mean, so probably that's not a good sign:) I enter the sqlite command-line by command 'sqlite3', as per below. But not sure if this is what you mean. ~/workspace/finance/ $ sqlite3 SQLite version 3.8.2 2013-12-06 14:53:30 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> – JJuice Nov 22 '18 at 8:56
  • That's what I asked for. If you don't specify a file name on command line, it uses a temporary database in memory instead, existing until you exit the programme. You can change that with .load command in the programme, and also attach additional databases. – Blauelf Nov 22 '18 at 9:34
2

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. ;-)

2
  • Thanks! Indeed did not connect to the particular database, ATTACH command solved the issue. One question though: what is the function of the part that follows after ''finance.db' as'? Can you thereby give the database a name which thereafter you need to use when refering to that database (for instance when using the CREATE TABLE command)? – JJuice Nov 22 '18 at 9:32
  • 1
    You can use whatever name you want. If you for example said ATTACH finance.db AS money;, you would then refer to the users table as money.users. – Blauelf Nov 22 '18 at 9:36
1

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).

1
  • 1
    To say it another way, a "foreign key" is a key on a column in one table that is dependent on the values in a column in another table. In other words, for a particular value to be entered in column x of table y, that same value MUST ALREADY be in column a of table b. For example, you might create a foreign key that says for a table to store a particular userid, that userid must already exist in the USERS table. For more info, google it. – Cliff B Nov 22 '18 at 12:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .