A relational database is made up of several components, of which the table is most significant. The database table is where all the data in a database is stored, and without tables, there would not be much use for relational databases.
Overall Structure of a Database Table
A database consists of one or more tables. Each table is made up of rows and columns. If you think of a table as a grid, the column goes from left to right across the grid and each entry of data is listed down as a row.
Each row in a relational is uniquely identified by a primary key. This can be by one or more sets of column values. In most scenarios, it is a single column, such as employeeID.
Every relational table has one primary key. Its purpose is to uniquely identify each row in the database. No two rows can have the same primary key value. The practical result of this is that you can select every single row by just knowing its primary key.
Columns
Columns are defined to hold a specific type of data, such as dates, numeric, or textual data. In the simplest of definitions, a column is defined by its name and data type. The name is used in SQL statements when selecting and ordering data, and the data type is used to validate information stored.
So, a DateOfBirth column defined as a date, can be referred to in an order by clause as:
ORDER BY DateOfBirth
And, if you try to add a value of “Hello Kitty” to the column, as part of its validation, it will recognize it isn’t a date, and reject it.
Columns names can’t be duplicated in a table. So, having two “name” columns is a no no. Though you could have two “name” columns, such as name1, and name2, you’ll learn later on, that this is frowned up, as it breaks normal form (I explain this in another post).
Rows
A table can contain zero or more rows. When there are zero, it said to be empty. There is no practical limit on the number of rows a table can hold; however, remember the table’s primary key may have some influence on this. What I mean, is that if your table holds states, and the primary key is the state’s abbreviation, then by definition, since there are only fifty states in the union, and you cannot have duplicates in a primary key, your table is limited to fifty rows.
There is no guarantee that the rows in a table are stored in a particular order. Use the ORDER BY clause to do so.
Also, strictly speaking, in a relational database, there is no first or last row. Yes, you can tease out a first row of a result using a keyword such as LIMIT or TOP, but those are used once the data is retrieved and sorted. The difference here is that you’re seeing the first row of the result, not what is physically stored in the table.
Wrap Up
In summary, you can think of the columns as giving the table its personality and the rows its substance.
Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you.
The post What is a Database Table? appeared first on Essential SQL.
