I am working in Azure SQL Managed Instance. I have a table with some explicitly-named NOT NULL constraints, such as the following:
CREATE TABLE my_db.my_schema.my_table (
my_column int CONSTRAINT nn_my_table_my_column NOT NULL
)
;
I'm trying to find where this constraint lives in the DB. To my knowledge, NOT NULL constraints are CHECK constraints. So, I have tried the following:
SELECT
*
FROM
my_db.sys.objects
WHERE
type_desc = 'CHECK_CONSTRAINT'
;
However, no rows return. So, either Azure SQL Managed Instance doesn't declare NOT NULL constraints as CHECK constraints, or they're saved elsewhere. How can I find this particular constraint?
my_column int CONSTRAINT nn_my_table_my_column NOT NULL doesn't actually create a CONSTRAINT. This is because there is no actual CHECK defined. You could just write the line as my_column int NOT NULL. We can confirm this by checking the sys.check_constraints and also attempting to create another CONSTRAINT with the same name:
USE Sandbox;
GO
CREATE TABLE dbo.MyTable (MyColumn int NOT NULL,
YourColumn int CONSTRAINT chk_Mytable_YourColumn NOT NULL);
GO
SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N'dbo.MyTable');
GO
ALTER TABLE dbo.MyTable ADD CONSTRAINT chk_Mytable_YourColumn CHECK (YourColumn > 0);
GO
SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID(N'dbo.MyTable');
GO
GO
DROP TABLE dbo.MyTable;
This returns no results for the first SELECT, only in the second, when the CREATE was successfully run. If a CONSTRAINT of the name already exists, it would have failed.
What you want to do is check if the column is NULLable or not, which is defined in sys.columns:
SELECT *
FROM sys.columns c
WHERE c.is_nullable = 0
AND object_id = OBJECT_ID(N'dbo.MyTable');
If you really wanted to create a CHECK CONSTRAINT that did not permit NULL values, then you would define it as the following:
my_column int CONSTRAINT nn_my_table_my_column CHECK (my_column IS NOT NULL)
Though, I really suggest that's a "bad" idea; there's no need for a CHECK CONSTRAINT here.