question

KaranGupta-3141 avatar image
0 Votes"
KaranGupta-3141 asked EchoLiu-msft edited

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

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](20) NOT NULL

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

  1. Create a new table with the user defined data type. Here is the table definition.

    CREATE TABLE [dbo].[myTab](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [EmpCity] [dbo].[citytype] NULL,
    CONSTRAINT [PK_myTab] PRIMARY KEY CLUSTERED
    (
    [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) 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.




sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited
 @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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KaranGupta-3141 avatar image
0 Votes"
KaranGupta-3141 answered EchoLiu-msft edited

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')


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Obviously, you did not use the appropriate rule.

0 Votes 0 ·

Hi Viorel-1

Could you please help me with the right expression

0 Votes 0 ·

Try this rule:

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

Assign it to citytype.

0 Votes 0 ·

Could you tell me what your rules are? And what are the values you want to insert and the values that are not allowed to be inserted?

0 Votes 0 ·