| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | ACTIVITY_ID | NUMBER(11,0) | NOT NULL | Unique ID for the activity row |
| FK | ASSAY_ID | NUMBER(9,0) | NOT NULL | Foreign key to the assays table (containing the assay description) |
| FK | DOC_ID | NUMBER(9,0) | | Foreign key to documents table (for quick lookup of publication details - can also link to documents through compound_records or assays table) |
| FK | RECORD_ID | NUMBER(9,0) | NOT NULL | Foreign key to the compound_records table (containing information on the compound tested) |
| FK | MOLREGNO | NUMBER(9,0) | | Foreign key to compounds table (for quick lookup of compound structure - can also link to compounds through compound_records table) |
| STANDARD_RELATION | VARCHAR2(50) | | Symbol constraining the activity value (e.g. >, <, =) |
| PUBLISHED_VALUE | NUMBER | | Datapoint value as it appears in the original publication. |
| PUBLISHED_UNITS | VARCHAR2(100) | | Units of measurement as they appear in the original publication |
| STANDARD_VALUE | NUMBER | | Same as PUBLISHED_VALUE but transformed to common units: e.g. mM concentrations converted to nM. |
| STANDARD_UNITS | VARCHAR2(100) | | Selected 'Standard' units for data type: e.g. concentrations are in nM. |
| STANDARD_FLAG | NUMBER(1,0) | | Shows whether the standardised columns have been curated/set (1) or just default to the published data (0). |
| STANDARD_TYPE | VARCHAR2(250) | | Standardised version of the published_activity_type (e.g. IC50 rather than Ic-50/Ic50/ic50/ic-50) |
| ACTIVITY_COMMENT | VARCHAR2(4000) | | Describes non-numeric activities i.e. 'Slighty active', 'Not determined' |
| PUBLISHED_TYPE | VARCHAR2(250) | | Type of end-point measurement: e.g. IC50, LD50, %inhibition etc, as it appears in the original publication |
| FK | DATA_VALIDITY_COMMENT | VARCHAR2(30) | | Comment reflecting whether the values for this activity measurement are likely to be correct - one of 'Manually validated' (checked original paper and value is correct), 'Potential author error' (value looks incorrect but is as reported in the original paper), 'Outside typical range' (value seems too high/low to be correct e.g., negative IC50 value), 'Non standard unit type' (units look incorrect for this activity type). |
| POTENTIAL_DUPLICATE | NUMBER(1,0) | | When set to 1, indicates that the value is likely to be a repeat citation of a value reported in a previous ChEMBL paper, rather than a new, independent measurement. Note: value of zero does not guarantee that the measurement is novel/independent though |
| PUBLISHED_RELATION | VARCHAR2(50) | | Symbol constraining the activity value (e.g. >, <, =), as it appears in the original publication |
| PCHEMBL_VALUE | NUMBER(4,2) | | Negative log of selected concentration-response activity values (IC50/EC50/XC50/AC50/Ki/Kd/Potency) |
| FK | BAO_ENDPOINT | VARCHAR2(11) | | ID for the corresponding result type in BioAssay Ontology (based on standard_type) |
| UO_UNITS | VARCHAR2(10) | | ID for the corresponding unit in Unit Ontology (based on standard_units) |
| QUDT_UNITS | VARCHAR2(70) | | ID for the corresponding unit in QUDT Ontology (based on standard_units) |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | STD_ACT_ID | NUMBER(9,0) | NOT NULL | Primary key. |
| UK | STANDARD_TYPE | VARCHAR2(250) | NOT NULL | The standard_type that other published_types in the activities table have been converted to. |
| DEFINITION | VARCHAR2(500) | | A description/definition of the standard_type. |
| UK | STANDARD_UNITS | VARCHAR2(100) | NOT NULL | The units that are applied to this standard_type and to which other published_units are converted. Note a standard_type may have more than one allowable standard_unit and therefore multiple rows in this table. |
| NORMAL_RANGE_MIN | NUMBER(24,12) | | The lowest value for this activity type that is likely to be genuine. This is only an approximation, so lower genuine values may exist, but it may be desirable to validate these before using them. For a given standard_type/units, values in the activities table below this threshold are flagged with a data_validity_comment of 'Outside typical range'. |
| NORMAL_RANGE_MAX | NUMBER(24,12) | | The highest value for this activity type that is likely to be genuine. This is only an approximation, so higher genuine values may exist, but it may be desirable to validate these before using them. For a given standard_type/units, values in the activities table above this threshold are flagged with a data_validity_comment of 'Outside typical range'. |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | ASSAY_ID | NUMBER(9,0) | NOT NULL | Unique ID for the assay |
| FK | DOC_ID | NUMBER(9,0) | NOT NULL | Foreign key to documents table |
| DESCRIPTION | VARCHAR2(4000) | | Description of the reported assay |
| FK | ASSAY_TYPE | VARCHAR2(1) | | Assay classification, e.g. B=Binding assay, A=ADME assay, F=Functional assay |
| ASSAY_TEST_TYPE | VARCHAR2(20) | | Type of assay system (i.e., in vivo or in vitro) |
| ASSAY_CATEGORY | VARCHAR2(20) | | screening, confirmatory (ie: dose-response), summary, panel or other. |
| ASSAY_ORGANISM | VARCHAR2(250) | | Name of the organism for the assay system (e.g., the organism, tissue or cell line in which an assay was performed). May differ from the target organism (e.g., for a human protein expressed in non-human cells, or pathogen-infected human cells). |
| ASSAY_TAX_ID | NUMBER(11,0) | | NCBI tax ID for the assay organism. |
| ASSAY_STRAIN | VARCHAR2(200) | | Name of specific strain of the assay organism used (where known) |
| ASSAY_TISSUE | VARCHAR2(100) | | Name of tissue used in the assay system (e.g., for tissue-based assays) or from which the assay system was derived (e.g., for cell/subcellular fraction-based assays). |
| ASSAY_CELL_TYPE | VARCHAR2(100) | | Name of cell type or cell line used in the assay system (e.g., for cell-based assays). |
| ASSAY_SUBCELLULAR_FRACTION | VARCHAR2(100) | | Name of subcellular fraction used in the assay system (e.g., microsomes, mitochondria). |
| FK | TID | NUMBER(9,0) | | Target identifier to which this assay has been mapped. Foreign key to target_dictionary. From ChEMBL_15 onwards, an assay will have only a single target assigned. |
| FK | RELATIONSHIP_TYPE | VARCHAR2(1) | | Flag indicating of the relationship between the reported target in the source document and the assigned target from TARGET_DICTIONARY. Foreign key to RELATIONSHIP_TYPE table. |
| FK | CONFIDENCE_SCORE | NUMBER(1,0) | | Confidence score, indicating how accurately the assigned target(s) represents the actually assay target. Foreign key to CONFIDENCE_SCORE table. 0 means uncurated/unassigned, 1 = low confidence to 9 = high confidence. |
| FK | CURATED_BY | VARCHAR2(32) | | Indicates the level of curation of the target assignment. Foreign key to curation_lookup table. |
| FK | SRC_ID | NUMBER(3,0) | NOT NULL | Foreign key to source table |
| SRC_ASSAY_ID | VARCHAR2(50) | | Identifier for the assay in the source database/deposition (e.g., pubchem AID) |
| FK,UK | CHEMBL_ID | VARCHAR2(20) | NOT NULL | ChEMBL identifier for this assay (for use on web interface etc) |
| FK | CELL_ID | NUMBER(9,0) | | Foreign key to cell dictionary. The cell type or cell line used in the assay |
| FK | BAO_FORMAT | VARCHAR2(11) | | ID for the corresponding format type in BioAssay Ontology (e.g., cell-based, biochemical, organism-based etc) |
| FK | TISSUE_ID | NUMBER(9,0) | | ID for the corresponding tissue/anatomy in Uberon. Foreign key to tissue_dictionary |
| FK | VARIANT_ID | NUMBER(9,0) | | Foreign key to variant_sequences table. Indicates the mutant/variant version of the target used in the assay (where known/applicable) |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| WHO_NAME | VARCHAR2(2000) | | WHO/INN name for the compound |
| LEVEL1 | VARCHAR2(10) | | First level of classification |
| LEVEL2 | VARCHAR2(10) | | Second level of classification |
| LEVEL3 | VARCHAR2(10) | | Third level of classification |
| LEVEL4 | VARCHAR2(10) | | Fourth level of classification |
| PK | LEVEL5 | VARCHAR2(10) | NOT NULL | Complete ATC code for compound |
| LEVEL1_DESCRIPTION | VARCHAR2(2000) | | Description of first level of classification |
| LEVEL2_DESCRIPTION | VARCHAR2(2000) | | Description of second level of classification |
| LEVEL3_DESCRIPTION | VARCHAR2(2000) | | Description of third level of classification |
| LEVEL4_DESCRIPTION | VARCHAR2(2000) | | Description of fourth level of classification |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | COMPONENT_ID | NUMBER(9,0) | NOT NULL | Primary key. Unique identifier for each of the molecular components of biotherapeutics in ChEMBL (e.g., antibody chains, recombinant proteins, synthetic peptides). |
| COMPONENT_TYPE | VARCHAR2(50) | NOT NULL | Type of molecular component (e.g., 'PROTEIN','DNA','RNA'). |
| DESCRIPTION | VARCHAR2(200) | | Description/name of molecular component. |
| SEQUENCE | CLOB | | Sequence of the biotherapeutic component. |
| SEQUENCE_MD5SUM | VARCHAR2(32) | | MD5 checksum of the sequence. |
| TAX_ID | NUMBER(11,0) | | NCBI tax ID for the species from which the sequence is derived. May be null for humanized monoclonal antibodies, synthetic peptides etc. |
| ORGANISM | VARCHAR2(150) | | Name of the species from which the sequence is derived. |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | CELL_ID | NUMBER(9,0) | NOT NULL | Primary key. Unique identifier for each cell line in the target_dictionary. |
| UK | CELL_NAME | VARCHAR2(50) | NOT NULL | Name of each cell line (as used in the target_dicitonary pref_name). |
| CELL_DESCRIPTION | VARCHAR2(200) | | Longer description (where available) of the cell line. |
| CELL_SOURCE_TISSUE | VARCHAR2(50) | | Tissue from which the cell line is derived, where known. |
| CELL_SOURCE_ORGANISM | VARCHAR2(150) | | Name of organism from which the cell line is derived. |
| UK | CELL_SOURCE_TAX_ID | NUMBER(11,0) | | NCBI tax ID of the organism from which the cell line is derived. |
| CLO_ID | VARCHAR2(11) | | ID for the corresponding cell line in Cell Line Ontology |
| EFO_ID | VARCHAR2(12) | | ID for the corresponding cell line in Experimental Factory Ontology |
| CELLOSAURUS_ID | VARCHAR2(15) | | ID for the corresponding cell line in Cellosaurus Ontology |
| CL_LINCS_ID | VARCHAR2(8) | | Cell ID used in LINCS (Library of Integrated Network-based Cellular Signatures) |
| FK,UK | CHEMBL_ID | VARCHAR2(20) | | ChEMBL identifier for the cell (used in web interface etc) |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | COMPONENT_ID | NUMBER(9,0) | NOT NULL | Primary key. Unique identifier for the component. |
| COMPONENT_TYPE | VARCHAR2(50) | | Type of molecular component represented (e.g., 'PROTEIN','DNA','RNA'). |
| UK | ACCESSION | VARCHAR2(25) | | Accession for the sequence in the source database from which it was taken (e.g., UniProt accession for proteins). |
| SEQUENCE | CLOB | | A representative sequence for the molecular component, as given in the source sequence database (not necessarily the exact sequence used in the assay). |
| SEQUENCE_MD5SUM | VARCHAR2(32) | | MD5 checksum of the sequence. |
| DESCRIPTION | VARCHAR2(200) | | Description/name for the molecular component, usually taken from the source sequence database. |
| TAX_ID | NUMBER(11,0) | | NCBI tax ID for the sequence in the source database (i.e., species that the protein/nucleic acid sequence comes from). |
| ORGANISM | VARCHAR2(150) | | Name of the organism the sequence comes from. |
| DB_SOURCE | VARCHAR2(25) | | The name of the source sequence database from which sequences/accessions are taken. For UniProt proteins, this field indicates whether the sequence is from SWISS-PROT or TREMBL. |
| DB_VERSION | VARCHAR2(10) | | The version of the source sequence database from which sequences/accession were last updated. |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK,FK | MOLREGNO | NUMBER(9,0) | NOT NULL | Foreign key to compounds table (compound structure) |
| MW_FREEBASE | NUMBER(9,2) | | Molecular weight of parent compound |
| ALOGP | NUMBER(9,2) | | Calculated ALogP |
| HBA | NUMBER(3,0) | | Number hydrogen bond acceptors |
| HBD | NUMBER(3,0) | | Number hydrogen bond donors |
| PSA | NUMBER(9,2) | | Polar surface area |
| RTB | NUMBER(3,0) | | Number rotatable bonds |
| RO3_PASS | VARCHAR2(3) | | Indicates whether the compound passes the rule-of-three (mw < 300, logP < 3 etc) |
| NUM_RO5_VIOLATIONS | NUMBER(1,0) | | Number of violations of Lipinski's rule-of-five, using HBA and HBD definitions |
| ACD_MOST_APKA | NUMBER(9,2) | | The most acidic pKa calculated using ACDlabs v12.01 |
| ACD_MOST_BPKA | NUMBER(9,2) | | The most basic pKa calculated using ACDlabs v12.01 |
| ACD_LOGP | NUMBER(9,2) | | The calculated octanol/water partition coefficient using ACDlabs v12.01 |
| ACD_LOGD | NUMBER(9,2) | | The calculated octanol/water distribution coefficient at pH7.4 using ACDlabs v12.01 |
| MOLECULAR_SPECIES | VARCHAR2(50) | | Indicates whether the compound is an acid/base/neutral |
| FULL_MWT | NUMBER(9,2) | | Molecular weight of the full compound including any salts |
| AROMATIC_RINGS | NUMBER(3,0) | | Number of aromatic rings |
| HEAVY_ATOMS | NUMBER(3,0) | | Number of heavy (non-hydrogen) atoms |
| NUM_ALERTS | NUMBER(3,0) | | Number of structural alerts for QED calculation (as defined by Brenk et al., ChemMedChem 2008) |
| QED_WEIGHTED | NUMBER(3,2) | | Weighted quantitative estimate of drug likeness (as defined by Bickerton et al., Nature Chem 2012) |
| MW_MONOISOTOPIC | NUMBER(11,4) | | Monoisotopic parent molecular weight |
| FULL_MOLFORMULA | VARCHAR2(100) | | Molecular formula for the full compound (including any salt) |
| HBA_LIPINSKI | NUMBER(3,0) | | Number of hydrogen bond acceptors calculated according to Lipinski's original rules (i.e., N + O count)) |
| HBD_LIPINSKI | NUMBER(3,0) | | Number of hydrogen bond donors calculated according to Lipinski's original rules (i.e., NH + OH count) |
| NUM_LIPINSKI_RO5_VIOLATIONS | NUMBER(1,0) | | Number of violations of Lipinski's rule of five using HBA_LIPINSKI and HBD_LIPINSKI counts |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | DOC_ID | NUMBER(9,0) | NOT NULL | Unique ID for the document |
| JOURNAL | VARCHAR2(50) | | Abbreviated journal name for an article |
| YEAR | NUMBER(4,0) | | Year of journal article publication |
| VOLUME | VARCHAR2(50) | | Volume of journal article |
| ISSUE | VARCHAR2(50) | | Issue of journal article |
| FIRST_PAGE | VARCHAR2(50) | | First page number of journal article |
| LAST_PAGE | VARCHAR2(50) | | Last page number of journal article |
| PUBMED_ID | NUMBER(11,0) | | NIH pubmed record ID, where available |
| DOI | VARCHAR2(100) | | Digital object identifier for this reference |
| FK,UK | CHEMBL_ID | VARCHAR2(20) | NOT NULL | ChEMBL identifier for this document (for use on web interface etc) |
| TITLE | VARCHAR2(500) | | Document title (e.g., Publication title or description of dataset) |
| DOC_TYPE | VARCHAR2(50) | NOT NULL | Type of the document (e.g., Publication, Deposited dataset) |
| AUTHORS | VARCHAR2(4000) | | For a deposited dataset, the authors carrying out the screening and/or submitting the dataset. |
| ABSTRACT | CLOB | | For a deposited dataset, a brief description of the dataset. |
| PATENT_ID | VARCHAR2(20) | | Patent ID for this document |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | MEC_ID | NUMBER(9,0) | NOT NULL | Primary key for each drug mechanism of action |
| FK | RECORD_ID | NUMBER(9,0) | NOT NULL | Record_id for the drug (foreign key to compound_records table) |
| FK | MOLREGNO | NUMBER(9,0) | | Molregno for the drug (foreign key to molecule_dictionary table) |
| MECHANISM_OF_ACTION | VARCHAR2(250) | | Description of the mechanism of action e.g., 'Phosphodiesterase 5 inhibitor' |
| FK | TID | NUMBER(9,0) | | Target associated with this mechanism of action (foreign key to target_dictionary table) |
| FK | SITE_ID | NUMBER(9,0) | | Binding site for the drug within the target (where known) - foreign key to binding_sites table |
| FK | ACTION_TYPE | VARCHAR2(50) | | Type of action of the drug on the target e.g., agonist/antagonist etc (foreign key to action_type table) |
| DIRECT_INTERACTION | NUMBER(1,0) | | Flag to show whether the molecule is believed to interact directly with the target (1 = yes, 0 = no) |
| MOLECULAR_MECHANISM | NUMBER(1,0) | | Flag to show whether the mechanism of action describes the molecular target of the drug, rather than a higher-level physiological mechanism e.g., vasodilator (1 = yes, 0 = no) |
| DISEASE_EFFICACY | NUMBER(1,0) | | Flag to show whether the target assigned is believed to play a role in the efficacy of the drug in the indication(s) for which it is approved (1 = yes, 0 = no) |
| MECHANISM_COMMENT | VARCHAR2(500) | | Additional comments regarding the mechanism of action |
| SELECTIVITY_COMMENT | VARCHAR2(100) | | Additional comments regarding the selectivity of the drug |
| BINDING_SITE_COMMENT | VARCHAR2(100) | | Additional comments regarding the binding site of the drug |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | FRAC_CLASS_ID | NUMBER(9,0) | NOT NULL | Unique numeric primary key for each level5 code |
| ACTIVE_INGREDIENT | VARCHAR2(500) | NOT NULL | Name of active ingredient (fungicide) classified by FRAC |
| LEVEL1 | VARCHAR2(2) | NOT NULL | Mechanism of action code assigned by FRAC |
| LEVEL1_DESCRIPTION | VARCHAR2(2000) | NOT NULL | Description of mechanism of action |
| LEVEL2 | VARCHAR2(2) | NOT NULL | Target site code assigned by FRAC |
| LEVEL2_DESCRIPTION | VARCHAR2(2000) | | Description of target provided by FRAC |
| LEVEL3 | VARCHAR2(6) | NOT NULL | Group number assigned by FRAC |
| LEVEL3_DESCRIPTION | VARCHAR2(2000) | | Description of group provided by FRAC |
| LEVEL4 | VARCHAR2(7) | NOT NULL | Number denoting the chemical group (number not assigned by FRAC) |
| LEVEL4_DESCRIPTION | VARCHAR2(2000) | | Chemical group name provided by FRAC |
| UK | LEVEL5 | VARCHAR2(8) | NOT NULL | A unique code assigned to each ingredient (based on the level 1-4 FRAC classification, but not assigned by IRAC) |
| FRAC_CODE | VARCHAR2(4) | NOT NULL | The official FRAC classification code for the ingredient |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | HRAC_CLASS_ID | NUMBER(9,0) | NOT NULL | Unique numeric primary key for each level3 code |
| ACTIVE_INGREDIENT | VARCHAR2(500) | NOT NULL | Name of active ingredient (herbicide) classified by HRAC |
| LEVEL1 | VARCHAR2(2) | NOT NULL | HRAC group code - denoting mechanism of action of herbicide |
| LEVEL1_DESCRIPTION | VARCHAR2(2000) | NOT NULL | Description of mechanism of action provided by HRAC |
| LEVEL2 | VARCHAR2(3) | NOT NULL | Indicates a chemical family within a particular HRAC group (number not assigned by HRAC) |
| LEVEL2_DESCRIPTION | VARCHAR2(2000) | | Description of chemical family provided by HRAC |
| UK | LEVEL3 | VARCHAR2(5) | NOT NULL | A unique code assigned to each ingredient (based on the level 1 and 2 HRAC classification, but not assigned by HRAC) |
| HRAC_CODE | VARCHAR2(2) | NOT NULL | The official HRAC classification code for the ingredient |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | IRAC_CLASS_ID | NUMBER(9,0) | NOT NULL | Unique numeric primary key for each level4 code |
| ACTIVE_INGREDIENT | VARCHAR2(500) | NOT NULL | Name of active ingredient (insecticide) classified by IRAC |
| LEVEL1 | VARCHAR2(1) | NOT NULL | Class of action e.g., nerve action, energy metabolism (code not assigned by IRAC) |
| LEVEL1_DESCRIPTION | VARCHAR2(2000) | NOT NULL | Description of class of action, as provided by IRAC |
| LEVEL2 | VARCHAR2(3) | NOT NULL | IRAC main group code denoting primary site/mechanism of action |
| LEVEL2_DESCRIPTION | VARCHAR2(2000) | NOT NULL | Description of site/mechanism of action provided by IRAC |
| LEVEL3 | VARCHAR2(6) | NOT NULL | IRAC sub-group code denoting chemical class of insecticide |
| LEVEL3_DESCRIPTION | VARCHAR2(2000) | NOT NULL | Description of chemical class or exemplifying ingredient provided by IRAC |
| UK | LEVEL4 | VARCHAR2(8) | NOT NULL | A unique code assigned to each ingredient (based on the level 1, 2 and 3 IRAC classification, but not assigned by IRAC) |
| IRAC_CODE | VARCHAR2(3) | NOT NULL | The official IRAC classification code for the ingredient |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | MET_ID | NUMBER(9,0) | NOT NULL | Primary key |
| FK,UK | DRUG_RECORD_ID | NUMBER(9,0) | | Foreign key to compound_records. Record representing the drug or other compound for which metabolism is being studied (may not be the same as the substrate being measured) |
| FK,UK | SUBSTRATE_RECORD_ID | NUMBER(9,0) | | Foreign key to compound_records. Record representing the compound that is the subject of metabolism |
| FK,UK | METABOLITE_RECORD_ID | NUMBER(9,0) | | Foreign key to compound_records. Record representing the compound that is the result of metabolism |
| UK | PATHWAY_ID | NUMBER(9,0) | | Identifier for the metabolic scheme/pathway (may be multiple pathways from one source document) |
| PATHWAY_KEY | VARCHAR2(50) | | Link to original source indicating where the pathway information was found (e.g., Figure 1, page 23) |
| UK | ENZYME_NAME | VARCHAR2(200) | | Name of the enzyme responsible for the metabolic conversion |
| FK,UK | ENZYME_TID | NUMBER(9,0) | | Foreign key to target_dictionary. TID for the enzyme responsible for the metabolic conversion |
| MET_CONVERSION | VARCHAR2(200) | | Description of the metabolic conversion |
| ORGANISM | VARCHAR2(100) | | Organism in which this metabolic reaction occurs |
| UK | TAX_ID | NUMBER(11,0) | | NCBI Tax ID for the organism in which this metabolic reaction occurs |
| MET_COMMENT | VARCHAR2(1000) | | Additional information regarding the metabolism (e.g., organ system, conditions under which observed, activity of metabolites) |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | MOLREGNO | NUMBER(9,0) | NOT NULL | Internal Primary Key for the molecule |
| PREF_NAME | VARCHAR2(255) | | Preferred name for the molecule |
| FK,UK | CHEMBL_ID | VARCHAR2(20) | NOT NULL | ChEMBL identifier for this compound (for use on web interface etc) |
| MAX_PHASE | NUMBER(1,0) | NOT NULL | Maximum phase of development reached for the compound (4 = approved). Null where max phase has not yet been assigned. |
| THERAPEUTIC_FLAG | NUMBER(1,0) | NOT NULL | Indicates that a drug has a therapeutic application (as opposed to e.g., an imaging agent, additive etc). |
| DOSED_INGREDIENT | NUMBER(1,0) | NOT NULL | Indicates that the drug is dosed in this form (e.g., a particular salt) |
| STRUCTURE_TYPE | VARCHAR2(10) | NOT NULL | Indications whether the molecule has a small molecule structure or a protein sequence (MOL indicates an entry in the compound_structures table, SEQ indications an entry in the protein_therapeutics table, NONE indicates an entry in neither table, e.g., structure unknown) |
| CHEBI_PAR_ID | NUMBER(9,0) | | Preferred ChEBI ID for the compound (where different from assigned) |
| MOLECULE_TYPE | VARCHAR2(30) | | Type of molecule (Small molecule, Protein, Antibody, Oligosaccharide, Oligonucleotide, Cell, Unknown) |
| FIRST_APPROVAL | NUMBER(4,0) | | Earliest known approval year for the molecule |
| ORAL | NUMBER(1,0) | NOT NULL | Indicates whether the drug is known to be administered orally. |
| PARENTERAL | NUMBER(1,0) | NOT NULL | Indicates whether the drug is known to be administered parenterally |
| TOPICAL | NUMBER(1,0) | NOT NULL | Indicates whether the drug is known to be administered topically. |
| BLACK_BOX_WARNING | NUMBER(1,0) | NOT NULL | Indicates that the drug has a black box warning |
| NATURAL_PRODUCT | NUMBER(1,0) | NOT NULL | Indicates whether the compound is natural product-derived (currently curated only for drugs) |
| FIRST_IN_CLASS | NUMBER(1,0) | NOT NULL | Indicates whether this is known to be the first compound of its class (e.g., acting on a particular target). |
| CHIRALITY | NUMBER(1,0) | NOT NULL | Shows whether a drug is dosed as a racemic mixture (0), single stereoisomer (1) or is an achiral molecule (2) |
| PRODRUG | NUMBER(1,0) | NOT NULL | Indicates that the molecule is a pro-drug (see molecule hierarchy for active component, where known) |
| INORGANIC_FLAG | NUMBER(1,0) | NOT NULL | Indicates whether the molecule is inorganic (i.e., containing only metal atoms and <2 carbon atoms) |
| USAN_YEAR | NUMBER(4,0) | | The year in which the application for a USAN/INN name was made |
| AVAILABILITY_TYPE | NUMBER(1,0) | | The availability type for the drug (0 = discontinued, 1 = prescription only, 2 = over the counter) |
| USAN_STEM | VARCHAR2(50) | | Where the compound has been assigned a USAN name, this indicates the stem, as described in the USAN_STEM table. |
| POLYMER_FLAG | NUMBER(1,0) | | Indicates whether a molecule is a small molecule polymer (e.g., polistyrex) |
| USAN_SUBSTEM | VARCHAR2(50) | | Where the compound has been assigned a USAN name, this indicates the substem |
| USAN_STEM_DEFINITION | VARCHAR2(1000) | | Definition of the USAN stem |
| INDICATION_CLASS | VARCHAR2(1000) | | Indication class(es) assigned to a drug in the USP dictionary |
| WITHDRAWN_FLAG | NUMBER(1,0) | NOT NULL | Flag indicating whether the drug has been withdrawn in at least one country (not necessarily in the US) |
| WITHDRAWN_YEAR | NUMBER(4,0) | | Year the drug was first withdrawn in any country |
| WITHDRAWN_COUNTRY | VARCHAR2(1000) | | List of countries/regions where the drug has been withdrawn |
| WITHDRAWN_REASON | VARCHAR2(1000) | | Reasons for withdrawl (e.g., safety) |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK,FK | MOLREGNO | NUMBER(9,0) | NOT NULL | Foreign key to compounds table. This field holds a list of all of the ChEMBL compounds with associated data (e.g., activity information, approved drugs). Parent compounds that are generated only by removing salts, and which do not themselves have any associated data will not appear here. |
| FK | PARENT_MOLREGNO | NUMBER(9,0) | | Represents parent compound of molregno in first field (i.e., generated by removing salts). Where molregno and parent_molregno are same, the initial ChEMBL compound did not contain a salt component, or else could not be further processed for various reasons (e.g., inorganic mixture). Compounds which are only generated by removing salts will appear in this field only. Those which, themselves, have any associated data (e.g., activity data) or are launched drugs will also appear in the molregno field. |
| FK | ACTIVE_MOLREGNO | NUMBER(9,0) | | Where a compound is a pro-drug, this represents the active metabolite of the 'dosed' compound given by parent_molregno. Where parent_molregno and active_molregno are the same, the compound is not currently known to be a pro-drug. |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | PROD_PAT_ID | NUMBER(9,0) | NOT NULL | Primary key |
| FK,UK | PRODUCT_ID | VARCHAR2(30) | NOT NULL | Foreign key to products table - FDA application number for the product |
| UK | PATENT_NO | VARCHAR2(11) | NOT NULL | Patent numbers as submitted by the applicant holder for patents covered by the statutory provisions |
| UK | PATENT_EXPIRE_DATE | DATE | NOT NULL | Date the patent expires as submitted by the applicant holder including applicable extensions |
| DRUG_SUBSTANCE_FLAG | NUMBER(1,0) | NOT NULL | Patents submitted on FDA Form 3542 and listed after August 18, 2003 may have a drug substance flag set to 1, indicating the sponsor submitted the patent as claiming the drug substance |
| DRUG_PRODUCT_FLAG | NUMBER(1,0) | NOT NULL | Patents submitted on FDA Form 3542 and listed after August 18, 2003 may have a drug product flag set to 1, indicating the sponsor submitted the patent as claiming the drug product |
| FK,UK | PATENT_USE_CODE | VARCHAR2(10) | | Code to designate a use patent that covers the approved indication or use of a drug product |
| DELIST_FLAG | NUMBER(1,0) | NOT NULL | Sponsor has requested patent be delisted if set to 1. This patent has remained listed because, under Section 505(j)(5)(D)(i) of the Act, a first applicant may retain eligibility for 180-day exclusivity based on a paragraph IV certification to this patent for a certain period. Applicants under Section 505(b)(2) are not required to certify to patents where this flag is set to 1 |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| DOSAGE_FORM | VARCHAR2(200) | | The dosage form of the product (e.g., tablet, capsule etc) |
| ROUTE | VARCHAR2(200) | | The administration route of the product (e.g., oral, injection etc) |
| TRADE_NAME | VARCHAR2(200) | | The trade name for the product |
| APPROVAL_DATE | DATE | | The FDA approval date for the product (not necessarily first approval of the active ingredient) |
| AD_TYPE | VARCHAR2(5) | | RX = prescription, OTC = over the counter, DISCN = discontinued |
| ORAL | NUMBER(1,0) | | Flag to show whether product is orally delivered |
| TOPICAL | NUMBER(1,0) | | Flag to show whether product is topically delivered |
| PARENTERAL | NUMBER(1,0) | | Flag to show whether product is parenterally delivered |
| BLACK_BOX_WARNING | NUMBER(1,0) | | Flag to show whether the product label has a black box warning |
| APPLICANT_FULL_NAME | VARCHAR2(200) | | Name of the company applying for FDA approval |
| INNOVATOR_COMPANY | NUMBER(1,0) | | Flag to show whether the applicant is the innovator of the product |
| PK | PRODUCT_ID | VARCHAR2(30) | NOT NULL | FDA application number for the product |
| NDA_TYPE | VARCHAR2(10) | | New Drug Application Type. The type of new drug application approval. New Drug Applications (NDA or innovator) are N. Abbreviated New Drug Applications (ANDA or generic) are A. |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | PROTEIN_CLASS_ID | NUMBER(9,0) | NOT NULL | Primary key. Unique identifier for each classification. |
| UK | PROTEIN_CLASS_DESC | VARCHAR2(810) | NOT NULL | Concatenated description of each classification for searching purposes etc. |
| UK | L1 | VARCHAR2(100) | NOT NULL | First level classification (e.g., Enzyme, Transporter, Ion Channel). |
| UK | L2 | VARCHAR2(100) | | Second level classification. |
| UK | L3 | VARCHAR2(100) | | Third level classification. |
| UK | L4 | VARCHAR2(100) | | Fourth level classification. |
| UK | L5 | VARCHAR2(100) | | Fifth level classification. |
| UK | L6 | VARCHAR2(100) | | Sixth level classification. |
| UK | L7 | VARCHAR2(100) | | Seventh level classification. |
| UK | L8 | VARCHAR2(100) | | Eighth level classification. |
| KEYS | COLUMN_NAME | DATA_TYPE | NULLABLE | COMMENT |
| PK | TID | NUMBER(9,0) | NOT NULL | Unique ID for the target |
| FK | TARGET_TYPE | VARCHAR2(30) | | Describes whether target is a protein, an organism, a tissue etc. Foreign key to TARGET_TYPE table. |
| PREF_NAME | VARCHAR2(200) | NOT NULL | Preferred target name: manually curated |
| TAX_ID | NUMBER(11,0) | | NCBI taxonomy id of target |
| ORGANISM | VARCHAR2(150) | | Source organism of molecuar target or tissue, or the target organism if compound activity is reported in an organism rather than a protein or tissue |
| FK,UK | CHEMBL_ID | VARCHAR2(20) | NOT NULL | ChEMBL identifier for this target (for use on web interface etc) |
| SPECIES_GROUP_FLAG | NUMBER(1,0) | NOT NULL | Flag to indicate whether the target represents a group of species, rather than an individual species (e.g., 'Bacterial DHFR'). Where set to 1, indicates that any associated target components will be a representative, rather than a comprehensive set. |