Want to learn more? Take the full course at [ Ссылка ] at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---

Now it's time to turn to the contents of individual columns: the data types and the constraints on what values can exist in each column.

Foreign keys and primary keys are two types of constraints that limit the values in a column, but columns can also be constrained in other ways.

Unique means that each value except NULL must be different from the values in all other rows. Not NULL means what it says - the column cannot contain null values.

Check constraints are a way of implementing additional conditions on the values of a column, such as requiring the column only contain positive values, or ensuring that the value of one column is greater than the value of another column.

Constraints can limit the values in a column, but the main thing that determines what values are allowed is the column's type. Each column in the database can only store one type of data.

In this course, we're talking about three of the most common types of data: numeric, character, and date/time. These three, along with boolean - which holds true or false values - are the most common types you'll encounter, but they're not the only ones.

There are also special data types to hold monetary values, geometric data like points or lines, and structured data types like XML and JSON. These special types differ more across database implementations than the four common ones.

Within the broad categories of numeric, character, or date/time data, there are multiple column types with different details. For example, different numeric types require different amounts of memory per row and can store different ranges of values.

In the upcoming chapters, we'll talk more about these specific types, so no need to worry about the details at this point.

You can find the type of each column in the entity-relationship diagram. Here is the fortune500 table.

There are three different numeric data types used in the table: integer, real, and numeric. Even if you don't have an entity-relationship diagram, the column type is a core piece of information you can expect to find in other kinds of documentation.

Values can be converted temporarily from one type to another through a process called casting. When you cast a column as a different type, the data is converted to the new type only for the current query.

To change a value's type, use the cast function, first, specify the value you want to cast. This can be a single value or the name of a column. Then use the keyword AS. Finally, specify the name of the type you want to convert the data to.

Here's an example of casting the single numeric value 3-point-7 as an integer. Casting from numeric to integer rounds the value to the nearest integer, which is 4.

To convert the type of an entire column, enter the name of the column as the value. Here, a column called total is converted to type integer. We need a from clause to specify which table the column comes from.

There's an alternate notation for casting values: a double colon. It does the same thing as the cast function, but it's more compact.

Put the value to convert before the double colon and the type to cast it as after the double colon. The examples here are the same as those on the previous slide, except with the double colon notation instead of the cast function.

OK, you know what time it is. Let's practice.

свернуть