I found one of the Jr. Developer writing trigger for the requirement where he wanted to make sure invalidate data does not enter in table column. I suggested him to write Check Constraint. Check Constraints are very handy to make sure all the data in the table is validated before it enters in the database.
Let us check constraint on over one of the table on postalcode table in database AdventureWorks database. Constraint will suggest that value which is larger than 11 character can not be inserted into the column. Once constraint is created, it can be tested by tring to insert value which is larger than 11 character. It will throw an error saying it can not be done as it violates constraint.
USE AdventureWorks
GO
ALTER TABLE Person.Address
ADD CONSTRAINT CK_Address_PostalCode
CHECK (LEN(PostalCode) < 11)
GO
UPDATE Person.Address
SET PostalCode = '12345-12345-123'
WHERE AddressID = 1
GO
Result Set:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint “CK_Address_PostalCode”.
The conflict occurred in database “AdventureWorks”, table “Person.Address”,
column ‘PostalCode’.
The statement has been terminated.
Reference : Pinal Dave (https://blog.sqlauthority.com)
12 Comments. Leave new
thanx man.
how to add more than one value as a check constraint for column?
thanks in advance
ponnusamy, you can add multiple constraints on the column. Add a constraint for each value.
we can not add multiple constraint to single column..
i have one column on table1,
how can i add constraint to that column,
depending on a column on another table
i have ‘salary’ for a worker on workers table,
and i want it to be more than ‘salarybasis’ on jobs table
thanks
u can take column by using =alter table table_name add(column_name constraint constraint_name references table_name(column_name))
i forgot something
it’s important to do that without using triggers
with triggers it’s not hard
@Nir
If there is a FK to the jobs TABLE, add the salary basis column to the FK, then you have it locally and a normal CHECK CONSTRAINT could be used.
Another options would be instead of salary to have salarybasis_modifier, which means that the salary is always at least the salarybasis but it can also be added to in this COLUMN.
What will happen If add a check constraint on a prefilled table i.e table whihc already has data.
Will it allow me to create the check constraint.
You can check this yourself:
— Delete any old test table
IF NOT (SELECT OBJECT_ID(‘dbo.MyTestTable’)) IS NULL DROP TABLE MyTestTable
GO
— Create test table
CREATE TABLE dbo.MyTestTable(i INT)
GO
— Insert row
INSERT INTO dbo.MyTestTable SELECT 1
GO
— Following fails because MyTestTable already has a row
— which doesn’t satisfy the check
ALTER TABLE dbo.MyTestTable ADD CONSTRAINT MyTestTable_chk CHECK (i > 1)
GO
— Insert row
— This succeeds because the check above wasn’t
— actually commited because of the error
INSERT INTO dbo.MyTestTable SELECT 1
GO
— Following works because we are saying that
— “don’t check old values’
ALTER TABLE dbo.MyTestTable WITH NOCHECK ADD CONSTRAINT MyTestTable_chk CHECK (i > 1)
GO
— Try to insert row
— This fails because of the check above
INSERT INTO dbo.MyTestTable SELECT 1
GO
— Returns 2 rows
SELECT * FROM dbo.MyTestTable
GO
— Drop test table
DROP dbo.MyTestTable
GO
Is this possible to fix the start value of a column??
For example there is a column called BookingId (varchar)in a table, and it should start with the value HB100.
Please let me know if it can be done using any check constraint on the column..
Thanks
yes
u can give alias before that content