How to Check Mandatory Fields in a Table Using SQL?

How to Check Mandatory Fields in a Table Using SQL?

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

To check for mandatory (non-nullable) fields in a table using SQL, you'll need to query the database's system catalog or information schema tables, which store metadata about the database structure. The exact query can vary depending on the database system you are using (such as MySQL, PostgreSQL, SQL Server, etc.), but the general approach is to look for columns where the IS_NULLABLE property is set to 'NO'.

Here is an example for some commonly used database systems:

MySQL or MariaDB

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name'
AND IS_NULLABLE = 'NO';

PostgreSQL

SELECT column_name
FROM information_schema.columns
WHERE table_catalog = 'your_database_name'
AND table_name = 'your_table_name'
AND is_nullable = 'NO';

SQL Server

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'your_database_name'
AND TABLE_NAME = 'your_table_name'
AND IS_NULLABLE = 'NO';

Oracle

SELECT COLUMN_NAME
FROM ALL_TAB_COLS
WHERE OWNER = 'your_schema_name'
AND TABLE_NAME = 'your_table_name'
AND NULLABLE = 'N';

SQLite

SQLite does not conform to the SQL standard's information schema, so you would use the PRAGMA statement to get information about a table's schema, although it does not explicitly show which fields are mandatory:

PRAGMA table_info('your_table_name');

You'll need to look at the notnull column in the result, where a value of 1 indicates a non-nullable field.

T-SQL

In T-SQL, which is the dialect of SQL used by Microsoft SQL Server, you can query the sys.columns catalog view to find out which columns in a table are defined as NOT NULL (mandatory). Here's an example of how you can do this:

SELECT 
    c.name AS ColumnName
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    t.name = 'YourTableName' -- Replace with your actual table name
    AND c.is_nullable = 0
    AND t.schema_id = SCHEMA_ID('YourSchemaName'); -- Replace with your actual schema name, e.g., dbo

This query joins the sys.columns view with the sys.tables view to filter columns from a specific table. It then filters to only show columns where is_nullable is 0 (meaning NOT NULL).

If you're working with the default schema (usually dbo), you can omit the schema filter:

SELECT 
    c.name AS ColumnName
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
WHERE 
    t.name = 'YourTableName' -- Replace with your actual table name
    AND c.is_nullable = 0;

Remember to replace your_database_name, your_schema_name, and your_table_name with the actual names in your database. The queries above will return a list of column names in the specified table that are set as non-nullable, which are the mandatory fields.