Insert in to table with check condition

Transact-SQL
1

uid | empno | work_type |

aa0001 | 18050 | uid_creat |
aa0001 | 18056 | prep |
aa0001 | 18351 | valid |
aa0002 | 18056 | uid_creat |
aa0002 | 18050 | prep |
aa0002 | 18351 | valid |

  1. uid_creat
  2. prep
  3. valid

uid is fk from uid table,
empno is fk from emp table
work_typ is fk form wor_def table

priamry key(uid,empno,work_type)

in the above table for each uid there should be a unique empno but there we should perform a check condition that at first uid_creat work_type need to assign for a UID ,
if we try to assing prep before uid_creat it shud throw error i.e 'wrong work assign'

regards
Narender

2

Could you share some examples of data that would pass the check and data that would fail?

3

CREATE TABLE Assocaites
( [Emp No] INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL)

    INSERT INTO Assocaites([Emp no],Name) VALUES 
(8320,'abc'),
(14697,'xyz'),
(17482,'PED')


create table REMOTE_UIDS (
Remote_UID varchar(6) primary key ,
ModelNumber varchar(15) null)

insert into REMOTE_UIDS values('aa0001','dvd54')



Create table Work_Defination

(
Work_Type varchar(10) primary key check(Work_Type IN('UID_CREA','IDEN','PREP')),
Work_Description varchar(25) check (Work_Description IN('UID Creation','Identification','Preparation'))
)

insert into work_Defination values
('UID_CREA','UID Creation'),
('IDEN','Identification'),
('PREP','Preparation')

create table Work_Allocation
(Remote_UID varchar(6) foreign key references REMOTE_UIDS(Remote_UID),
[Emp No] INT foreign key references Assocaites([Emp No] ),
Work_Type varchar(10) foreign key references Work_Defination(Work_Type),
Primary key(Remote_UID,[Emp No],Work_Type)
)

insert into Work_Allocation values
('AA0001',8320,'IDEN'),
('aa0001',14697,'PREP'),
('aa0001',17482,'UID_CREA')

SELECT * FROM Work_Allocation
WORK_TYPE SHOULD FALLOW THE SEQUENNCE

  1. UID_CREA'
  2. 'IDEN'
  3. 'PREP'

IF I TRY TO ASSIGN 'IDEN' UNLESS 'UID_CREA' IS ASSIGNED IT SHOULD THROW ERROR.

I.E ALTER ASSIGN OF 'UID_CREA' , 'IDEN' then only i shoul d be able to assing PREP in Work_Allocation table

4

hi gbritton,
i had a requirement.

please do needful.

There are around 700 Universal Functions list is uploaded for your reference in UNV_Fun table,
all the functions are unique.

There is another table UID in which Unique remote Remote_ID I.e AA0001 last up 100000 with a change is sequence AA0001 to AA9999,AB0001 to AB9999,AC,AD

Requirement is For each Remote_UID(AA0001) there will be around out of 700 functions only 40 to 50 Universal function bit is '1' and rest of the universal functions set to '0'(By default).

My motto is to tell which UIDs is having data for which universal functions.

please tel me how to proceed in-order to develop these kind of scenario.

remote data.JPG1089×155 33.4 KB

waiting for your reply.