365 Days of Daily Coding: Day 119

It’s been a slow and steady few days. I have been wishing to go back home especially because my brother is getting married next month. Unfortunately, I do not have many annual leaves this year so I will have to wait until next year to go back home.

Puzzle 3:

My solution:

Following are the common type of SQL constraints which are basically conditions to pass before a data enters into a table. Reference

EmployeeID : Unique; Not Null; Primary Key
FiscalYear: Check if its numbers
StartDate: Not Null; Check the format of the date for example 20210121
EndDate:Check the format of the date for example 20210121

ALTER TABLE EmployeePayRecords MODIFY EmployeeID INTEGER NOT NULL;
ALTER TABLE EmployeePayRecords MODIFY FiscalYear INTEGER NOT NULL;
ALTER TABLE EmployeePayRecords MODIFY StartDate DATE NOT NULL;
ALTER TABLE EmployeePayRecords MODIFY EndDate DATE NOT NULL;
ALTER TABLE EmployeePayRecords MODIFY Payrate INTEGER NOT NULL;
ALTER TABLE EmployeePayRecords ADD CONSTRAINT EmployeeID_Unique UNIQUE (EmployeeID);
ALTER TABLE EmployeePayRecords
ADD CONSTRAINT EmployeeID_pk PRIMARY KEY (EmployeeID);
ADD CONSTRAINT StartDate_pk PRIMARY KEY (StartDate);
ADD CONSTRAINT EndDate_ck CHECK (to_date(EndDate, 'DDMMYYYY'));
ADD CONSTRAINT StartDate_ck CHECK (to_date(StartDate, 'DDMMYYYY'));

Solution from the repo