Tables¶

Limit SQL Server column to a list of possible values¶

Limit SQL Server column to a list of possible values

CHECK constraint after creation¶

ALTER TABLE <table> ADD CONSTRAINT chk_val CHECK (col in ('yes','no','maybe'))

MSDN: CHECK Constraints

Check list at creation¶

CREATE TABLE test( _id PRIMARY KEY NOT NULL, decision NVARCHAR(5), CHECK (decision in ('yes','no','maybe')) );

Restrict varchar column to specific values

CHECK constraint from creation¶

CREATE TABLE SomeTable
(
   Id int NOT NULL,
   Frequency varchar(200),
   CONSTRAINT chk_Frequency CHECK (Frequency IN ('Daily', 'Weekly', 'Monthly', 'Yearly'))
)

Constraints and Trust¶

SQL Server constraint is “not trusted”

Managing untrusted foreign keys

Blitz Result: Foreign Keys or Check Constraints Not Trusted

If you disable a constraint and then enable it again without specifying the WITH CHECK option then SQL Server will mark that constraint as "not trusted" since it can no longer rely on the existence of the constraint to guarantee that the data is consistent with the constraint conditions. This results in the constraint being ignored for the purposes of execution plans, so the best practice is to always use WITH CHECK when re-enabling constraints.

Everytime MSSQL cannot check the constraint:

1. If you disable a constraint and enable without check:
2. Alter table nocheck constraint constraintname Alter table with nocheck check constraint constraintname
3. If you create a constraint disables:
4. Alter table with nocheck add constraint ...
5. If you enable a constraint but there are rows that violates the constraint

Too see rows that violates the constraint you can use DBCC CHECKCONSTRAINTS

SQL Server: How to make server check all its check constraints?

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS won't actually make your constraints trusted. It will report any rows that violate the constraints. To actually make all of your constraints trusted, you can do the following:

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS --This reports any data that violates constraints.

--This reports all constraints that are not trusted
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  
FROM sys.check_constraints 
WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id) AS table_name, name, is_disabled  
FROM sys.foreign_keys
WHERE is_not_trusted = 1
ORDER BY table_name

Test if Object Exists¶

https://stackoverflow.com/questions/5952006/how-to-check-if-table-exist-and-if-it-doesnt-exist-create-table-in-sql-server-2

IF object_id('MyTable') is not null PRINT 'Present!' ELSE PRINT 'Not accounted for'

https://docs.microsoft.com/en-us/sql/t-sql/functions/object-id-transact-sql

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID

Object type:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SO = Sequence object

U = Table (user-defined)

V = View

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Customers') BEGIN PRINT 'Table Exists' END

http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/

From SQL Server 2016 CTP3 you can use new DIE statements instead of big IF wrappers, e.g.:

DROP TABLE IF EXISTS dbo.Product

DROP TRIGGER IF EXISTS trProductInsert