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.
Create a rule with the following definition
CREATE RULE [dbo].[City_rule]
AS
@value LIKE '^[a-zA-Z]+$'
GO
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.
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.