Database Constraints

Owned by Yaw Nti-Addae
Jun 26, 2019
1 min read

All primary table keys are unique by default and therefore, not individually marked within each table.

Table

Required? 

Column Name

Constraint Type

Referenced Column

Comments

cvgroup

Y

cvgroup_id




Y

name

Unique

ON name, type



definition




Y

type

Unique

ON name, type

Combination of name and type is unique

props




platform

Y

platform_id




Y

name

UniqueOn NameRemove platform type

Y

code





description





created_by





created_date





modified_by





modified_date




Y

status




Y

type_id





props




dnasample

Y

dnasample_id




Y

name

 Unique On name, project_id, num

code





platename





num

 Unique On name, project_id, num



well_row





well_col




Y

project_id

 Unique On name, project_id, num 

Y

germplasm_id





created_by





created_date





modified_by





modified_date




Y

status





props




germplasm

Y

germplasm_id





name





external_code





species_id





type_id





created_by





created_date





modified_by





modified_date




Y

status





code





props




cv

Y

cv_id




Y

term

Unique

ON term, cvgroup_id


Y

definition




Y

rank




Y

cvgroup_id

Unique

ON term, cvgroup_id



abbreviation





dbxref_id




Y

status





props




display

Y

display_id




Y

table_name





column_name





display_name





created_by





created_date





modified_by





modified_date





rank




project

Y

project_id




Y

name

Unique

ON pi_contact, name



code





description




Y

pi_contact

Unique

ON pi_contact, name



created_by





created_date





modified_by





modified_date




Y

status





props




protocol

Y

protocol_id




Y

name

Unique

On platform_id, name

description





type_id





platform_id

Unique

On platform_id, name

props





created_by





created_date





modified_by





modified_date





status




vendor_protocol

Y

vendor_protocol_id





name




Y

vendor_id

Unique

On vendor_id, protocol_id


Y

protocol_id

Unique

On vendor_id, protocol_id



status




dnarun

Y

dnarun_id




Y

experiment_id

Unique

On experiment_id, dnasample_id


Y

dnasample_id

Unique

On experiment_id, dnasample_id



name





code





dataset_dnarun_idx





props




mapset

Y

mapset_id




Y

name

Unique

On name, type_id

 


Y

code





description





reference_id




Y

type_id

Unique

On name, type_id



created_by





created_date





modified_by





modified_date




Y

status





props




linkage_group

Y

linkage_group_id




Y

name

Unique

On name, map_id


Y

start




Y

stop




Y

map_id

Unique

On name, map_id



created_by





created_date





modified_by





modified_date




marker_linkage_group

Y

marker_linkage_group_id




Y

marker_id





start





stop




Y

linkage_group_id














dataset

Y

dataset_id




Y

experiment_id

Unique

On experiment_id, callinganalysis_id, type_id, name


Y

callinganalysis_id

Unique

On experiment_id, callinganalysis_id, type_id, name



analyses





data_table





data_file





quality_table





quality_file





scores





created_by





created_date





modified_by





modified_date





status





type_id

Unique

On experiment_id, callinganalysis_id, type_id, name


Y

name

Unique

On experiment_id, callinganalysis_id, type_id, name


analysis

Y

analysis_id





name





description




Y

type_id

Unique


program





programversion





algorithm





sourcename





sourceversion





sourceuri





reference_id





parameters





timeexecuted




Y

status





created_by





created_date





modified_by





modified_date




experiment

Y

experiment_id




Y

name

Unique

On name, project_id, vendor_protocol_id


Y

code




Y

project_id

Unique

On name, project_id, vendor_protocol_id



manifest_id





data_file



What is data_file? GBS experiment, linked to the original raw data file

created_by





created_date





modified_by





modified_date




Y

status




Y

vendor_protocol_id

Unique

On name, project_id, vendor_protocol_id


manifest

Y

manifest_id




Y

name

Unique

Y

code





file_path





created_by





created_date





modified_by





modified_date




dbxref

Y

dbxref_id



internal DB

Y

accession

Unique

ON accession, ver



ver

Unique

ON accession, ver



description





db_name





url





props




gobiiprop

Y

gobiiprop_id




Y

type_id

Unique

ON type_id, rank



value




Y

rank

Unique

ON type_id, rank


organization

Y

organization_id




Y

name

Unique

ON name



address





website





created_by





created_date





modified_by





modified_date




Y

status




role

Y

role_id




Y

role_name




Y

role_code





read_tables





write_tables




contact

Y

contact_id




Y

lastname




Y

firstname




Y

code




Y

email

Unique

ON email



roles





created_by





created_date





modified_by





modified_date





organization_id




marker_group






Y

marker_group_id




Y

name

Unique

On name, markers

Business rule: don't allow the same marker_group name associate with different sets of marker list.

code




Y

markers





germplasm_group





created_by





created_date





modified_by





modified_date




Y

status




reference

Y

reference_id




Y

name

Unique



Y

version



version must be part of the name and need to be combined eventually with name





file_path





created_by





created_date





modified_by





modified_date




marker

Y

marker_id




Y

platform_id

Unique

On name, platform_id



variant_id




Y

name

Unique

On name, platform_id



code





ref





alts





sequence





reference_id





primers





strand_id




Y

status





probsets





dataset_marker_idx





props





dataset_vendor_protocol




variant

Y

variant_id





code

Unique




created_by





created_date





modified_by





modified_date