-
-
Save JeremyMorgan/5833666 to your computer and use it in GitHub Desktop.
CREATE TABLE [state]( | |
[stateID] [int] IDENTITY(1,1) NOT NULL, | |
[stateCode] [nchar](2) NOT NULL, | |
[stateName] [nvarchar](128) NOT NULL, | |
CONSTRAINT [PK_state] PRIMARY KEY CLUSTERED | |
( [stateID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) | |
ON [PRIMARY] | |
GO | |
INSERT into [state] values | |
('AK', 'Alaska'), | |
('AL', 'Alabama'), | |
('AZ', 'Arizona'), | |
('AR', 'Arkansas'), | |
('CA', 'California'), | |
('CO', 'Colorado'), | |
('CT', 'Connecticut'), | |
('DE', 'Delaware'), | |
('DC', 'District of Columbia'), | |
('FL', 'Florida'), | |
('GA', 'Georgia'), | |
('HI', 'Hawaii'), | |
('ID', 'Idaho'), | |
('IL', 'Illinois'), | |
('IN', 'Indiana'), | |
('IA', 'Iowa'), | |
('KS', 'Kansas'), | |
('KY', 'Kentucky'), | |
('LA', 'Louisiana'), | |
('ME', 'Maine'), | |
('MD', 'Maryland'), | |
('MA', 'Massachusetts'), | |
('MI', 'Michigan'), | |
('MN', 'Minnesota'), | |
('MS', 'Mississippi'), | |
('MO', 'Missouri'), | |
('MT', 'Montana'), | |
('NE', 'Nebraska'), | |
('NV', 'Nevada'), | |
('NH', 'New Hampshire'), | |
('NJ', 'New Jersey'), | |
('NM', 'New Mexico'), | |
('NY', 'New York'), | |
('NC', 'North Carolina'), | |
('ND', 'North Dakota'), | |
('OH', 'Ohio'), | |
('OK', 'Oklahoma'), | |
('OR', 'Oregon'), | |
('PA', 'Pennsylvania'), | |
('PR', 'Puerto Rico'), | |
('RI', 'Rhode Island'), | |
('SC', 'South Carolina'), | |
('SD', 'South Dakota'), | |
('TN', 'Tennessee'), | |
('TX', 'Texas'), | |
('UT', 'Utah'), | |
('VT', 'Vermont'), | |
('VA', 'Virginia'), | |
('WA', 'Washington'), | |
('WV', 'West Virginia'), | |
('WI', 'Wisconsin'), | |
('WY', 'Wyoming'); |
Thanks, but...Puerto Rico?
michalstanko the title is all 50 states. Puerto Rico is not a state. You could have added Puerto Rico yourself in less time than it took to write your comment.
the abreviation for Puerto Rico is PR (just in case anybody else wanted to know.)
lmao @FiestaUSA, that response
DC is not a state either, even though they wish they were.... but if we are talking shipping states we could also include the Virgin Islands.
Alabama (AL) is missing
If you are interested in data integrity, especially the kind that the DBMS provides, you should have a unique index on stateCode so that you can't possibly have two DCs (one is more than enough haha). Furthermore, stateCode could (and should IMHO) be the primary key. Why invent something when a natural key already exists.
Thanks, but...Puerto Rico?
PR is a US territory.
If you are interested in data integrity, especially the kind that the DBMS provides, you should have a unique index on stateCode so that you can't possibly have two DCs (one is more than enough haha). Furthermore, stateCode could (and should IMHO) be the primary key. Why invent something when a natural key already exists.
Good point. Likely a better design overall.
Thank you!
INSERT into [state] values ('AL', 'Alabama'),
('AK', 'Alaska'),
('AL', 'Alabama'),
('AZ', 'Arizona'),
Alabama (AL) is missing
Actually Alabama is twice.
That's so awesome, here I was dreading the entry and a very quick search led me to this. Thanks!
The reason for having an integer primary key is so that if the table is used for "states" beyond the USA (ie for states or provinces or regions), then there could be duplicates if just the 2 char code was used as the pk.
$states_abbr = array_flip(array("AL"=>"Alabama", "AK"=>"Alaska", "AZ"=>"Arizona", "AR"=>"Arkansas", "CA"=>"California", "CO"=>"Colorado", "CT"=>"Connecticut", "DE"=>"Delaware", "DC"=>"District of Columbia", "FL"=>"Florida", "GA"=>"Georgia", "HI"=>"Hawaii", "ID"=>"Idaho", "IL"=>"Illinois", "IN"=>"Indiana", "IA"=>"Iowa", "KS"=>"Kansas", "KY"=>"Kentucky", "LA"=>"Louisiana", "ME"=>"Maine", "MD"=>"Maryland", "MA"=>"Massachusetts", "MI"=>"Michigan", "MN"=>"Minnesota", "MS"=>"Mississippi", "MO"=>"Missouri", "MT"=>"Montana", "NE"=>"Nebraska", "NV"=>"Nevada", "NH"=>"New Hampshire", "NJ"=>"New Jersey", "NM"=>"New Mexico", "NY"=>"New York", "NC"=>"North Carolina", "ND"=>"North Dakota", "OH"=>"Ohio", "OK"=>"Oklahoma", "OR"=>"Oregon", "PA"=>"Pennsylvania", "RI"=>"Rhode Island", "SC"=>"South Carolina", "SD"=>"South Dakota", "TN"=>"Tennessee", "TX"=>"Texas", "UT"=>"Utah", "VT"=>"Vermont", "VA"=>"Virginia", "WA"=>"Washington", "WV"=>"West Virginia", "WI"=>"Wisconsin","WY"=>"Wyoming"));