Adding primary keys to tables

[isedwards]

@smachua could you look through the Climsoft database for tables that don't currently have primary keys and suggest which fields can make up the primary keys? In many cases these already have unique indices.

We can then create a script with ALTER TABLE table_name ADD primary key (field1, field2, ...) commands as part of the next database update.

[smachua]

Ok @isedwards I will look through.

[isedwards]

Thank you Samuel

[isedwards]

Noting #1: DROP TABLE IF EXISTS abc, aws1, aws_elements, aws_stations, aws_test, ss, testing_aws, testing_aws1;

The table below lists every table in the Climsoft 4.1.1 schema that currently does not explicitly define a primary key. The "Candidate Primary Key" gives the current UNIQUE INDEX in cases where this could be explicitly made into a composite primary key.

For completeness, the third column compares this analysis to the attached primary keys summary:
Climsoft.Data.Model.Tables.and.Keys.pdf (lists 24 tables)

Table name	Candidate Primary Key	Comparison
aws_lsi	UNIQUE INDEX `identification` (`Cols`)
aws_lsi1	UNIQUE INDEX `identification` (`Cols`)
aws_malawi12	UNIQUE INDEX `identification` (`No`)
aws_rema1	UNIQUE INDEX `identification` (`Cols`)
aws_rwanda1	# no unique candidate: `Cols` INT(11) NULL DEFAULT '0'
aws_rwanda_rain	UNIQUE INDEX `identification` (`Cols`)
aws_toa5_bw1	UNIQUE INDEX `identification` (`Element_Abbreviation`) # `Cols` not suitable
bufr_crex_data	# no unique candidate: `nos` INT(11) NULL DEFAULT '0',
bufr_crex_master	# no unique candidate (table may contain duplicates)
ccitt	# no unique candidate (table may contain duplicates)
faultresolution	UNIQUE INDEX `solution` (`resolvedDatetime`, `associatedWith`)	✓
featuregeographicalposition	UNIQUE INDEX `FK_mysql_climsoft_db_v4_synopfeatureFeatureGeographicalPosition` (`belongsTo`, `observedOn`)	✓
instrumentinspection	UNIQUE INDEX `inspection` (`performedOn`, `inspectionDatetime`)	✓
observationfinal	UNIQUE INDEX `obsFinalIdentification` (`recordedFrom`, `describedBy`, `obsDatetime`)	add: obsLevel
observationinitial	UNIQUE INDEX `obsInitialIdentification` (`recordedFrom`, `describedBy`, `obsDatetime`, `qcStatus`, `acquisitionType`)	add: obsLevel
observationschedule	UNIQUE INDEX `scheduleIdentification` (`classifiedInto`, `startTime`, `endTime`)	✓
paperarchive	UNIQUE INDEX `paper_archive_identification` (`belongsTo`, `formDatetime`, `classifiedInto`)	✓
physicalfeature	UNIQUE INDEX `featureIdentification` (`associatedWith`, `beginDate`, `classifiedInto`, `description`)	✓
qcabslimits	UNIQUE INDEX `obsInitialIdentification` (`StationId`, `ElementId`, `Datetime`)
routinereporttransmission	UNIQUE INDEX `report` (`reportClass`, `reportDatetime`, `reportedFrom`)
seq_day	# no unique candidate: `dd` VARCHAR(50) NULL DEFAULT '0'
seq_hour	# no unique candidate: `hh` INT(11) NULL DEFAULT '0'
seq_leap_year	# no unique candidate: `yyyy` INT(11) NULL DEFAULT NULL
seq_month	# no unique candidate: `mm` VARCHAR(50) NULL DEFAULT '0'
seq_month_day_element	# no unique candidate (table may contain duplicates)
seq_month_day_element_leap_yr	# no unique candidate (table may contain duplicates)
seq_year	# no unique candidate: `yyyy` INT(11) NULL DEFAULT NULL
stationelement	UNIQUE INDEX `stationElementIdentification` (`recordedFrom`, `describedBy`, `recordedWith`, `beginDate`)	✓
stationidalias	UNIQUE INDEX `stationid_alias_identification` (`idAlias`)
stationlocationhistory	UNIQUE INDEX `history` (`belongsTo`, `openingDatetime`)	✓
stationqualifier	UNIQUE INDEX `stationid_qualifier_identification` (`qualifier`, `qualifierBeginDate`, `qualifierEndDate`, `belongsTo`)	✓
tdcf_indicators	# no unique candidate (table may contain duplicates)
tm_307073	# no unique candidate (table may contain duplicates)
tm_307080	# no unique candidate (table may contain duplicates)
tm_307081	# no unique candidate (table may contain duplicates)
tm_307082	# no unique candidate (table may contain duplicates)
tm_307083	# no unique candidate (table may contain duplicates)
tm_307084	# no unique candidate (table may contain duplicates)
tm_307086	# no unique candidate (table may contain duplicates)
tm_307089	# no unique candidate (table may contain duplicates)

[Steve-Palmer]

In the list of tables without primary keys, there are several named "aws_xxxxx" where the xxxxx looks like a country or group (Malawi, Rwanda, REMA). It looks as though these are either temporary tables created during the setup of station details or they are used in processing automatic weather station reports from the named country or group. Either way, these probably should not be in the distribution database.

[smachua]

@Steve-Palmer you're right about these tables. The "aws_xx.." tables are created at run time whenever a new AWS data structure is introduced in Climsoft operation. Those listed by @isedwards have been left as samples in the distribution database as a demonstration on how a new AWS data structure should be constructed.
There also tables for temporary use e.g. bufr_crex_data which is created to combine elements from a TDCF Template (i.e. tm_307xxxx) in use with those from bufr_crex_master to produce a BUFR or CREX message. And there many more temporary tables for different Climsoft operations not listed here.
I suggest that the Primary/Unique keys be of concern only to those tables that constitute the relational data model. I have compiled and attached them here.
Climsoft Data Model Tables and Keys.pdf