Displaying Table Columns and NULL Constraints in SQL

When working with databases, it's often useful to know which columns in your tables allow NULL values and which do not. You can easily retrieve this information using SQL queries on the information schema of your database. Below, I'll show you how to do this for both MySQL and PostgreSQL.

MySQL

To get a list of columns and their NULL constraints for a specific table in MySQL, you can query the INFORMATION_SCHEMA.COLUMNS table. Here’s the query you can use:

SELECT 
    COLUMN_NAME AS 'Column Name', 
    IS_NULLABLE AS 'Is Nullable'
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = 'your_database_name' 
    AND TABLE_NAME = 'your_table_name'
ORDER BY 
    ORDINAL_POSITION;

Explanation:

• COLUMN_NAME: The name of the column.
• IS_NULLABLE: Indicates whether the column allows NULL values (YES means it allows NULL, NO means it does not allow NULL).
• TABLE_SCHEMA: The name of your database.
• TABLE_NAME: The name of your table.
• ORDINAL_POSITION: Ensures the columns are listed in the order they appear in the table schema.

PostgreSQL

Similarly, in PostgreSQL, you can query the information_schema.columns table to retrieve this information. Here’s the equivalent query:

SELECT 
    column_name AS "Column Name", 
    is_nullable AS "Is Nullable"
FROM 
    information_schema.columns
WHERE 
    table_schema = 'public' 
    AND table_name = 'your_table_name'
ORDER BY 
    ordinal_position;

Explanation:

• column_name: The name of the column.
• is_nullable: Indicates whether the column allows NULL values (YES means it allows NULL, NO means it does not allow NULL).
• table_schema: Typically 'public' for most user-defined tables unless you are using a different schema.
• table_name: The name of your table.
• ordinal_position: Ensures the columns are listed in the order they appear in the table schema.

Example

Assume you have a table named users in a database named my_database. Here's how you would query the constraints:

MySQL Example:

SELECT 
    COLUMN_NAME AS 'Column Name', 
    IS_NULLABLE AS 'Is Nullable'
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_SCHEMA = 'my_database' 
    AND TABLE_NAME = 'users'
ORDER BY 
    ORDINAL_POSITION;

PostgreSQL Example:

SELECT 
    column_name AS "Column Name", 
    is_nullable AS "Is Nullable"
FROM 
    information_schema.columns
WHERE 
    table_schema = 'public' 
    AND table_name = 'users'
ORDER BY 
    ordinal_position;

Example Output:

Column Name	Is Nullable
id	NO
name	NO
email	YES
created_at	NO
updated_at	YES

Using these queries, you can easily check the NULL constraints of your table columns, helping you understand the schema and data requirements better.