how to apply constraint by Stored procedures that ID should start with 'E' and has 2 numerics aftarwards?

EID should only be inserted if it starts with 'E' and has 2 numerics aftarwards …. i was trying to write a procedure for the purpose like:

create proc emp_checks(@eid varchar(20), @name varchar(20),@age int, @salary int, @city varchar(20)) AS Begin Declare @citycount int= (select count(*) from employee where @city=city)  if (@citycount < 2 and @eid like 'E--%') insert into employee values(@eid,@name,@age,@salary,@city) else  print 'city exceeded' End 

here the issue is that -- or spaces would insert non-numerics also here this code also includes few extras *just homework things 🙂

Add Comment
1 Answer(s)

Why would you write a stored procedure? MySQL now supports check constraints:

alter table employee add constraint chk_employee_eid     check (eid regexp '^E[0-9]{2}') 

Note that this allows anything after the first 3 characters. If you don’t want anything, then add $ to the pattern:

alter table employee add constraint chk_employee_eid     check (eid regexp '^E[0-9]{2}$') 
Answered on July 16, 2020.
Add Comment

Your Answer

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