Join Bill Weinman for an in-depth discussion in this video Creating an ID column, part of SQL Essential Training (2014).
ID columns are columns that hold a unique value for each row in a table. Typically, ID columns are automatically populated. The method for creating an ID column, is not standardized. How you create an ID column is different for every database system. I'm going to show you how this works in SQLite, the database system we're using here. Keep in mind that this will be different for other database systems. I'm showing you this just so you'll have an idea how it works, but know that the specific syntax will be different on different systems.
So here I'm using the in-memory database and we'll start by creating a table. Now this first column is the ID column. So I'm going to name it ID, you can name it whatever you like. ID is pretty common, and it'll be an integer. And for SQLite and most database systems the ID column has to be of an integer type of some sort. And in SQLite if you use the key words primary key, and this is where the constraint goes in the syntax.
This will automatically generate ID's in SQLite. In other words it invokes a sequence generator and it will create sequential numbers in that column. And we'll just create a couple of other columns here and we'll call them a and b. A will be an integer and b will be text and remember, I don't put a comma after that last column in the definition. And now we'll go ahead and add some rows to the table. That looks like this,INSERT INTO text, and we'll specify a and b columns.
And I'll just copy that a couple times. Call them 11 and 12, and b and c. And we'll put in a select statement, so we can see what is in our table now. Press the Go button. And you see our ID column has been automatically populated with sequential numbers. Internally, the database system creates a sequence generator that's keyed to this table. Every time a new row is inserted into the table, the system will use the sequence generator to create a new unique value for the ID column.
Each table may have only one ID column and each row will have a unique value in that column. ID columns are very common in relational databases. They make it easy to create a relationship between rows in different tables. Keep in mind that the method for creating ID columns varies significantly from system to system, so this syntax is not portable, it's not part of the standard. You'll need to find out how it's done for the system you're using in order to use the correct syntax.
- Understanding SQL terminology and syntax
- Creating new tables and records
- Inserting and updating data
- Writing basic SQL queries
- Sorting and filtering
- Accessing related tables with JOIN
- Working with strings
- Finding the numeric type of a value
- Using aggregate functions and transactions
- Updating a table with triggers
- Creating views
Skill Level Beginner
Q: For Mac OS X: When I try to start the Apache Web Server from the XAMPP control panel, it doesn't start, and when I open "localhost" in my web browser, I see a white screen that says "It Works!" instead of the XAMPP page.
sudo apachectl stop
Q: I'm on a Mac, and I get an error in SID that says "attempt to write a read only database." How can I fix this?
A: This usually means that the database folder does not have sufficient permissions for writing by the web user. This can happen if you create the SQL folder new, rather than copying it from the Exercise Files. Here's how to fix this:
- Open a Finder window and Navigate to /Applications/XAMPP/htdocs/SQL
- Control-click on the SQL folder and select "Get Info" from the context menu.
- Under "Sharing and Permissions" (you may need to open the disclosure triangle), in the "everyone" row, select "Read & Write."Then you can close the Info window.
- Now repeat the process for the three *.db files inside the folder.