0

I am trying to figure out how to get an item code/product code to have a letter fixed to the start each time I create a new product.

For example, if the product code is A1234 the person cannot change the letter A, but they can change the last 4 numbers.

PRODUCT_CODE must have 5 characters (the first being A and the rest numbers)

How can I create a constraint on the Product_code column such that it must begin with A and end in four numbers (if it is not in this format, and is it possible to do it with a where clause? Thanks

  • Are you just wanting this to be a constraint on the column or are you wanting this to be an auto_incrementing field? – Siyual Mar 24 '17 at 18:39
  • 3
    Sounds like a `Check` constraint, incl. regex? Maybe [this link](http://stackoverflow.com/a/7621734/2654498) could help? – Nick Mar 24 '17 at 18:41
  • just to be a constraint –  Mar 24 '17 at 18:51

1 Answers1

0

This constraint would enforce your example:

create table t1 (product_code varchar2(5));

alter table t1 add constraint product_code_fmt
   check (regexp_like(product_code,'A[[:digit:]]{4}'));
Daniel Emge
  • 1,567
  • 1
  • 9
  • 7
  • Hi thank you i dont suppose you know if its possible or better yet how to do it via WHERE condition ? –  Mar 24 '17 at 21:43
  • You can't enforce a constraint with a where clause. If you don't like the check constraint, you could use a table trigger. – Daniel Emge Mar 27 '17 at 13:46