1

I can't figure out the order my drop tables and create tables should be in. I'm creating a knockoff soccer registration database. Originally I was fine but wanted to make it more complex and since then I can't figure out the order everything is supposed to be in. I know this sounds dumb, but I'm still a student who is trying to create something on his own to learn more. Below is the order my drop tables and create tables are in right now along with my alter tables. I greatly appreciate your guys help!

-- --------------------------------------------------------------------------------
-- Drop Tables
-- --------------------------------------------------------------------------------
IF OBJECT_ID('TTeamPlayers') IS NOT NULL DROP TABLE TTeamPlayers
IF OBJECT_ID('TPlayers') IS NOT NULL DROP TABLE TPlayers
IF OBJECT_ID('TTeamCoaches') IS NOT NULL DROP TABLE TTeamCoaches
IF OBJECT_ID('TFields') IS NOT NULL DROP TABLE TFields
IF OBJECT_ID('TAgeGroups') IS NOT NULL DROP TABLE TAgeGroups
IF OBJECT_ID('TReferees') IS NOT NULL DROP TABLE TReferees
IF OBJECT_ID('TCoaches') IS NOT NULL DROP TABLE TCoaches
IF OBJECT_ID('TStates') IS NOT NULL DROP TABLE TStates
IF OBJECT_ID('TSockSizes') IS NOT NULL DROP TABLE TSockSizes
IF OBJECT_ID('TPantSizes') IS NOT NULL DROP TABLE TPantSizes
IF OBJECT_ID('TShirtSizes') IS NOT NULL DROP TABLE TShirtSizes
IF OBJECT_ID('TGenders') IS NOT NULL DROP TABLE TGenders
IF OBJECT_ID('TTeams') IS NOT NULL DROP TABLE TTeams
IF OBJECT_ID('Z_TeamPlayers') IS NOT NULL DROP TABLE Z_TeamPlayers
IF OBJECT_ID('Z_TeamCoaches') IS NOT NULL DROP TABLE Z_TeamCoaches
IF OBJECT_ID('Z_Teams') IS NOT NULL DROP TABLE Z_Teams
IF OBJECT_ID('Z_Players') IS NOT NULL DROP TABLE Z_Players
IF OBJECT_ID('Z_Coaches') IS NOT NULL DROP TABLE Z_Coaches
IF OBJECT_ID('TUsers') IS NOT NULL DROP TABLE TUsers
IF OBJECT_ID('TRoles') IS NOT NULL DROP TABLE TRoles
IF OBJECT_ID('TLogins') IS NOT NULL DROP TABLE TLogins
IF OBJECT_ID('TFieldGames') IS NOT NULL DROP TABLE TFieldGames
------------------------------------------------------------------------------------
-- create tables
------------------------------------------------------------------------------------
create table TTeams
(
    intTeamID       INTEGER         NOT NULL
    ,strTeam        VARCHAR(50)     NOT NULL
    ,CONSTRAINT TTeams_PK PRIMARY KEY ( intTeamID )
)

CREATE TABLE TGenders
(
    intGenderID     INTEGER         NOT NULL
    ,strGender      VARCHAR(10)     NOT NULL
    ,CONSTRAINT TGenders_PK PRIMARY KEY ( intGenderID )
)

CREATE TABLE TShirtSizes
(
    intShirtSizeID  INTEGER         NOT NULL
    ,strShirtSize   VARCHAR(50)     NOT NULL
    ,CONSTRAINT TShirtSizes_PK PRIMARY KEY ( intShirtSizeID )
)

CREATE TABLE TPantSizes
(
    intPantSizeID   INTEGER         NOT NULL
    ,strPantSize    VARCHAR(50)     NOT NULL
    ,CONSTRAINT TPantSizes_PK PRIMARY KEY ( intPantSizeID )
)

CREATE TABLE TSockSizes
(
    intSockSizeID   INTEGER         NOT NULL
    ,strSockSize    VARCHAR(50)     NOT NULL
    ,CONSTRAINT TSockSizes_PK PRIMARY KEY ( intSockSizeID )
)

CREATE TABLE TStates 
(
    intStateID      INTEGER         NOT NULL
    ,strState       VARCHAR(50)     NOT NULL
    ,CONSTRAINT TStates_PK PRIMARY KEY ( intStateID )
)

