Introducing constraints on SQL type tables​
​by Damian Calin
SQL constraints are used to specify rules for data in a table.​There are many types of constraints but most commonly used and covering the majority of business scenarios : UNIQUE - Ensures that all values in a column are different CHECK - Ensures that the values in a column satisfies a specific condition ​Introducing new constraints support in SQL function arsenal . addConstraintsSQL [tableSQL , Association [“constrantname1”  {“CHECK” , condition }, “constrantname2”  {“UNIQUE” , {col1 ,col2 .... }} ] , showErrorsAsTable ]​Input - Table SQL - Association containing constraints to apply to table data. CKECK type constraint contains condition that must be met for the data to be valid UNIQUE type constraints contains list of columns that should be unique - showErrorsAsTable (True|False) default False. If True displays output[“errors”] in table format Output - Table SQL containing only valid data - output[“errors”] association containing invalid data corresponding to each constraints.

Load SQL functions

Load the last version of SQLOperators package containing SQL function developed so far.
See my previous post : SQL operators as Wolfram Language functions (https://community.wolfram.com/groups/-/m/t/2195893)
In[]:=
CloudImport["https://www.wolframcloud.com/env/damcalrom/Published/SQLOperators.wl"];

Get sample data

In[]:=
symbols={"MSFT","TSLA","AMZN"};​​sampledata=(Flatten/@Tuples[{{#},FinancialData[#,{{2020}}]//Normal}])&/@symbols//Flatten[#,1]&;​​sampledataSQL=tableSQL[sampledata,{"symbol","time","close"}];

Add some invalid data

In[]:=
tbinvalid=​​sampledataSQL//​​(*addduplicateson{symbol,time}*)​​unionSQL[#,tableSQL[ConstantArray[{"MSFT",DateObject[{2020,3,1}],Quantity[200,"$"]},2],{"symbol","time","close"}]​​ ]&//​​(*addrowwithinvaliddate*)​​unionSQL[#,tableSQL[{{"MSFT",20201539,Quantity[300,"$"]}},{"symbol","time","close"}]​​ ]&;​​​​tbinvalid//​​showTableSQL
Total Rows:762 Elapsed:1.×
-7
10
Out[]//TableForm=
symbol
time
close
MSFT
Thu 2 Jan 2020 00:00:00GMT+1.
$
160.62
MSFT
Fri 3 Jan 2020 00:00:00GMT+1.
$
158.62
MSFT
Mon 6 Jan 2020 00:00:00GMT+1.
$
159.03
MSFT
Tue 7 Jan 2020 00:00:00GMT+1.
$
157.58
MSFT
Wed 8 Jan 2020 00:00:00GMT+1.
$
160.09
MSFT
Thu 9 Jan 2020 00:00:00GMT+1.
$
162.09
MSFT
Fri 10 Jan 2020 00:00:00GMT+1.
$
161.34
MSFT
Mon 13 Jan 2020 00:00:00GMT+1.
$
163.28
MSFT
Tue 14 Jan 2020 00:00:00GMT+1.
$
162.13
MSFT
Wed 15 Jan 2020 00:00:00GMT+1.
$
163.18

Define constraints on table

In[]:=
tbvalid=tbinvalid//​​ addConstraintsSQL[#,Association["ckTime"{"CHECK",DateObjectQ[time]},(*checkconstrainttoeliminateinvaliddates*)​​ "ckCloseLimit"{"CHECK",QuantityMagnitude[close]>100},(*checkconstrainttoeliminateclosevaluesongivencondition*)​​ "ukSymbolTime"{"UNIQUE",{symbol,time}}(*uniqueconstrainttoeliminateduplicatesoncombinationofcolumns*)​​ ]]&;//AbsoluteTiming​​​​tbvalid//​​showTableSQL
Out[]=
{5.47238,Null}
Total Rows:743 Elapsed:0.
Out[]//TableForm=
symbol
time
close
MSFT
Thu 2 Jan 2020 00:00:00GMT+1.
$
160.62
MSFT
Fri 3 Jan 2020 00:00:00GMT+1.
$
158.62
MSFT
Mon 6 Jan 2020 00:00:00GMT+1.
$
159.03
MSFT
Tue 7 Jan 2020 00:00:00GMT+1.
$
157.58
MSFT
Wed 8 Jan 2020 00:00:00GMT+1.
$
160.09
MSFT
Thu 9 Jan 2020 00:00:00GMT+1.
$
162.09
MSFT
Fri 10 Jan 2020 00:00:00GMT+1.
$
161.34
MSFT
Mon 13 Jan 2020 00:00:00GMT+1.
$
163.28
MSFT
Tue 14 Jan 2020 00:00:00GMT+1.
$
162.13
MSFT
Wed 15 Jan 2020 00:00:00GMT+1.
$
163.18

Get invalid rows filtered out by the constraints defined on table

In[]:=
​​tbvalid["errors"]["ckTime"]//showTableSQL​​tbvalid["errors"]["ckCloseLimit"]//showTableSQL​​tbvalid["errors"]["ukSymbolTime"]//showTableSQL
Total Rows:1 Elapsed:5.×
-6
10
Out[]//TableForm=
symbol
time
close
MSFT
20201539
$
300
Total Rows:16 Elapsed:1.7×
-6
10
Out[]//TableForm=
symbol
time
close
TSLA
Thu 2 Jan 2020 00:00:00GMT+1.
$
86.05
TSLA
Fri 3 Jan 2020 00:00:00GMT+1.
$
88.60
TSLA
Mon 6 Jan 2020 00:00:00GMT+1.
$
90.31
TSLA
Tue 7 Jan 2020 00:00:00GMT+1.
$
93.81
TSLA
Wed 8 Jan 2020 00:00:00GMT+1.
$
98.43
TSLA
Thu 9 Jan 2020 00:00:00GMT+1.
$
96.27
TSLA
Fri 10 Jan 2020 00:00:00GMT+1.
$
95.63
TSLA
Mon 16 Mar 2020 00:00:00GMT+1.
$
89.01
TSLA
Tue 17 Mar 2020 00:00:00GMT+1.
$
86.04
TSLA
Wed 18 Mar 2020 00:00:00GMT+1.
$
72.24
Total Rows:2 Elapsed:2.7×
-6
10
Out[]//TableForm=
symbol
time
close
MSFT
Day: Sun 1 Mar 2020
$
200
MSFT
Day: Sun 1 Mar 2020
$
200

Using showErrorsAsTable = True will display invalid rows in table format

In[]:=
tbvalid=tbinvalid//​​ addConstraintsSQL[#,​​ Association["ckTime"{"CHECK",DateObjectQ[time]},(*checkconstrainttoeliminateinvaliddates*)​​ "ckCloseLimit"{"CHECK",QuantityMagnitude[close]>100},(*checkconstrainttoeliminateclosevaluesongivencondition*)​​ "ukSymbolTime"{"UNIQUE",{symbol,time}}(*uniqueconstrainttoeliminateduplicatesoncombinationofcolumns*)​​ ],​​ True​​ ]&;//AbsoluteTiming​​​​tbvalid["errors"]//​​showTableSQL
If you enjoy the project or find it useful , any crypto donations are welcomed @ wallet address : 0xbc75Ca2Fde6D0FE1d2bA9f6b4a39f10Eb151dd86 or via paystring : calin.damian.crypto$paystring.crypto.com