Alter table commands take so long to executeAdd a column with a default value to an existing table in SQL ServerIs there a REAL performance difference between INT and VARCHAR primary keys?Setting up foreign keys in phpMyAdmin?How to add 'ON DELETE CASCADE' in ALTER TABLE statementFinding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?Add Foreign Key to existing tableHow to import an SQL file using the command line in MySQL?The ALTER TABLE statement conflicted with the FOREIGN KEY constraintSQL create table and set auto increment value without Alter table
What is the purpose of std::forward()'s rvalue reference overload?
Scala list with same adjacent values
Can an old DSLR be upgraded to match modern smartphone image quality
Are grass strips more dangerous than tarmac?
If a massive object like Jupiter flew past the Earth how close would it need to come to pull people off of the surface?
Grep complete name including dot in the word
When was the word "ambigu" first used with the sense of "meal with all items served at the same time"?
Is there any Biblical Basis for 400 years of silence between Old and New Testament?
What does the wifi amplifier exactly amplify?
Why use water tanks from a retired Space Shuttle?
How to detach yourself from a character you're going to kill?
Where can I find the list of all tendons in the human body?
What if you don't bring your credit card or debit for incidentals?
Strange math syntax in old basic listing
How can I grammatically understand "Wir über uns"?
Can you dispel the Slow effect of a Stone Golem?
Beginner's snake game using PyGame
arcpy.GetParameterAsText not passing arguments to script?
Why don't I have ground wiring on any of my outlets?
What is the difference between nullifying your vote and not going to vote at all?
Modern approach to radio buttons
What are the problems in teaching guitar via Skype?
Site-specific value for an appsetting in a multisite solution
Applicants clearly not having the skills they advertise
Alter table commands take so long to execute
Add a column with a default value to an existing table in SQL ServerIs there a REAL performance difference between INT and VARCHAR primary keys?Setting up foreign keys in phpMyAdmin?How to add 'ON DELETE CASCADE' in ALTER TABLE statementFinding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?Add Foreign Key to existing tableHow to import an SQL file using the command line in MySQL?The ALTER TABLE statement conflicted with the FOREIGN KEY constraintSQL create table and set auto increment value without Alter table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
0
I'm trying to create 4 table hierarchy - Sport Category, League, Team and Player. Till this moment this is what I have created:
-- Create PLAYER table
create table PLAYER
(
PLAYER_ID INT not null,
NAME_OF_PLAYER VARCHAR(36) not null,
NUMBER_OF_PLAYER INT not null,
TEAM_ID INT not null
) ;
alter table PLAYER
add constraint PLAYER_PK primary key (PLAYER_ID);
alter table PLAYER MODIFY COLUMN PLAYER_ID INT auto_increment;
alter table PLAYER
add constraint PLAYER_UK unique (NAME_OF_PLAYER, TEAM_ID);
-- Create TEAM table
create table TEAM
(
TEAM_ID INT not null,
NAME_OF_TEAM VARCHAR(36) not null,
LEAGUE_ID INT not null
) ;
alter table TEAM
add constraint TEAM_PK primary key (TEAM_ID);
alter table TEAM MODIFY COLUMN TEAM_ID INT auto_increment;
alter table TEAM
add constraint TEAM_UK unique (NAME_OF_TEAM);
-- Create LEAGUE table
create table LEAGUE
(
LEAGUE_ID INT not null,
NAME_OF_LEAGUE VARCHAR(36) not null,
SPORT_CATEGORY_ID INT not null
) ;
alter table LEAGUE
add constraint LEAGUE_PK primary key (LEAGUE_ID);
alter table LEAGUE MODIFY COLUMN LEAGUE_ID INT auto_increment;
alter table LEAGUE
add constraint LEAGUE_UK unique (NAME_OF_LEAGUE);
-- Create Sport_Category table
create table SPORT_CATEGORY
(
SPORT_CATEGORY_ID INT not null,
NAME_OF_CATEGORY VARCHAR(36) not null
) ;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_PK primary key (SPORT_CATEGORY_ID);
alter table SPORT_CATEGORY MODIFY COLUMN SPORT_CATEGORY_ID INT auto_increment;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_UK unique (NAME_OF_CATEGORY);
alter table PLAYER
add constraint PLAYER_FK foreign key (TEAM_ID)
references TEAM (TEAM_ID);
alter table TEAM
add constraint TEAM_FK1 foreign key (LEAGUE_ID)
references LEAGUE (LEAGUE_ID);
alter table LEAGUE
add constraint LEAGUE_FK1 foreign key (SPORT_CATEGORY_ID)
references SPORT_CATEGORY (SPORT_CATEGORY_ID);
When I execute the this sql file, it takes like 15 seconds to finish. With some testing I noticed that alter table statements make it slower. Is this normal for this kind of relation tables? Is it because of the complexity of the tables?
I'm new to working with relations but very enthusiastic about it. Any help will be appreciated.
mysql sql database-design hierarchy database-normalization
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
asked Mar 24 at 11:06
I.StanevI.Stanev
409
|
show 2 more comments
0
I'm trying to create 4 table hierarchy - Sport Category, League, Team and Player. Till this moment this is what I have created:
-- Create PLAYER table
create table PLAYER
(
PLAYER_ID INT not null,
NAME_OF_PLAYER VARCHAR(36) not null,
NUMBER_OF_PLAYER INT not null,
TEAM_ID INT not null
) ;
alter table PLAYER
add constraint PLAYER_PK primary key (PLAYER_ID);
alter table PLAYER MODIFY COLUMN PLAYER_ID INT auto_increment;
alter table PLAYER
add constraint PLAYER_UK unique (NAME_OF_PLAYER, TEAM_ID);
-- Create TEAM table
create table TEAM
(
TEAM_ID INT not null,
NAME_OF_TEAM VARCHAR(36) not null,
LEAGUE_ID INT not null
) ;
alter table TEAM
add constraint TEAM_PK primary key (TEAM_ID);
alter table TEAM MODIFY COLUMN TEAM_ID INT auto_increment;
alter table TEAM
add constraint TEAM_UK unique (NAME_OF_TEAM);
-- Create LEAGUE table
create table LEAGUE
(
LEAGUE_ID INT not null,
NAME_OF_LEAGUE VARCHAR(36) not null,
SPORT_CATEGORY_ID INT not null
) ;
alter table LEAGUE
add constraint LEAGUE_PK primary key (LEAGUE_ID);
alter table LEAGUE MODIFY COLUMN LEAGUE_ID INT auto_increment;
alter table LEAGUE
add constraint LEAGUE_UK unique (NAME_OF_LEAGUE);
-- Create Sport_Category table
create table SPORT_CATEGORY
(
SPORT_CATEGORY_ID INT not null,
NAME_OF_CATEGORY VARCHAR(36) not null
) ;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_PK primary key (SPORT_CATEGORY_ID);
alter table SPORT_CATEGORY MODIFY COLUMN SPORT_CATEGORY_ID INT auto_increment;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_UK unique (NAME_OF_CATEGORY);
alter table PLAYER
add constraint PLAYER_FK foreign key (TEAM_ID)
references TEAM (TEAM_ID);
alter table TEAM
add constraint TEAM_FK1 foreign key (LEAGUE_ID)
references LEAGUE (LEAGUE_ID);
alter table LEAGUE
add constraint LEAGUE_FK1 foreign key (SPORT_CATEGORY_ID)
references SPORT_CATEGORY (SPORT_CATEGORY_ID);
When I execute the this sql file, it takes like 15 seconds to finish. With some testing I noticed that alter table statements make it slower. Is this normal for this kind of relation tables? Is it because of the complexity of the tables?
I'm new to working with relations but very enthusiastic about it. Any help will be appreciated.
mysql sql database-design hierarchy database-normalization
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
asked Mar 24 at 11:06
I.StanevI.Stanev
409
1
Four questions is sort of the definition of "too broad".
– Gordon Linoff
Mar 24 at 12:02
@GordonLinoff I agree, you are right.
– I.Stanev
Mar 24 at 13:09
2
I ran your queries on an instance of MySQL 8.0 on my laptop, and the total time reported by the profiler was 0.174 seconds. The longest single statement was 0.024 seconds. I have to wonder if you are running on a very old computer, or if you have a computer that is running other demanding software.
– Bill Karwin
Mar 24 at 15:09
Just out of curiosity, why do you add the keys with alter table instead of inline within the create table?
– Shadow
Mar 24 at 16:03
1
Most of your ALTERs are unnecessary, because you can define primary keys, column options, and indexes as you CREATE TABLE. Sometimes I see foreign keys added to tables using ALTER because you can't create a foreign key to a table you haven't defined yet. So to avoid needing to create tables in a specific order, some people define all their tables without foreign keys, then after all tables are created, do another pass to add foreign keys with ALTER TABLE.
– Bill Karwin
Mar 24 at 17:31
|
show 2 more comments
0
0
0
I'm trying to create 4 table hierarchy - Sport Category, League, Team and Player. Till this moment this is what I have created:
-- Create PLAYER table
create table PLAYER
(
PLAYER_ID INT not null,
NAME_OF_PLAYER VARCHAR(36) not null,
NUMBER_OF_PLAYER INT not null,
TEAM_ID INT not null
) ;
alter table PLAYER
add constraint PLAYER_PK primary key (PLAYER_ID);
alter table PLAYER MODIFY COLUMN PLAYER_ID INT auto_increment;
alter table PLAYER
add constraint PLAYER_UK unique (NAME_OF_PLAYER, TEAM_ID);
-- Create TEAM table
create table TEAM
(
TEAM_ID INT not null,
NAME_OF_TEAM VARCHAR(36) not null,
LEAGUE_ID INT not null
) ;
alter table TEAM
add constraint TEAM_PK primary key (TEAM_ID);
alter table TEAM MODIFY COLUMN TEAM_ID INT auto_increment;
alter table TEAM
add constraint TEAM_UK unique (NAME_OF_TEAM);
-- Create LEAGUE table
create table LEAGUE
(
LEAGUE_ID INT not null,
NAME_OF_LEAGUE VARCHAR(36) not null,
SPORT_CATEGORY_ID INT not null
) ;
alter table LEAGUE
add constraint LEAGUE_PK primary key (LEAGUE_ID);
alter table LEAGUE MODIFY COLUMN LEAGUE_ID INT auto_increment;
alter table LEAGUE
add constraint LEAGUE_UK unique (NAME_OF_LEAGUE);
-- Create Sport_Category table
create table SPORT_CATEGORY
(
SPORT_CATEGORY_ID INT not null,
NAME_OF_CATEGORY VARCHAR(36) not null
) ;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_PK primary key (SPORT_CATEGORY_ID);
alter table SPORT_CATEGORY MODIFY COLUMN SPORT_CATEGORY_ID INT auto_increment;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_UK unique (NAME_OF_CATEGORY);
alter table PLAYER
add constraint PLAYER_FK foreign key (TEAM_ID)
references TEAM (TEAM_ID);
alter table TEAM
add constraint TEAM_FK1 foreign key (LEAGUE_ID)
references LEAGUE (LEAGUE_ID);
alter table LEAGUE
add constraint LEAGUE_FK1 foreign key (SPORT_CATEGORY_ID)
references SPORT_CATEGORY (SPORT_CATEGORY_ID);
When I execute the this sql file, it takes like 15 seconds to finish. With some testing I noticed that alter table statements make it slower. Is this normal for this kind of relation tables? Is it because of the complexity of the tables?
I'm new to working with relations but very enthusiastic about it. Any help will be appreciated.
mysql sql database-design hierarchy database-normalization
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
asked Mar 24 at 11:06
I.StanevI.Stanev
409
I'm trying to create 4 table hierarchy - Sport Category, League, Team and Player. Till this moment this is what I have created:
-- Create PLAYER table
create table PLAYER
(
PLAYER_ID INT not null,
NAME_OF_PLAYER VARCHAR(36) not null,
NUMBER_OF_PLAYER INT not null,
TEAM_ID INT not null
) ;
alter table PLAYER
add constraint PLAYER_PK primary key (PLAYER_ID);
alter table PLAYER MODIFY COLUMN PLAYER_ID INT auto_increment;
alter table PLAYER
add constraint PLAYER_UK unique (NAME_OF_PLAYER, TEAM_ID);
-- Create TEAM table
create table TEAM
(
TEAM_ID INT not null,
NAME_OF_TEAM VARCHAR(36) not null,
LEAGUE_ID INT not null
) ;
alter table TEAM
add constraint TEAM_PK primary key (TEAM_ID);
alter table TEAM MODIFY COLUMN TEAM_ID INT auto_increment;
alter table TEAM
add constraint TEAM_UK unique (NAME_OF_TEAM);
-- Create LEAGUE table
create table LEAGUE
(
LEAGUE_ID INT not null,
NAME_OF_LEAGUE VARCHAR(36) not null,
SPORT_CATEGORY_ID INT not null
) ;
alter table LEAGUE
add constraint LEAGUE_PK primary key (LEAGUE_ID);
alter table LEAGUE MODIFY COLUMN LEAGUE_ID INT auto_increment;
alter table LEAGUE
add constraint LEAGUE_UK unique (NAME_OF_LEAGUE);
-- Create Sport_Category table
create table SPORT_CATEGORY
(
SPORT_CATEGORY_ID INT not null,
NAME_OF_CATEGORY VARCHAR(36) not null
) ;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_PK primary key (SPORT_CATEGORY_ID);
alter table SPORT_CATEGORY MODIFY COLUMN SPORT_CATEGORY_ID INT auto_increment;
alter table SPORT_CATEGORY
add constraint SPORT_CATEGORY_UK unique (NAME_OF_CATEGORY);
alter table PLAYER
add constraint PLAYER_FK foreign key (TEAM_ID)
references TEAM (TEAM_ID);
alter table TEAM
add constraint TEAM_FK1 foreign key (LEAGUE_ID)
references LEAGUE (LEAGUE_ID);
alter table LEAGUE
add constraint LEAGUE_FK1 foreign key (SPORT_CATEGORY_ID)
references SPORT_CATEGORY (SPORT_CATEGORY_ID);
When I execute the this sql file, it takes like 15 seconds to finish. With some testing I noticed that alter table statements make it slower. Is this normal for this kind of relation tables? Is it because of the complexity of the tables?
I'm new to working with relations but very enthusiastic about it. Any help will be appreciated.
mysql sql database-design hierarchy database-normalization
mysql sql database-design hierarchy database-normalization
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
asked Mar 24 at 11:06
I.StanevI.Stanev
409
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
asked Mar 24 at 11:06
I.StanevI.Stanev
409
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
edited Apr 1 at 11:10
Mike Sherrill 'Cat Recall'
70.8k1387143
70.8k1387143
asked Mar 24 at 11:06
I.StanevI.Stanev
409
asked Mar 24 at 11:06
I.StanevI.Stanev
409
asked Mar 24 at 11:06
I.StanevI.Stanev
409
409
1
Four questions is sort of the definition of "too broad".
– Gordon Linoff
Mar 24 at 12:02
@GordonLinoff I agree, you are right.
– I.Stanev
Mar 24 at 13:09
2
I ran your queries on an instance of MySQL 8.0 on my laptop, and the total time reported by the profiler was 0.174 seconds. The longest single statement was 0.024 seconds. I have to wonder if you are running on a very old computer, or if you have a computer that is running other demanding software.
– Bill Karwin
Mar 24 at 15:09
Just out of curiosity, why do you add the keys with alter table instead of inline within the create table?
– Shadow
Mar 24 at 16:03
1
Most of your ALTERs are unnecessary, because you can define primary keys, column options, and indexes as you CREATE TABLE. Sometimes I see foreign keys added to tables using ALTER because you can't create a foreign key to a table you haven't defined yet. So to avoid needing to create tables in a specific order, some people define all their tables without foreign keys, then after all tables are created, do another pass to add foreign keys with ALTER TABLE.
– Bill Karwin
Mar 24 at 17:31
|
show 2 more comments
1
Four questions is sort of the definition of "too broad".
– Gordon Linoff
Mar 24 at 12:02
@GordonLinoff I agree, you are right.
– I.Stanev
Mar 24 at 13:09
2
I ran your queries on an instance of MySQL 8.0 on my laptop, and the total time reported by the profiler was 0.174 seconds. The longest single statement was 0.024 seconds. I have to wonder if you are running on a very old computer, or if you have a computer that is running other demanding software.
– Bill Karwin
Mar 24 at 15:09
Just out of curiosity, why do you add the keys with alter table instead of inline within the create table?
– Shadow
Mar 24 at 16:03
1
Most of your ALTERs are unnecessary, because you can define primary keys, column options, and indexes as you CREATE TABLE. Sometimes I see foreign keys added to tables using ALTER because you can't create a foreign key to a table you haven't defined yet. So to avoid needing to create tables in a specific order, some people define all their tables without foreign keys, then after all tables are created, do another pass to add foreign keys with ALTER TABLE.
– Bill Karwin
Mar 24 at 17:31
1
1
Four questions is sort of the definition of "too broad".
– Gordon Linoff
Mar 24 at 12:02
Four questions is sort of the definition of "too broad".
– Gordon Linoff
Mar 24 at 12:02
@GordonLinoff I agree, you are right.
– I.Stanev
Mar 24 at 13:09
@GordonLinoff I agree, you are right.
– I.Stanev
Mar 24 at 13:09
2
2
I ran your queries on an instance of MySQL 8.0 on my laptop, and the total time reported by the profiler was 0.174 seconds. The longest single statement was 0.024 seconds. I have to wonder if you are running on a very old computer, or if you have a computer that is running other demanding software.
– Bill Karwin
Mar 24 at 15:09
I ran your queries on an instance of MySQL 8.0 on my laptop, and the total time reported by the profiler was 0.174 seconds. The longest single statement was 0.024 seconds. I have to wonder if you are running on a very old computer, or if you have a computer that is running other demanding software.
– Bill Karwin
Mar 24 at 15:09
Just out of curiosity, why do you add the keys with alter table instead of inline within the create table?
– Shadow
Mar 24 at 16:03
Just out of curiosity, why do you add the keys with alter table instead of inline within the create table?
– Shadow
Mar 24 at 16:03
1
1
Most of your ALTERs are unnecessary, because you can define primary keys, column options, and indexes as you CREATE TABLE. Sometimes I see foreign keys added to tables using ALTER because you can't create a foreign key to a table you haven't defined yet. So to avoid needing to create tables in a specific order, some people define all their tables without foreign keys, then after all tables are created, do another pass to add foreign keys with ALTER TABLE.
– Bill Karwin
Mar 24 at 17:31
Most of your ALTERs are unnecessary, because you can define primary keys, column options, and indexes as you CREATE TABLE. Sometimes I see foreign keys added to tables using ALTER because you can't create a foreign key to a table you haven't defined yet. So to avoid needing to create tables in a specific order, some people define all their tables without foreign keys, then after all tables are created, do another pass to add foreign keys with ALTER TABLE.
– Bill Karwin
Mar 24 at 17:31
|
show 2 more comments
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55323148%2falter-table-commands-take-so-long-to-execute%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55323148%2falter-table-commands-take-so-long-to-execute%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Four questions is sort of the definition of "too broad".
– Gordon Linoff
Mar 24 at 12:02
@GordonLinoff I agree, you are right.
– I.Stanev
Mar 24 at 13:09
2
I ran your queries on an instance of MySQL 8.0 on my laptop, and the total time reported by the profiler was 0.174 seconds. The longest single statement was 0.024 seconds. I have to wonder if you are running on a very old computer, or if you have a computer that is running other demanding software.
– Bill Karwin
Mar 24 at 15:09
Just out of curiosity, why do you add the keys with alter table instead of inline within the create table?
– Shadow
Mar 24 at 16:03
1
Most of your ALTERs are unnecessary, because you can define primary keys, column options, and indexes as you CREATE TABLE. Sometimes I see foreign keys added to tables using ALTER because you can't create a foreign key to a table you haven't defined yet. So to avoid needing to create tables in a specific order, some people define all their tables without foreign keys, then after all tables are created, do another pass to add foreign keys with ALTER TABLE.
– Bill Karwin
Mar 24 at 17:31