Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together:

    p001
    b201

I know I need to create a rule and bind that rule to the primary key column. I am little confused in creating the rule using a LIKE query:

CREATE RULE pK-rule AS @pk LIKE'[a-z][0-9][0-9][0-9]'

Can anybody suggest what I need to change?

share|improve this question
add comment

2 Answers 2

up vote 2 down vote accepted
USE YourDatabase
GO

CREATE RULE PkRule AS
@pkValue LIKE '[a-z][0-9][0-9][0-9]'
GO

sp_bindrule 'PkRule','dbo.YourTable.ColName'
share|improve this answer
    
@chauhan thanks jp it worked so the like condition was correct which i gave thanks again –  Biju jose Aug 21 '12 at 6:16
7  
Rules are deprecated. Don't use. msdn.microsoft.com/en-us/library/ms176063.aspx –  gbn Aug 21 '12 at 7:09
    
thanks gbn for the information –  Biju jose Aug 21 '12 at 8:52
add comment

You probably don't want to use rules and instead use constraints, in this case a check constraint. The reason that you don't want to use rules is that rules have been deprecated means that they will be removed from SQL Server at some point in the future so it would be better to use the check constraint instead. Something like this will do the trick.

create table t1 (c1 char(4) primary key check (c1 like '[a-z][0-9][0-9][0-9]'), c2 int)
share|improve this answer
    
My thoughts exactly, +1 :) –  Mr.Brownstone Aug 21 '12 at 6:46
    
@mrdenny thanks denny for this thoughtful one and you are right check is much better than rule the one thing i missed is i don't that like function can be used in check constraint anyway thanks –  Biju jose Aug 21 '12 at 8:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.