top of page

Snack-Sized Data Basics
SQL - Column Constraints

When creating a table, we need to specify

  • the SQL keyword required to create a table

  • the table name

  • the column names

  • the column data types

  • and the column constraints if any​​

​​

 

CREATE TABLE table_name (
    column_name_1 DATA_TYPE COLUMN_CONSTRAINT,
    column_name_2 DATA_TYPE,
    column_name_3 DATA_TYPE
);

​​​​​​​​
Column constraints are used to impose restrictions on the type of data that can be stored in a column. While they are not required, they add an extra layer of data consistency and integrity. In a way, they act like security guards, ensuring that only the right data is allowed in and that everything follows the rules.​

dateleaflet-secuirtyguards.gif

​​​​​​​​
As we go over some common column constraints below, you'll notice the term NULL used frequently, so let's define it. 

NULL represents a missing value in a column. It's important to note that NULL is not the same as a blank space or the number 0, as both of those are considered non-missing values.​

Here are some common column constraints.

​

  • NOT NULL: ensures that a value is always provided (cannot be empty).

  • PRIMARY KEY: ensures a unique, non-NULL value for each record.

  • UNIQUE: ensures all values are unique, but allows NULL values.

  • DEFAULT: provides a default value for a column when no value is provided.

  • CHECK: ensures that the data meets specific conditions. For example, you might set a rule to only allow numbers above 5.

  • SERIAL: automatically generates a new, unique number for each record added. There's no need to specify INT as the data type when using SERIAL, as it automatically incorporates the INT data type.

  • FOREIGN KEY: creates a connection between two tables by referencing the unique ID in another table.

  • INDEX: speeds up data retrieval by creating a faster lookup, similar to an index in a book.

In our next lesson, we’ll explore table creation with some hands-on examples!

bottom of page