Learned some SQL today

Had to skip out of TEDxPurdueU today.

Which sucked.

There was a problem with my code at work, a problem I really couldn't handle from the front row.

We create runs. Each run has many regions. Each region has one or more accessions. Each accession has a well identifier to tell what part of the region it is in, and barcodes, which are identifiers. Problem is, barcodes associate with accessions, while wells associate with accessions, regions and wells. So, if you are putting one accession into several regions and wells, in a format like this

1,123123,Barcode1,Well1
1,123124,Barcode2,Well2
1,123125,Barcode3,Well3
1,123126,Barcode1,Well4
2,123123,Barcode1,Well4
2,123124,Barcode2,Well3
2,123125,Barcode3,Well2
2,123126,Barcode4,Well1

You now have set the barcodes twice for accessions 123123-123-126. Not good at all.

I first thought "I can check to see if that barcode is set, and skip it if it is". Then I thought, I could add some SQL to ensure it can't write again. That looks  a bit like this:

ALTER TABLE accession_barcode ADD CONSTRAINT accession_barcode UNIQUE ( accession , barcode )

Makes me wonder why I didn't do that in the first place.