postgres_createChembl17Tables.sql

--DROP TABLE TARGET_DICTIONARY;
CREATE TABLE TARGET_DICTIONARY
   (	TID integer NOT NULL,
	TARGET_TYPE varchar(30),
	PREF_NAME varchar(200),
	TAX_ID integer,
	ORGANISM varchar(150),
	CHEMBL_ID varchar(20),
	SPECIES_GROUP_FLAG integer
  ) ;
  CREATE UNIQUE INDEX PK_TD_TID ON TARGET_DICTIONARY (TID)   ;


CREATE TABLE TARGET_COMPONENTS
   (	TID integer,
	COMPONENT_ID integer,
	TARGCOMP_ID integer NOT NULL,
	HOMOLOGUE integer
  ) ;
  CREATE UNIQUE INDEX PK_TARGCOMP_ID ON TARGET_COMPONENTS (TARGCOMP_ID)   ;

CREATE TABLE COMPONENT_CLASS
   (	COMPONENT_ID integer,
   	PROTEIN_CLASS_ID integer,
   	COMP_CLASS_ID integer NOT NULL
  ) ;
  CREATE UNIQUE INDEX PK_COMP_CLASS_ID ON COMPONENT_CLASS (COMP_CLASS_ID)   ;
  CREATE UNIQUE INDEX UK_COMP_CLASS ON COMPONENT_CLASS (COMP_CLASS_ID,PROTEIN_CLASS_ID)   ;

CREATE TABLE PROTEIN_FAMILY_CLASSIFICATION
   (    PROTEIN_CLASS_ID integer NOT NULL,
	PROTEIN_CLASS_DESC varchar(410),
	L1 varchar(50),
	L2 varchar(50),
	L3 varchar(50),
	L4 varchar(50),
	L5 varchar(50),
	L6 varchar(50),
	L7 varchar(50),
	L8 varchar(50)
  ) ;
  CREATE UNIQUE INDEX PROTEIN_CLASS_PK ON PROTEIN_FAMILY_CLASSIFICATION (PROTEIN_CLASS_ID)   ;
  CREATE UNIQUE INDEX UK_PROTCLASS_DESC ON PROTEIN_FAMILY_CLASSIFICATION (PROTEIN_CLASS_DESC)   ;
  CREATE UNIQUE INDEX UK_PROTCLASS_LEVELS ON PROTEIN_FAMILY_CLASSIFICATION (L1,L2,L3,L4,L5,L6,L7,L8)   ;