SQL Server Performance Forum – Threads Archive

Userdefined Data Type

Hello! I have a table inside of that I want to define specific Userdefined Data Type so that a field only accept a certain format of data. For instance, eight numbers and one letter (25145218A). Is it possible to define these specific format or others in a field of a table? In that case, how can I do this? Is it better to define it only in the application? Thanks,
Cesar

Yes you can create a CHECK constaraint on the same to allow a particular datatype. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

See CREATE TABLE for examples on the same.. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Ok gaurav, I’ m going to see those examples later. Thank you,
Cesar

Cesar – You can create a rule than create a user defined datatype bind it. The rule will look something like this:<pre> CREATE RULE rule_name AS (substring(@v, 1, 7) NOT LIKE ‘%[^0-9]%’ ) — only digits AND (substring(@v, 8, 1) NOT LIKE ‘%[^A-Z]%’ ) — only letters </pre> Once the rule is created, you can create the user defined datatype as varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> and use rule_name as it’s rule. Bambola.

Thanks Bambola,
I’ ve been reading several concepts about Userdefined Data Types in SQL Server Help reference, and it says that the best option to define a specific data bind it is using Check Constraints instead of Rules, because Check Constraints are included in the Standard SQL-92 (I don#%92t know what does it means but sounds reliable J). Isn#%92t it?
In affirmative case, where could I find a large reference about Check Constraints syntax explanation and examples to define a specific data bind it? Your example is very good but I don#%92 t understand a big part of the syntax yet. Cesar

Inclusion in SQL-92 makes the probability of the feature to continue in next versions high. As I mentioned before BOL has a good explaination on this. Here is an excerpt from Inside SQL Server 2000- Although CHECK constraints and rules are essentially equivalent in functionality, CHECK constraints are easier to use and provide more flexibility. CHECK constraints are the preferred mechanism for restricting values in a column; SQL Server provides rules primarily as backward compatibility feature. A CHECK constraint can be conveniently defined when a column is defined, it can be defined on multiple columns, and it has access to all of SQL Server’s built-in functions. A rule, however, must be defined and then bound separately to a single column or user-defined datatype, and it has access only to built-in functions that don’t reference database objects. Both CHECK constraints and rules can require that a value fall within a particular range, match a particular pattern, or match one of the entries in a specified list. An advantage of CHECK constraints is that they can depend on either the value of another column or columns in the row or on the value returned by one of the built-in functions. A rule can’t reference other fields. As an example of applying a CHECK constraint or rule, a database containing information on senior citizens could have the CHECK constraint or the rule "age column must contain a value between 65 and 120 years." A birth certificate database could require that the date in the birth_date column be some date prior to the current date. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Agreed

If you are not comfortable with the example above, you can use something like<pre> ALTER TABLE table_name ADD CONSTRAINT [CK_name] CHECK ([fiels_name] like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]’) – example: declare @v varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> select @v = ‘1234567A' if @v like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' select ‘ok' else select ‘not ok' select @v = ’12A34567′ if @v like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' select ‘ok' else select ‘not ok'</pre> Bambola.

Better [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />], I have some questions about the example (declare @v varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />…): What is the purpose of the declaration? Where should I write it? The code would not be?: declare @v varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> select @v = ‘column_name' if @v like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]' select ‘ok' else select ‘not ok. You must enter 7 numbers and 1 letter'

The example was just to show you how it works. What you need to do is add a CONSTRAINT to your column.
This is the syntax to do that. Change the table_name CK_name and fiels_name, and run it in QA. ALTER TABLE table_name
ADD CONSTRAINT [CK_name] CHECK
([fiels_name] like ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]’) Bambola.

I have added Check Constraint successfully in the query analyser, but when I try to insert data in that column the error message is ‘[Microsoft][ODBC SQL Server Driver][SQL Server]Instruction INSERT in conflict with the constraint COLUMN CHECK ‘CK_code’…. the instruction finished’, and I entered correctly the code format for that column.

Could you script your constraint and post it together with the insert statement? Bambola.

The script is as follows: ALTER TABLE Users
ADD CONSTRAINT CK_code
CHECK (User_code LIKE ‘[0-9][0-9][0-9][0-9]
[0-9][0-9][0-9][0-9][A-Z]’)
Maybe the problem is the general format of the data (varchar, char, int,..) but I tried with several ways and the problem continues.

Sorry, I haven’t used any insert statement, I have inserted directly on table

what value are you trying to insert? it should be 8 digits and 1 letter… if it is case sensitive you will need also a-z ([A-Za-z]) Bambola.

No, the problem is the same every time. The script and data that I insert seems correct..: ALTER TABLE Users
ADD CONSTRAINT [CK_code]
CHECK ([User_code] LIKE ‘[0-9][0-9][0-9][0-9]
[0-9][0-9][0-9][0-9][A-Za-z]’)
DECLARE @V char(9)
SELECT @V = ‘12345678X’
INSERT INTO Users (User_code)
VALUES (@V)

Strange. The same thing happened to me. Then I deleted and recreated the constraint with the exact same code and it worked fine. Bambola.

You are lucky, the same thing does not happen to me.

It’s the line break that is causing the problem 😛
Try ALTER TABLE Users
ADD CONSTRAINT [CK_code]
CHECK ([User_code] LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Za-z]’) Bambola.

Good! [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />], it is true. Now works fine. Thank you very much, Cesar

]]>