How do I create a rule for a Primary Key

Question

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?

Answer 1

USE YourDatabase
GO

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

sp_bindrule 'PkRule','dbo.YourTable.ColName'

Answer 2

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)