CREATE TABLE TReferees
(
    intRefereeID    INTEGER         NOT NULL
    ,strFirstName   VARCHAR(50)     NOT NULL
    ,strMiddleName  VARCHAR(50)     NOT NULL
    ,strLastName    VARCHAR(50)     NOT NULL
    ,strEmail       VARCHAR(50)     NOT NULL
    ,dtmDateOfBirth DATE            NOT NULL
    ,strCity        VARCHAR(50)     NOT NULL
    ,intStateID     INTEGER         NOT NULL
    ,strAddress     varchar(50)     not null
    ,strZip         varchar(50)     not null
    ,strPhoneNumber VARCHAR(50)     NOT NULL
    ,CONSTRAINT TReferees_PK PRIMARY KEY ( intRefereeID )
)

CREATE TABLE TAgeGroups
(
    intAgeGroupID   INTEGER         NOT NULL
    ,strAge         VARCHAR(10)     NOT NULL
    ,CONSTRAINT TAgeGroups_PK PRIMARY KEY ( intAgeGroupID )
)

CREATE TABLE TFields
(
    intFieldID      INTEGER         NOT NULL
    ,strFieldName   VARCHAR(50)     NOT NULL
    ,CONSTRAINT TFields_PK PRIMARY KEY ( intFieldID )
)

CREATE TABLE TCoaches
(
    intCoachID      INTEGER         NOT NULL
    ,strFirstName   VARCHAR(50)     NOT NULL
    ,strMiddleName  varchar(50)     not null
    ,strLastName    VARCHAR(50)     NOT NULL
    ,intShirtID     INTEGER         NOT NULL
    ,dtmDateOfBirth DATE            NOT NULL
    ,strCity        Varchar(50)     not null
    ,intStateID     integer         not null
    ,strPhoneNumber varchar(50)     not null
    ,strEmail       VARCHAR(50)     NOT NULL
    ,strAddress     varchar(50)     not null
    ,strZip         varchar(50)     not null
    ,CONSTRAINT TCoaches_PK PRIMARY KEY ( intCoachID )
)

CREATE TABLE TTeamCoaches
(
    intTeamCoachID  INTEGER         NOT NULL
    ,intTeamID      INTEGER         NOT NULL
    ,intCoachID     INTEGER         NOT NULL
    ,CONSTRAINT TTeamCoaches_PK PRIMARY KEY ( intTeamCoachID )
)

CREATE TABLE TPlayers
(
    intPlayerID     INTEGER         NOT NULL
    ,strFirstName   VARCHAR(50)     NOT NULL
    ,strMiddleName  varchar(50)     not null
    ,strLastName    VARCHAR(50)     NOT NULL
    ,strEmail       VARCHAR(50)     NOT NULL
    ,intShirtSizeID INTEGER         NOT NULL
    ,intPantSizeID  INTEGER         NOT NULL
    ,intSockSizeID  INTEGER         NOT NULL
    ,strCity        VARCHAR(50)     NOT NULL
    ,intStateID     INTEGER         NOT NULL
    ,intGenderID    INTEGER         NOT NULL
    ,intAgeGroupID  INTEGER         NOT NULL
    ,strAddress     varchar(50)     not null
    ,strZip         varchar(50)     not null
    ,CONSTRAINT TPlayers_PK PRIMARY KEY ( intPlayerID )
)

CREATE TABLE TTeamPlayers
(
    intTeamPlayerID INTEGER         NOT NULL
    ,intTeamID      INTEGER         NOT NULL
    ,intPlayerID    INTEGER         NOT NULL
    ,CONSTRAINT TTeamPlayers_PK PRIMARY KEY ( intTeamPlayerID )
)

CREATE TABLE Z_TeamPlayers
(
    intTeamPlayerAuditID    INTEGER     IDENTITY    NOT NULL
    ,intTeamPlayerID        INTEGER         NOT NULL
    ,intTeamID              INTEGER         NOT NULL
    ,intPlayerID            INTEGER         NOT NULL
    ,UpdatedBy              VARCHAR(128)        NOT NULL
    ,UpdatedOn              DATETIME            NOT NULL
    ,strAction              VARCHAR(1)          NOT NULL
    ,strModified_Reason     VARCHAR(1000)
    ,CONSTRAINT Z_TeamPlayers_PK PRIMARY KEY ( intTeamPlayerAuditID )
)

CREATE TABLE Z_TeamCoaches
(
    intTeamCoachAuditID INTEGER IDENTITY    NOT NULL
    ,intTeamCoachID INTEGER         NOT NULL
    ,intTeamID      INTEGER         NOT NULL
    ,intCoachID     INTEGER         NOT NULL
    ,CONSTRAINT Z_TeamCoaches_PK PRIMARY KEY ( intTeamCoachAuditID )
)

