-
Notifications
You must be signed in to change notification settings - Fork 42
/
Copy pathcreate_snomed_tables.sql
233 lines (213 loc) · 10.2 KB
/
create_snomed_tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
-- Transact-SQL script to create table structure for SNOMED terminology tables
-- Copyright Chris Tillman 2018, licensed under GPL any version
-- Cannot drop database if you're already in it
USE master
GO
DROP DATABASE snomedct -- Avoid individual DROP TABLES
GO
CREATE DATABASE snomedct
GO
USE snomedct
CREATE TABLE sct2_concept (
id bigint NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_C_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_C_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_C_MODULEID DEFAULT 0,
definitionStatusId bigint NOT NULL CONSTRAINT DF_C_DEF_STATUSID DEFAULT 0,
CONSTRAINT PK_CONCEPT PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_description (
id bigint NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_D_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_D_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_D_MODULEID DEFAULT 0,
conceptId bigint NOT NULL ,
languageCode varchar (3) NOT NULL CONSTRAINT DF_D_LANG_CODE DEFAULT '',
typeId bigint NOT NULL CONSTRAINT DF_D_TYPE DEFAULT 0,
term varchar (255) NOT NULL CONSTRAINT DF_D_TERM DEFAULT '',
caseSignificanceId bigint NOT NULL CONSTRAINT DF_D_CASE_SIGNIF DEFAULT 0,
CONSTRAINT PK_SCT2_DESCRIPTION PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_identifier (
identifierSchemeId bigint NOT NULL ,
alternateIdentifier varchar (255) NOT NULL CONSTRAINT DF_I_ALT_ID DEFAULT '',
effectiveTime int NOT NULL CONSTRAINT DF_I_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_I_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_I_MODULEID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_I_REF_COMP DEFAULT 0,
CONSTRAINT PK_SCT2_IDENTIFIER PRIMARY KEY (identifierSchemeId,alternateIdentifier,effectiveTime)
)
GO
CREATE TABLE sct2_relationship (
id bigint NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_R_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_R_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_R_MODULEID DEFAULT 0,
sourceId bigint NOT NULL CONSTRAINT DF_R_SOURCE DEFAULT 0,
destinationId bigint NOT NULL CONSTRAINT DF_R_DEST DEFAULT 0,
relationshipGroup int NOT NULL CONSTRAINT DF_R_GROUP DEFAULT 0,
typeId bigint NOT NULL CONSTRAINT DF_R_TYPE DEFAULT 0,
characteristictypeId bigint NOT NULL CONSTRAINT DF_R_CHAR_TYPE DEFAULT 0,
modifierId bigint NOT NULL CONSTRAINT DF_R_MODIFIER DEFAULT 0,
CONSTRAINT PK_RELATIONSHIP PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_statedrelationship (
id bigint NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_SR_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_SR_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_SR_MODULEID DEFAULT 0,
sourceId bigint NOT NULL CONSTRAINT DF_SR_SOURCE DEFAULT 0,
destinationId bigint NOT NULL CONSTRAINT DF_SR_DEST DEFAULT 0,
relationshipGroup int NOT NULL CONSTRAINT DF_SR_GROUP DEFAULT 0,
typeId bigint NOT NULL CONSTRAINT DF_SR_TYPE DEFAULT 0,
characteristictypeId bigint NOT NULL CONSTRAINT DF_SR_CHAR_TYPE DEFAULT 0,
modifierId bigint NOT NULL CONSTRAINT DF_SR_MODIFIER DEFAULT 0,
CONSTRAINT PK_STATED_RELATIONSHIP PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_textdefinition (
id bigint NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_TD_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_TD_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_TD_MODULEID DEFAULT 0,
conceptId bigint NOT NULL CONSTRAINT DF_TD_CONCEPT DEFAULT 0,
languageCode varchar (3) NOT NULL CONSTRAINT DF_TD_LANG_CODE DEFAULT '',
typeId bigint NOT NULL CONSTRAINT DF_TD_TYPE DEFAULT 0,
term varchar (4096) NOT NULL CONSTRAINT DF_TD_TERM DEFAULT '',
caseSignificanceId bigint NOT NULL CONSTRAINT DF_TD_CASE_SIGNIF DEFAULT 0,
CONSTRAINT PK_TEXT_DEFINITION PRIMARY KEY (id,effectiveTime)
)
GO
-- ICD Mapping table
-- tls_Icd10cmHumanReadableMap_US1000124_20180301.tsv
-- DROP TABLE tls_Icd10cmHumanReadableMap
-- GO
CREATE TABLE tls_Icd10cmHumanReadableMap (
id_in uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_TI_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_TI_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_TI_MODULEID DEFAULT 0,
refsetId bigint NOT NULL CONSTRAINT DF_TI_REFSET DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_TI_REF_COMP DEFAULT 0,
referencedComponentName varchar (512) NOT NULL CONSTRAINT DF_REF_COMP_NAME DEFAULT '',
mapGroup tinyint NOT NULL CONSTRAINT DF_TI_MAPGRP DEFAULT 0,
mapPriority tinyint NOT NULL CONSTRAINT DF_TI_MAPPRI DEFAULT 0,
mapRule varchar (512) NOT NULL CONSTRAINT DF_TI_MAPRULE DEFAULT '',
mapAdvice varchar (512) NOT NULL CONSTRAINT DF_TI_MAPADVICE DEFAULT '',
mapTarget varchar (10) NOT NULL CONSTRAINT DF_TI_MAPTARGET DEFAULT '',
mapTargetName varchar (255) NOT NULL CONSTRAINT DF_TI_MAP_TRG_NAME DEFAULT '',
correlationId bigint NOT NULL CONSTRAINT DF_TI_CORR_ID DEFAULT 0,
mapCategoryId bigint NOT NULL CONSTRAINT DF_TI_CATEGORY_ID DEFAULT 0,
mapCategoryName varchar (255) NOT NULL CONSTRAINT DF_TI_CATEGORY_NAME DEFAULT '',
CONSTRAINT PK_TLS_ICD PRIMARY KEY (id_in,effectiveTime)
)
GO
-- I didn't do the refsets, the file names don't quite correspond to the
-- table names from the original MYSQL script ... probably have changed
/*
CREATE TABLE sct2_refset_c (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_C_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_C_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_C_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_C_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_C_REF_COMP DEFAULT 0,
attribute1 bigint NOT NULL CONSTRAINT DF_RS_C_ATTR1 DEFAULT 0,
CONSTRAINT PK_REFSET_C PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_REF_COMP DEFAULT 0,
CONSTRAINT PK_REFSET PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset_iissscc (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_I_CC_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_I_CC_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_I_CC_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_I_CC_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_I_CC_REF_COMP DEFAULT 0,
attribute1 int NOT NULL CONSTRAINT DF_RS_I_CC_ATTR1 DEFAULT 0,
attribute2 int NOT NULL CONSTRAINT DF_RS_I_CC_ATTR2 DEFAULT 0,
attribute3 varchar (255) NOT NULL CONSTRAINT DF_RS_I_CC_ATTR3 DEFAULT '',
attribute4 varchar (255) NOT NULL CONSTRAINT DF_RS_I_CC_ATTR4 DEFAULT '',
attribute5 varchar (255) NOT NULL CONSTRAINT DF_RS_I_CC_ATTR5 DEFAULT '',
attribute6 bigint NOT NULL CONSTRAINT DF_RS_I_CC_ATTR6 DEFAULT 0,
attribute7 bigint NOT NULL CONSTRAINT DF_RS_I_CC_ATTR7 DEFAULT 0,
CONSTRAINT PK_REFSET_IISSSCC PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset_iisssc (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_I_C_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_I_C_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_I_C_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_I_C_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_I_C_REF_COMP DEFAULT 0,
attribute1 int NOT NULL CONSTRAINT DF_RS_I_C_ATTR1 DEFAULT 0,
attribute2 int NOT NULL CONSTRAINT DF_RS_I_C_ATTR2 DEFAULT 0,
attribute3 varchar (255) NOT NULL CONSTRAINT DF_RS_I_C_ATTR3 DEFAULT '',
attribute4 varchar (255) NOT NULL CONSTRAINT DF_RS_I_C_ATTR4 DEFAULT '',
attribute5 varchar (255) NOT NULL CONSTRAINT DF_RS_I_C_ATTR5 DEFAULT '',
attribute6 bigint NOT NULL CONSTRAINT DF_RS_I_C_ATTR6 DEFAULT 0,
CONSTRAINT PK_REFSET_IISSSC PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset_s (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_S_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_S_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_S_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_S_RS DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_S_REF_COMP DEFAULT 0,
attribute1 varchar (255) NOT NULL CONSTRAINT DF_RS_S_ATTR1 DEFAULT '',
CONSTRAINT PK_REFSET_S PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset_cci (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_CCI_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_CCI_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_CCI_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_CCI_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_CCI_REF_COMP DEFAULT 0,
attribute1 bigint NOT NULL CONSTRAINT DF_RS_CCI_ATTR1 DEFAULT 0,
attribute2 bigint NOT NULL CONSTRAINT DF_RS_CCI_ATTR2 DEFAULT 0,
attribute3 int NOT NULL CONSTRAINT DF_RS_CCI_ATTR3 DEFAULT 0,
CONSTRAINT PK_REFSET_CCI PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset_ci (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_CI_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_CI_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_CI_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_CI_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_CI_REF_COMP DEFAULT 0,
attribute1 bigint NOT NULL CONSTRAINT DF_RS_CI_ATTR1 DEFAULT 0,
attribute2 int NOT NULL CONSTRAINT DF_RS_CI_ATTR2 DEFAULT 0,
CONSTRAINT PK_REFSET_CI PRIMARY KEY (id,effectiveTime)
)
GO
CREATE TABLE sct2_refset_ss (
id uniqueidentifier NOT NULL ,
effectiveTime int NOT NULL CONSTRAINT DF_RS_SS_EFF_TIME DEFAULT 0,
active tinyint NOT NULL CONSTRAINT DF_RS_SS_ACTIVE DEFAULT 0,
moduleId bigint NOT NULL CONSTRAINT DF_RS_SS_MODULEID DEFAULT 0,
refSetId bigint NOT NULL CONSTRAINT DF_RS_SS_RS_ID DEFAULT 0,
referencedComponentId bigint NOT NULL CONSTRAINT DF_RS_SS_REF_COMP DEFAULT 0,
attribute1 varchar(255) NOT NULL CONSTRAINT DF_RS_SS_ATTR1 DEFAULT '',
attribute2 varchar(255) NOT NULL CONSTRAINT DF_RS_SS_ATTR2 DEFAULT '',
CONSTRAINT PK_REFSET_SS PRIMARY KEY (id , effectiveTime)
)
GO
*/