Tables, Schemas, Columns, and Keys

Database tables

Now that we know more about a database’s use in the real world and benefits of SQL knowledge, we will dive into the components of the database.

We can refer to the database components as objects. These objects can range from tables to columns, indexes, and triggers. Essentially, these are all pieces of the data puzzle that make up the database itself.

Within the database are tables, which hold the data itself. A table consists of columns that are the table’s headers, for example, “FirstName” and “LastName.”

The cell is where the data is shown, like someone’s actual first and last names. Some cells may not always have data considered NULL in the database. This means that no data exists in that cell.

Note: In Microsoft SQL Server, a table can have up to 1,024 columns but can have any number of rows.

Schemas

A schema is considered a logical container for the tables. It’s essentially a way to group tables based on the data type they hold. It won’t affect any end user who interacts with the database, like someone who runs reports. However, an end user who works directly with the database, like a database administrator or a developer, will see the available schemas.

Consider a realistic example of several tables containing data for sales records. There might be several tables named “Sales Order”, “Sales History”, or “Sales Customer.” We can put all these sales tables into a “Sales” schema to better identify them when working directly with the database.

Columns

As mentioned previously, a column is a header within a table defined by a data type. The data type specifies the type(s) of data we can hold in that specific cell, for example, where the row and column meet. Every cell in the highlighted row represents a column of the table.

Rows and NULL values

A row is considered as an entry in a table. The row will be one line across and typically have data within each column. Sometimes, there may be a “NULL” value in one or many cells.

Back to our example, most people have first and last names, but not everyone has a middle name. In that case, a row would have values in the first and last name columns but not the middle name, as shown in the table below:

Primary key

A primary key is a constraint on a column that forces every value in that column to be unique and not null.

Forcing uniqueness on values in that column helps maintain the integrity of the data and helps prevent any future data issues.

A realistic example of a primary key would be an employee ID or a sales record ID. We wouldn’t want to have two of the same employee ID for two different people, nor would we want to have two or more of the same sales record ID for different sales transactions, which will be a challenge when trying to store and retrieve data.

We can see in the example below that each value for “BusinessEntityID” is unique for every person.

Foreign key

The foreign key acts as a link between two tables. The foreign key may allow repeated and null values. The foreign key is “ProductID” in the first table “Sales.SalesOrderDetail” below.

There is a link between the “ProductID” in the “SalesOrderDetail” table to the “ProductID” (primary key) in the “Production.Product” table, see the figure below: