Msg 513, Level 16, State 0, Line 4 A column insert or update conflicts with a rule imposed by a previous CREATE RULE

Question

Hi Experts

I am facing an issue in user defined data type in SQL server. Before I explain the issue, let me jot down the steps that I have executed.

1. Create a rule with the following definition CREATE RULE [dbo].[City_rule]
   AS
   @value LIKE '^[a-zA-Z]+$'
   GO
2. Create a new user defined type with the following definition CREATE TYPE [dbo].[citytype] FROM varchar NOT NULL

Please note that I have applied the above rule in this data type while creating the data type from SSMS.

3. Create a new table with the user defined data type. Here is the table definition. CREATE TABLE [dbo].myTab ON [PRIMARY]

Issue

The issue is, when I try to use the following insert query

INSERT INTO [dbo].[myTab]  
           ([EmpCity])  
     VALUES  
           ('sasa')  

I am getting the following error - Msg 513, Level 16, State 0, Line 4
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. The statement was terminated. The conflict occurred in database 'Books', table 'dbo.myTab', column 'EmpCity'.

Please help me to fix the issue.

Answer 1

It seems that you assume that LIKE accepts Regular Expressions, which is not true.

Try an alternative rule according to the available features of LIKE:

create rule [dbo].[City_rule]
as
    @value NOT LIKE '%[^a-z]%' and len(@value) > 0

Answer 2

@value LIKE '^[a-zA-Z]+$'  

Your rule indicates that the value you insert must be a value other than letters + $, for example: ‘2+$’.
So when you insert ('sasa') you will get an error.

What you want may be：

CREATE RULE [dbo].[City_rule]  
AS  
@value  NOT LIKE '%[a-z]%$%'  
GO  

The following link may be useful to you:

Percent character (Wildcard - Character(s) to Match) (Transact-SQL)

If this does not solve your problem, please tell us what rules you want to make for inserting values.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

Hi

After using "Not" now I can still insert both the values (please check the query below)

INSERT INTO [dbo].[myTab] ([EmpCity]) VALUES ('sasa')
INSERT INTO [dbo].[myTab] ([EmpCity]) VALUES ('sa2sa')