Is there a simple query to return whether a specific column allows nulls?
I want to change this as part of a DB upgrade script.
Alternatively, is it better to just change it, even if its already set?
Edit : This is for SQL Server (needs to support 2000 or later)
The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value. Which is why you can only use IS NULL / IS NOT NULL as predicates for such situations.
The IS NOT NULL condition is used in SQL to test for a non-NULL value. It returns TRUE if a non-NULL value is found, otherwise it returns FALSE. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Any particular RDBMS?
In SQL Server
use master
SELECT COLUMNPROPERTY( OBJECT_ID('dbo.spt_values'),'number','AllowsNull')
Or (more standard)
select IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
AND TABLE_NAME='spt_values'
AND COLUMN_NAME='number'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With