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 🙂
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}$')