I’ve made quite a few mistakes. I will try to both highlight them here as well as fix them in the original posts if that applies.
This is a little out of order, because I haven’t posted about this table yet, but I created a table that referred to all of the other tables. I had not added any data to it, but realized I forgot a field. I added that field, but I didn’t like the order.
So the fix for this was to save any query that adds a table. Since I hadn’t added any data it was easy to drop the table, modify my CREATE query, and add the table again.
(A tip, not a fix, is to view the top 1000 rows on a table even if there is no data, just to get a sense for the layout.)
Another issue I had was I realized one of the first tables I made needed another field. I added the field, and I’m willing to accept where it is in the table. (I’m going to be using views and probably Powershell Scripts to get the data anyway.,) I messed up the DECIMAL data type when adding that field though. I messed up a lot of those.
The fix is to ALTER the table and the column:
ALTER TABLE Caliber
ALTER COLUMN MaxOAL DECIMAL(4,3);I seem to have gotten my left and right confused. This field now allows decimals up to 9.999 which is what I need. Originally I set it up as 999.9.
I also wanted that field to be required, but since it was null when added, I couldn’t do that. So I’m going back and updating the data manually to add the value.
UPDATE Caliber
SET MaxOAL = 2.810
where CaliberName = '.308 Win';Finally, same thing happened when I began considering the Primer table. The Primer is generally driven by the Caliber. (Sometimes a caliber will have both a Large and Small version, but not often enough to worry about.) So, in this case unfortunately I have a lot to fix. Fortunately ChatGPT planned it out for me;
First, add the PrimerType to Caliber:
ALTER TABLE Caliber
ADD PrimerType VARCHAR(50);Then update the table with the data. I found it easiest to just use the CaliberID rather than the name:
UPDATE Caliber
SET PrimerType = 'Small Pistol'
where CaliberID = 1;I found that if the primer type is the same across multiple IDs I could use OR to do them all at once and I didn’t need a single quote:
WHERE CaliberID = 1 or CaliberID = 3;Now dropping the Primer table was tough. There was a foreign key reference to it in the PublishedLoad table. I tried dropping the collumn from the table, and that also didn’t work. I had to get this command from ChatGPT that gave me all Constraints on the table:
SELECT fk.name AS ConstraintName
FROM sys.foreign_keys AS fk
JOIN sys.tables AS t ON fk.parent_object_id = t.object_id
WHERE t.name = 'PublishedLoad';
And then once I found it I ran this command:
ALTER TABLE PublishedLoad
DROP CONSTRAINT ConstraintName; -- Replace ConstraintName with the actual constraint name
I’m not going to act like I completely understood that, but for now I just needed to get it done and not understand it.
I was then able to drop the Primer table.
And then I dropped the PrimerID column from the PublishedLoad table. That was weird for me. On one hand, I just know the primer type by caliber. On the other, creating a good DB that will give complete output means making sure the data is there. (Garbage in, garbage out. Nothing in, nothing out.)
Then I remembered the data IS there. The PublishedLoad table references the Caliber table, the Powder table, and the Bullet table. The complete view will come later with joins and views. Again, I think I’m getting the point of SQL!
Last, I needed to remove some test data. Since I tend to use the word “test” any time I enter test data, that made it easy:
DELETE FROM Caliber
WHERE CaliberName LIKE '%Test%';