CREATE TABLE Z_Teams
(
    intTeamAuditID  INTEGER IDENTITY        NOT NULL
    ,intTeamID      INTEGER         NOT NULL
    ,strTeam        VARCHAR(50)     NOT NULL
    ,UpdatedBy      VARCHAR(128)        NOT NULL
    ,UpdatedOn      DATETIME            NOT NULL
    ,strAction              VARCHAR(1)          NOT NULL
    ,strModified_Reason     VARCHAR(1000)
    ,CONSTRAINT Z_Teams_PK PRIMARY KEY ( intTeamAuditID )
)

CREATE TABLE Z_Players
(
    intPlayerAuditID    INTEGER IDENTITY    NOT NULL
    ,intPlayerID        INTEGER         NOT NULL
    ,strFirstName   VARCHAR(50)     NOT NULL
    ,strLastName    VARCHAR(50)     NOT NULL
    ,strEmail       VARCHAR(50)     NOT NULL
    ,intShirtSizeID INTEGER         NOT NULL
    ,intPantSizeID  INTEGER         NOT NULL
    ,intSockSizeID  INTEGER         NOT NULL
    ,strCity        VARCHAR(50)     NOT NULL
    ,intStateID     INTEGER         NOT NULL
    ,intGenderID    INTEGER         NOT NULL
    ,intAgeGroupID  INTEGER         NOT NULL
    ,UpdatedBy      VARCHAR(128)        NOT NULL
    ,UpdatedOn      DATETIME            NOT NULL
    ,strAction              VARCHAR(1)          NOT NULL
    ,strModified_Reason     VARCHAR(1000)
    ,CONSTRAINT Z_Players_PK PRIMARY KEY ( intPlayerAuditID )
)

CREATE TABLE Z_Coaches
(
    intAuditCoachID     INTEGER IDENTITY        NOT NULL
    ,intCoachID     INTEGER         NOT NULL
    ,strFirstName   VARCHAR(50)     NOT NULL
    ,strLastName    VARCHAR(50)     NOT NULL
    ,strCity        Varchar(50)     not null
    ,intStateID     integer         not null
    ,strPhoneNumber varchar(50)     not null
    ,UpdatedBy      VARCHAR(128)        NOT NULL
    ,UpdatedOn      DATETIME            NOT NULL
    ,strAction              VARCHAR(1)          NOT NULL
    ,strModified_Reason     VARCHAR(1000)
    ,CONSTRAINT Z_Coaches_PK PRIMARY KEY ( intAuditCoachID )
)

CREATE TABLE TUsers
(
    intUserID       integer         not null
    ,intLoginID     integer         not null
    ,intRoleID      integer         not null
    ,CONSTRAINT TUsers_PK PRIMARY KEY ( intUserID )
)

CREATE TABLE TRoles
(
    intRoleID       integer         not null
    ,strRole            VARCHAR(50)     NOT NULL
    ,CONSTRAINT TRoles_PK PRIMARY KEY ( intRoleID )
)

CREATE TABLE TLogins 
(
    intLoginID      integer         not null
    ,strLoginName   varchar(50)     not null
    ,strPassword    VARCHAR(50)     NOT NULL
    ,CONSTRAINT TLogins_PK PRIMARY KEY ( intLoginID )
)

CREATE TABLE TFieldGames
(
    intFieldGameID  INTEGER         NOT NULL
    ,intFieldID     INTEGER         NOT NULL
    ,intTeamID      INTEGER         NOT NULL
    ,intRefereeID   INTEGER         NOT NULL
    ,CONSTRAINT TFieldGames_PK PRIMARY KEY ( intFieldGameID )
)


-- 1
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TPlayers_FK
FOREIGN KEY ( intPlayerID ) REFERENCES TPlayers ( intPlayerID )

-- 2
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TShirtSizes_FK
FOREIGN KEY ( intShirtSizeID ) REFERENCES TShirtSizes ( intShirtSizeID )

-- 3
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TPantSizes_FK
FOREIGN KEY ( intPantSizeID ) REFERENCES TPantSizes ( intPantSizeID )

-- 4
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TSockSizes_FK
FOREIGN KEY ( intSockSizeID ) REFERENCES TSockSizes ( intSockSizeID )

-- 5
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TStates_FK
FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )

-- 6
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TGenders_FK
FOREIGN KEY ( intGenderID ) REFERENCES TGenders ( intGenderID )

-- 7
ALTER TABLE TPlayers ADD CONSTRAINT TPlayers_TAgeGroups_FK
FOREIGN KEY ( intAgeGroupID ) REFERENCES TAgeGroups ( intAgeGroupID )

