Creating the First Database¶
Mousing over each of the icons at the top of the DB Browser tool will show what the icon does.
To create a database, simply click on the icon for “New Database”:
Name the database “contributors” and save it anywhere you like (the desktop will work, or your documents folder). This single file will contain the entire database you create.
Note: As you do work in DB Browser, be aware that it will not automatically save your work. If you plan to step away from the tutorial, be sure to save your changes by clicking the “Write Changes” button:
You can also save SQL queries as individual scripts.
Creating a Table¶
Name the table “contributors” and begin creating columns as below by clicking the “Add field” button.
Note: You should name and order the fields AND fill in the drop-down menu and checkboxes exactly as displayed!
Important notes
Some important things to note:
- As you start adding fields and options, note how the table creation SQL in the bottom pane dynamically updates.
- The
id
field will be a unique identifier for each contributor (and therefore will be the “Primary Key” for the row), which is why we checked thePK
box for this one field. Checking theAI
box will make this integer automatically increment for each row we add (so each new row will have a new id). Finally, this field should not be null or empty (because we need it as the unique identifier), so we check theNot
box as well. - The next five columns will all contain text strings of undetermined
lengths (last names, for example, come in all kinds of lengths), so
we’ll use the
TEXT
datatype, which allows for text of varying length.
By the way, it may seem strange that the zip
column uses a TEXT
datatype, but remember that some zips start with a 0 (00501 is in NY).
So, we want to treat this column as a string of text, rather than as a
number (which would be 501).
Click OK and DB Browser will create the table based on your specifications, by executing the full SQL statement in the lower pane of the table creation window:
CREATE TABLE `contributors` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`last_name` TEXT,
`first_name` TEXT,
`city` TEXT,
`state` TEXT,
`zip` TEXT,
`amount` INTEGER
);
The syntax should be fairly clear, since it just reflects the choices we made in the form. It’s creating a table called “contributors” with the fields and data types we’ve defined.
You should now have a “contributors” table in the list in the Database Structure panel of the manager. Clicking the arrow beside the listing for “contributors” will show you the column list for the table.