đź“šBack-end Notes

4.SQL data types

Oct 04, 20246 min read

Numeric data types

Data Types

When you create a table in a database, you need to define column names and the data type of the content that will reside in those columns.

Data type tells the database management system such as MySQL, how to interpret the value of the column. Data types maintain data in the right format and make sure the value of each column is as expected.

Most used data types

  • numeric,
  • string
  • date and time

Numeric Data type

String data type

Numeric data types is the generic term used for all specific data types that let a column store data as numbers in the database.

The two most common numeric data types used in databases are:

  • The integer data type : used for a whole number value
  • The decimal data type : used for number with a fraction value.

TIP

Fractional numbers can be inserted, but they’ll always be automatically rendered up or down to the nearest whole number of the database.

Max & Min number value

In MySQL Database Management System, tiny integer or TINYINT is used for very small integer number value, with the maximum possible value that can be inserted is 255.

While integer or INT can be used to store a very big number, the maximum value that it can store is over four billion.

These data types can also accept negative and positive values.

TIP

In some Database Management Systems, you can also force columns to accept positive numbers. This increases the maximum value they can store.

String data types

You can use a string datatype to define the columns datatype, particularly in instances when it accepts both numeric and text characters.

When you create a table in a database, it is important for data integrity to ensure that only valid values are inserted in your table. For example, you should use string datatype when you intend to store data that contains a mix of character types.

If you define a column as a string, then any type of text can be inserted. This includes:

  • alphabet characters
  • numeric characters
  • special characters

String data type

String datatype is a generic term used for different string datatypes in the database.

The most used string datatypes are:

  • CHAR : which stands for character. This datatype is used to hold characters of a fixed length.
  • VARCHAR : stands for variable character. This holds characters of the variable length.

CHAR

CHAR means that the given length of the characters is predetermined. It can’t be changed after declaration.

Column attributes are defined as CHAR followed by a character length in parentheses. (CHAR(50))

CHAR is the best option if you’ve predefined size of character that you want to maintain.

NOTE

For example, the table contains the records for a student with a username Mark 1 2 3, which is a total of seven characters. However, because the column is defined as CHAR (50), this username occupies the length of 50 characters within the space.

VARCHAR

The VARCHAR datatype works in a similar way to CHAR. Whoever it is a variable length. This means that the length can be changed. It’s not fixed.

VARCHAR are often used when you’re not sure how many characters might be inserted in the column field.

You can type VARCHAR(50) in SQL to allow for any input up to maximum of 50 characters.

NOTE

This means that the name of each student only occupies as much space as there are characters in their name.

Other string data types

  • TINYTEXT is used to define columns that require less than 255 characters, like short paragraphs.
  • TEXT is used to define columns of less than 65,000 characters, like an article.
  • MEDIUMTEXT defined columns of 16.7 million characters. For example, the text of a book.
  • The LONGTEXT datatype stores up to four gigabytes of text data.
CREATE TABLE students 
(
    username CHAR(5), 
    fullName VARCHAR(100), 
    email VARCHAR(255)
);

Default values

Database constraints

Database constraints

Database constraints are used to limit the type of data that can be stored in a table. This ensures that all data inserted into the table is accurate and reliable.

If the database dictates a violation between the constraint and the data operations, then it aborts these operations.

An example of a violation might be an attempt to insert or upload invalid data to a table. The database realizes that the data is invalid and rejects it.

INFO

Constraints can be column level where the rule applies to a specific column. They can also be applied at table level.

For example, I could use the foreign key constraints to prevent actions that will destroy links between tables.

Two of the most used database constraints include:

  • NOT NULL : a method of preventing empty value fields.
  • DEFAULT : a method of assigning default values.

NOT NULL SQL constraint

NOT NULL

The NOT NULL SQL constraint is used to ensure the data fields are always completed and never left blank.

NOT NULL SQL Statement

The NOT NULL default value is implemented using a SQL statement. A typical NOT NULL SQL statement begins with the creation of a basic table in the database.

CREATE TABLE Customer (
	customer_id int NOT NULL,
	customer_name varchar(255) NOT NULL,
);

Now, any operation that attempts to place a null value in these columns will fail, like inserting or updating data.

DEFAULT constraint

DEFAULT

The DEFAULT constraints sets a default value for a column of no value is specified. This means that if no data is entered for a specific field within a column, then the table will automatically insert a default value instead.

DEFAULT SQL Statement

CREATE TABLE Player (name varchar(50) NOT NULL, city varchar(30) DEFAULT "Barcelona");

Now when I add data into the table for a new player, I don’t need to type in Barcelona for players who are from the city. Instead, it will be inserted automatically.

Additional resources

The following list of resources explore the meaning and role of different data types in databases. They also provide good examples for how to declare and use the data types in SQL.

SQLDataDatabaseDataType

Previous one → 3.Basic database structure | Next one → 5.Create and read

Graph View

Backlinks