-- 8
ALTER TABLE TTeamCoaches ADD CONSTRAINT TTeamCoaches_TCoaches_FK
FOREIGN KEY ( intCoachID ) REFERENCES TCoaches ( intCoachID )

-- 9
ALTER TABLE TFieldGames ADD CONSTRAINT TFieldGames_TFields_FK
FOREIGN KEY ( intFieldID ) REFERENCES TFields ( intFieldID )

-- 10
ALTER TABLE TFieldGames ADD CONSTRAINT TFieldGames_TReferees_FK
FOREIGN KEY ( intRefereeID ) REFERENCES TReferees ( intRefereeID )

--11
ALTER TABLE TUsers ADD CONSTRAINT TUsers_TLogins_FK
FOREIGN KEY ( intLoginID ) REFERENCES TLogins ( intLoginID )

-- 12
ALTER TABLE TUsers ADD CONSTRAINT TUsers_TRoles_FK
FOREIGN KEY ( intRoleID ) REFERENCES TRoles ( intRoleID )


-- 13
ALTER TABLE TTeamPlayers ADD CONSTRAINT TTeamPlayers_TTeams_FK
FOREIGN KEY ( intTeamID ) REFERENCES TTeams ( intTeamID )

-- 14
ALTER TABLE TTeamCoaches ADD CONSTRAINT TTeamCoaches_TTeams_FK
FOREIGN KEY ( intTeamID ) REFERENCES TTeams ( intTeamID )

-- 15
ALTER TABLE TCoaches ADD CONSTRAINT TCoaches_TShirtSizes_FK
FOREIGN KEY ( intShirtSizeID ) REFERENCES TShirtSizes ( intShirtSizeID )

--16
ALTER TABLE TCoaches ADD CONSTRAINT TCoaches_TStates_FK
FOREIGN KEY ( intStateID ) REFERENCES TStates ( intStateID )
NoLuckCollector
  • 65
  • 8
  • 2
    There is no Foreign Keys at all so as for now, order does not matter. It could be even single `DROP TABLE IF EXISTS TTeamPlayers, TPlayers,TTeamCoaches,...;` [Demo](http://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=9eef7fe2efd3439bff559800131892b1) – Lukasz Szozda Mar 15 '18 at 20:47
  • 2
    I would suggest not using the T prefix on every table. It just makes it harder to read the name of the table and it doesn't provide any real benefit. Worse is it may actually hinder you at some point. Consider if you need to make some change but something else it hitting your table. Since you can't fix the other code you create named TPlayers. Now you have a table prefix on the wrong object type. – Sean Lange Mar 15 '18 at 20:49
  • @SeanLange I agree about remove the prefix, but dont understand the example, if someone is using the table you still stuck not matter what the name is. – Juan Carlos Oropeza Mar 15 '18 at 20:50
  • I only use the T prefix because they teach that at my school so it's easier to understand its a table. I know its dumb and has no benefit at all and you wont see this in the real world but I just wanted to stick with it for a little longer since I'm still in school – NoLuckCollector Mar 15 '18 at 20:52
  • TShirtSizes, sounds like a table only for T-Shirts, I agree with Sean. – AaronLS Mar 15 '18 at 20:53
  • @JuanCarlosOropeza - No...you create the view as a replacement for the table. So the new view would start with someName, then rename the table, finally rename the view to the original table name. Now you have view named TPlayers that is not actually a table but is instead a view. – Sean Lange Mar 15 '18 at 20:53

2 Answers2

3

Since there are no foreign keys in your scheme, the order won't matter. This is what those foreign keys are for: knowing about relations and protect your structure. Without the foreign keys, the database cannot know anything about the structure, not keep you away from destroying the structure. You have to take care of the structure until you add those foreign keys.

P.S.
  • 188
  • 10
  • I have the foreign keys listed in the question. I didn't at first but now I do. – NoLuckCollector Mar 15 '18 at 21:11
  • OK, now walk through your list of tables, and drp those first having no reference from a foreign id. Repeat this in a loop until all references are "solved" aka no foreign keys exist. Drop the left tables... – P.S. Mar 15 '18 at 21:18
0

Since you're learning, the easiest approach is the technique used by SSMS for scripting. First, drop the foreign keys in one batch (example). Then drop the tables. Not certain what your focus in learning is at this point, but you should master tsql first before you attempt to script DDL at any level of complexity. Given what you have described, you are likely in over your head and should concentrate on gaining skill/experience using a well-designed database first - e.g., AdventureWorks or World Wide Importers.

SMor
  • 2,830
  • 4
  • 11
  • 14