SQL DDLシ壹ユ繝シ繝悶Ν螳夂セゥ
逶ョ谺。
讎りヲ
DDL譁シ壹ョ繝シ繧ソ螳夂セゥ險隱
DataDefinitionLanguage
繝繝シ繝悶Ν縺ョ菴懈舌サ螳夂セゥ遲(Create/Alter/Truncate/Drop)
螳溯。後&繧後k縺ィ證鈴サ吶ョCommit縺碁←逕ィ縺輔l縲ヽollBack縺ァ縺阪↑縺縲
繝繝シ繝悶Ν螳夂セゥ
Create Table
縲縲 縲縲蛻怜錐シ Number(4)
縲縲 縲縲蛻怜錐シ Date
譌「蟄倩。ィ繧貞渕縺ォ縺励◆陦ィ菴懈(蜑ッ蝠上>蜷医o縺)
Create Table 陦ィ蜷 ス As Select ス From ス
蝠上>蜷医o縺帙◆陦ィ繧偵さ繝斐シ縺励※譁ー縺励>繝繝シ繝悶Ν繧剃ス懈
繝サ蛻怜錐
繝サ繝繝シ繧ソ
繝サNOT NULL 蛻カ邏
縺ョ縺ソ繧ウ繝斐シ縺輔l繧九
繝サIndex縲∽サ門宛邏遲峨ッ繧ウ繝斐シ縺輔l縺ェ縺縲
繝サ陦ィ縺ォ髢「縺吶k蜈ィ縺ヲ縺ョ繧ェ繝悶ず繧ァ繧ッ繝域ィゥ髯縺御サ倅ク弱&繧後k
Alter Table
縲縲蛻怜錐シ Number(4)
縲縲蛻怜錐シ Date
螳夂セゥ貂医∩縺ョ陦ィ縺ョ螳夂セゥ螟画峩
ALTER TABLE (陦ィ蜷) DROP ( (蛻怜錐) );
ALTER TABLE (陦ィ蜷) ADD ( (蛻怜錐) NVARCHAR2(1) );
ALTER TABLE (陦ィ蜷) MODIFY ( (蛻怜錐) NVARCHAR2(100) );
DROP シ壹ョ繝シ繧ソ譬シ邏肴ク医∩縺ョ蛻励r蜑企勁蜿ッ閭ス
ADD シ哢OT NULL蛻カ邏莉倥″縺ョ蛻励r霑ス蜉縺吶k蝣エ蜷医ッ陦ィ蜈ィ菴薙r遨コ縺ォ縺吶k蠢隕√′縺ゅk
MODIFY シ
繝繝シ繧ソ譬シ邏肴ク医∩縺ョ蛻励ョ蝙九r螟峨∴繧句エ蜷医ッ蛻励ョ繝シ繧ソ繧貞炎髯、縺吶k蠢隕√′縺ゅk
CHAR(10)竊辰HAR(5)遲峨ョ譁蟄怜鈴聞縺ョ螟画峩縺ッ縲∝励ョ繝シ繧ソ縺悟、画峩蠕後ョ譁蟄怜鈴聞莉・荳九〒縺ゅl縺ー蜿ッ閭ス
UPDATE 陦ィ SET jsonb蛻怜錐 = jsonb蛻怜錐 || '{"jsonb蛻怜蛻怜錐":"蛻晄悄蛟、"}';
ALTER TABLE 陦ィ蜷 DROP COLUMN 蛻怜錐
ALTER TABLE 陦ィ蜷 ALTER COLUMN 蛻怜錐 TYPE 蝙句錐
ALTER TABLE 陦ィ蜷 DROP COLUMN 蛻怜錐
繝繝シ繧ソ蝙句、画峩
ALTER TABLE 陦ィ蜷 MODIFY 蛻怜錐 繝繝シ繧ソ蝙;
遘サ蜍(繝繝シ繧ソ蝙句、画峩)
ALTER TABLE 陦ィ蜷 MODIFY 蛻怜錐 繝繝シ繧ソ蝙 AFTER 蛻怜錐;
荳サ繧ュ繝シ螟画峩
ALTER TABLE 陦ィ蜷 DROP PRIMARY KEY, ADD PRIMARY KEY (蛻怜錐1,蛻怜錐2);
繧ォ繝ゥ繝繧ウ繝。繝ウ繝亥、画峩
ALTER TABLE 陦ィ蜷 CHANGE COLUMN 蛻怜錐 蛻怜錐 int(5) NOT NULL COMMENT '繧ウ繝。繝ウ繝';
騾」逡ェ蛻晄悄蛹
ALTER TABLE 陦ィ蜷 auto_increment = 1;
縲縲蛻怜錐シ INT
縲縲蛻怜錐シ DateTime
蛻励ョ霑ス蜉
ALTER TABLE [陦ィ蜷江 ADD [蛻怜錐] [蝙犠 DEFAULT 蛟、 NOT NULL;
ALTER TABLE [TestDB] ADD [TestTable] [int] DEFAULT 2 NOT NULL;
ALTER TABLE [TestDB] ADD [TestTable] [int] NOT NULL;
ALTER TABLE [TestDB] ADD [TestTable] [int] ;
窶サ蛻励ッ譛蠕悟ーセ縺ォ霑ス蜉縺輔l繧九
蛻励ョ蜑企勁
ALTER TABLE [陦ィ蜷江 DROP COLUMN [蛻怜錐];
ALTER TABLE [TestTable] DROP COLUMN [TestColumn];
Drop Table
Drop Table 陦ィ蜷
縲仙酔譎ゅ↓蜑企勁縺輔l繧九が繝悶ず繧ァ繧ッ繝医
繝サ繧、繝ウ繝繝繧ッ繧ケ
繝サ繝医Μ繧ャ繝シ
繝サ蛻カ邏
縲先ョ九k繧ェ繝悶ず繧ァ繧ッ繝医
繝サ繝薙Η繝シ(辟。蜉ケ)
繝サ繧キ繝弱ル繝
螟夜Κ繧ュ繝シ繧呈戟縺、繝繝シ繝悶Ν縺ョ蜑企勁
FORIGN KEY蛻カ邏繧定ィュ螳壹@縺ヲ縺繧句エ蜷医∬ヲェ陦ィ繧貞炎髯、縺ァ縺阪↑縺縲
縺薙ョ蝣エ蜷医∝宛邏繧貞炎髯、縺励※縺九i陦ィ繧貞炎髯、縲
縺セ縺溘ッCascade Constraints蜿・繧貞茜逕ィ縺励※蛻カ邏蜑企勁縺ィ蜷梧凾縺ォ陦ィ繧貞炎髯、縲
RecycleBin
Drop縺励◆繝繝シ繝悶Ν縺ッRecycleBin(繧エ繝溽ョア)縺ォ遘サ蜍輔
Purge蜿・繧堤畑縺繧九→RecycleBin縺ォ遘サ蜍輔&縺帙★縺ォ蜑企勁
繧エ繝溽ョア蜑企勁
PURGE TABLE 陦ィ蜷;
陦ィ蜑企勁縺ィ蜷梧凾縺ォ繧エ繝溽ョア蜑企勁
DROP TABLE 陦ィ蜷 PURGE;
繧エ繝溽ョア陦ィ遉コ
SELECT * FROM RECYCLEBIN;
繧エ繝溽ョア蜈ィ蜑企勁
PURGE RECYCLEBIN;
RecycleBin縺ョ陦ィ繧貞セゥ蜈
FlashBack Table 陦ィ蜷 To Before Drop
窶サDROP TABLE縺ッDDL譁縺ェ縺ョ縺ァROLL BACK縺ァ縺阪↑縺
View縺ョ蜿らァ蜈縺ョ陦ィ繧奪rop縺励◆蝣エ蜷
View縺ッ谿九k縺檎┌蜉ケ謇ア縺縺ィ縺ェ繧九
繧キ繝弱ル繝繧ら┌蜉ケ縲
Drop Table 陦ィ蜷
螟夜Κ繧ュ繝シ繧呈戟縺、繝繝シ繝悶Ν縺ョ蜑企勁
FORIGN KEY蛻カ邏繧定ィュ螳壹@縺ヲ縺繧句エ蜷医∬ヲェ陦ィ繧貞炎髯、縺ァ縺阪↑縺縲
縺薙ョ蝣エ蜷医∝宛邏繧貞炎髯、縺励※縺九i陦ィ繧貞炎髯、縲
縺セ縺溘ッ
Drop Table 陦ィ蜷 Cascade
蛻カ邏蜑企勁縺ィ蜷梧凾縺ォ陦ィ繧貞炎髯、
Rename
繧ウ繝。繝ウ繝
COMMENT ON TABLE 陦ィ蜷 IS 'ス';
繧ォ繝ゥ繝繧ウ繝。繝ウ繝郁ソス蜉
COMMENT ON COLUMN (陦ィ蜷).(蛻怜錐) IS 'ス'
Truncate Table
繝繝シ繧ソ縺ョ縺ソ蜑企勁縲Delete繧医j騾溘>縲
Truncate Table 陦ィ蜷
蛻カ邏
繝繝シ繝悶Ν縺ォ縺ッ繝繝シ繧ソ繧呈シ邏阪☆繧九↓蠖薙◆縺」縺ヲ蛻カ邏繧定ィュ縺代k莠九′縺ァ縺阪k縲
蛻カ邏縺ョ蛻ゥ轤ケ
繝サ繝ォ繝シ繝ォ縺ョ髮荳ュ蛹(髫懷ョウ繧帝亟縺偵k)
繝サ髫懷ョウ縺ョ菴取ク帙↓繧医k髢狗匱蜉ケ邇縺ョ蜷台ク
繝サ繝繝シ繧ソ菴懈舌ョ騾溷コヲ縺ッ菴惹ク九☆繧(繝√ぉ繝繧ッ縺悟ソ隕√↑轤コ)
蛻カ邏縺ョ螳夂セゥ
繝サ蛻励Ξ繝吶Ν讒区枚
繝サ陦ィ繝ャ繝吶Ν讒区枚
縺後≠繧九
蛻励Ξ繝吶Ν讒区枚
蛻怜錐 繝繝シ繧ソ蝙 (CONSTRAINT 蛻カ邏蜷) 蛻カ邏蜀螳ケ
NOT NULL 縺ッ縺薙%縺ァ縺励°螳夂セゥ縺ァ縺阪↑縺
ALTER TABLE縺ァ縺ッ螳夂セゥ縺ァ縺阪↑縺
陦ィ繝ャ繝吶Ν讒区枚
(CONSTRAINT 蛻カ邏蜷) 蛻カ邏蜀螳ケ
NOT NULL 縺ッ螳夂セゥ縺ァ縺阪↑縺
ALTER TABLE縺ァ縺ッ縺薙%縺ァ縺励°螳夂セゥ縺ァ縺阪↑縺
蛻カ邏縺ョ荳隕ァ蜿門セ
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'public'
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
NOT NULL蛻カ邏
險ュ螳壹☆繧九→INDEX縺瑚ェ蜍輔〒菴懈舌&繧後k縲
PRIMARY KEY蛻カ邏繧ょ酔縺
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア NOT NULL
竊 (陦ィ螳夂セゥ)縺ッ荳榊庄縲や (蛻怜ョ夂セゥ) 縺ョ縺ソ縲
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲CONSTRAINT 蛻カ邏蜷 NOT NULL(蛻怜錐)
PRIMARY KEY蛻カ邏
荳サ繧ュ繝シ繧剃ク諢上↓險ュ螳壹☆繧句エ蜷医↓菴ソ逕ィ
繝サNOT NULL蛻カ邏
繝サUNIQUE蛻カ邏
縺瑚ェ蜍輔〒隱イ縺輔l繧九
險ュ螳壹☆繧九→INDEX縺瑚ェ蜍輔〒菴懈舌&繧後k縲
NOT NULL蛻カ邏繧ょ酔縺
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア PRIMARY KEY
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲CONSTRAINT 蛻カ邏蜷 PRIMARY KEY(蛻怜錐, 蛻怜錐)
UNIQUE蛻カ邏
縺溘□縺湧ULL蛟、縺ッ驥崎、縺励※譬シ邏阪〒縺阪k縲
荳サ繧ュ繝シ縺ァ縺ェ縺蛻励r荳諢上↓險ュ螳壹☆繧句エ蜷医↓菴ソ逕ィ
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア UNIQUE
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲CONSTRAINT 蛻カ邏蜷 UNIQUE(蛻怜錐, 蛻怜錐)
CHECK蛻カ邏
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア CHECK(ス)
窶サ莉門励r蜿らァ縺励※縺ョ蛻カ邏繧貞ョ夂セゥ縺ァ縺阪↑縺
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲CONSTRAINT 蛻カ邏蜷 CHECK(ス)
窶サ莉門励r蜿らァ縺励※縺ョ蛻カ邏繧貞ョ夂セゥ縺ァ縺阪k
CHECK(A蛻 > B蛻)
(萓)
Create Table TestTable
縲縲Col1 Number(5) CHECK(0 < Col1 AND Col1 < 100)
縲縲Col2 Varchar(3) CHECK(Col2 = 'aaa')
FOREIGN KEY蛻カ邏
螟夜Κ繧ュ繝シ縺ッNULL縺セ縺溘ッ隕ェ陦ィ縺ョ荳サ繧ュ繝シ縺励°險ュ螳壹〒縺阪↑縺
隕ェ繧ュ繝シ縺ッ荳サ繧ュ繝シ蛻カ邏/UNIQUE蛻カ邏縺ョ險ュ螳壹′蠢隕
螟夜Κ繧ュ繝シ縺ョ蜿らァ縺吶k隕ェ陦ィ縺ョ隕ェ繧ュ繝シ縺ッ蜑企勁縺ァ縺阪↑縺(ON DELETE CASCADE繧ェ繝励す繝ァ繝ウ菴ソ逕ィ譎る勁縺)
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア REFERENCES 螟夜Κ繝繝シ繝悶Ν蜷(蛻怜錐),
縲縲蛻怜錐 蝙区ュ蝣ア CONSTRAINT 蛻カ邏蜷 REFERENCES 螟夜Κ繝繝シ繝悶Ν蜷(蛻怜錐);
窶サ蛻怜ョ夂セゥ縺ョ蝣エ蜷医ッFOREIGN KEY(蛻怜錐縲∝怜錐)縺ッ逵∫払縺吶k
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲FOREIGN KEY(蛻怜錐縲∝怜錐) REFERENCES 螟夜Κ繝繝シ繝悶Ν蜷(蛻怜錐縲∝怜錐)
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲蛻怜錐 蝙区ュ蝣ア,
縲縲CONSTRAINT 蛻カ邏蜷 FOREIGN KEY(蛻怜錐縲∝怜錐)
縲縲REFERENCES 螟夜Κ繝繝シ繝悶Ν蜷(蛻怜錐縲∝怜錐)
隕ェ(蜀驛ィ)繝繝シ繝悶Νシ啅PDATE譎ゅョ謖吝虚
Create Table 陦ィ蜷
縲縲蛻怜錐 蝙区ュ蝣ア
縲縲FOREIGN KEY(蛻怜錐縲∝怜錐) REFERENCES 螟夜Κ繝繝シ繝悶Ν蜷(蛻怜錐縲∝怜錐)
縲縲ON UPDATE 險ュ螳壼、
隕ェ(蜀驛ィ)繝繝シ繝悶Ν縺梧峩譁ー縺輔l縺溷エ蜷医ョ謖吝虚シ險ュ螳壼、
繝サRESTRICT
隕ェ繝繝シ繝悶Ν縺梧峩譁ー縺輔l縺溷エ蜷医√お繝ゥ繝シ縺檎匱逕溘☆繧(繝繝輔か繝ォ繝)
繝サNO ACTION
RESTRICT縺ィ蜷後§
繝サON DELETE CASCADE
隕ェ繝繝シ繝悶ΝUPDATEシ壼ュ舌ユ繝シ繝悶Ν繧ょ酔縺伜、縺ォUPDATE
隕ェ繝繝シ繝悶ΝDELETEシ壼ュ舌ユ繝シ繝悶Ν繧DELETE
繝サON DELETE SET NULL
隕ェ繝繝シ繝悶ΝUPDATEシ壼ュ舌ユ繝シ繝悶Ν縺ョ蟇セ雎。蛻励↓NULL
隕ェ繝繝シ繝悶ΝDELETEシ壼ュ舌ユ繝シ繝悶Ν縺ョ蟇セ雎。蛻励↓NULL
繧、繝ウ繝繝繧ッ繧ケ髢「騾」蛻カ邏
縲ADD CONSTRAINT [蛻カ邏蜷江 PRIMARY KEY CLUSTERED
(
縲[蛻怜錐] ASC,
縲ス
)WITH (
縲繧、繝ウ繝繝繧ッ繧ケ菴懈先凾縺ョ繧ェ繝励す繝ァ繝ウ(譌「螳壼、シ唹FF)
縲fillfactor縺ァ謖螳壹&繧後k遨コ縺埼伜沺縺ョ繝代シ繧サ繝ウ繝繝シ繧ク縺後√う繝ウ繝繝繧ッ繧ケ縺ョ荳ュ髢薙Ξ繝吶Ν縺ョ繝壹シ繧ク縺ォ驕ゥ逕ィ縺輔l繧
縲PAD_INDEX = OFF,
縲
縲蛻蟶邨ア險亥崎ィ育ョ玲欠螳(譌「螳壼、シ唹FF)
縲STATISTICS_NORECOMPUTE = OFF,
縲
縲tempdb縺ク縺ョ荳ヲ縺ケ譖ソ縺育オ先棡譬シ邏(譌「螳壼、シ唹FF)
縲SORT_IN_TEMPDB = OFF,
縲
縲荳諢上ョ繧、繝ウ繝繝繧ッ繧ケ縺ォ驥崎、縺吶k繧ュ繝シ蛟、繧呈諺蜈・縺励h縺縺ィ縺励◆蝣エ蜷医ョ繧ィ繝ゥ繝シ蠢懃ュ(隕丞ョ壼、シ唹FF)
縲ONシ夊ュヲ蜻願。ィ遉コ縲ゆク諢乗ァ蛻カ邏縺ォ驕募渚縺吶k陦後ョ縺ソ縺悟、ア謨励☆繧九
縲OFFシ壹お繝ゥ繝シ陦ィ遉コ縲よ諺蜈・謫堺ス懷ィ菴薙′繝ュ繝シ繝ォ繝舌ャ繧ッ縺輔l繧九
縲IGNORE_DUP_KEY = OFF,
縲
縲繧、繝ウ繝繝繧ッ繧ケ謫堺ス懈凾縲∝渕陦ィ繝サ髢「騾」繧、繝ウ繝繝繧ッ繧ケ繧偵け繧ィ繝ェ繧繝繝シ繧ソ螟画峩縺ァ菴ソ逕ィ(譌「螳壼、シ唹FF)
縲ONLINE = OFF,
縲
縲陦後Ο繝繧ッ險ア蜿ッ(譌「螳壼、シ唹N)
縲ALLOW_ROW_LOCKS = ON,
縲
縲繝壹シ繧ク繝ュ繝繧ッ險ア蜿ッ(譌「螳壼、シ唹N)
縲ALLOW_PAGE_LOCKS = ON
縲
) ON [PRIMARY]
蛻カ邏縺ョ霑ス蜉
ALTER TABLE 陦ィ蜷 ADD(CONSTRAINT 蛻カ邏蜷 PRIMARY KEY(蛻怜錐));
蜿医ッALTER TABLE MODIFY(譌「蟄伜怜錐 PRIMARY KEY);
NOT NULL蛻カ邏
ALTER TABLE 陦ィ蜷 MODIFY(蛻怜錐 NOT NULL);
FOREIGN KEY蛻カ邏
ALTER TABLE 陦ィ蜷 ADD CONSTRAINT 蛻カ邏蜷
縲FOREIGN KEY (蛻怜錐) REFERENCES 陦ィ蜷(蛻怜錐);
繧ィ繝ゥ繝シ
ALTER TABLE ADD (譌「蟄伜怜錐 PRIMARY KEY);
ALTER TABLE 陦ィ蜷 ALTER COLUMN 蛻怜錐 SET NOT NULL
FOREIGN KEY蛻カ邏
ALTER TABLE 陦ィ蜷 ADD CONSTRAINT 蛻カ邏蜷 FOREIGN KEY (蛻怜錐1, 蛻怜錐2)
蛻カ邏縺ョ蛛懈ュ「
set FOREIGN_KEY_CHECKS=0;
螟夜Κ繧ュ繝シ蛻カ邏譛牙柑
set FOREIGN_KEY_CHECKS=1;
蛻カ邏縺ョ蜑企勁
NOT NULL蛻カ邏縺ョ蜑企勁(NULL險ア螳ケ)
縲蛻カ邏縺ョ螟画峩縲
NOT NULL蛻カ邏縺ョ蜑企勁(NULL險ア螳ケ)
縲蛻カ邏縺ョ螟画峩縲
蛻カ邏縺ョ螟画峩
ALTER TABLE 陦ィ蜷 MODIFY(蛻怜錐 NULL);
ALTER TABLE 陦ィ蜷 ALTER COLUMN 蛻怜錐 DROP NOT NULL;
螻樊ァ
險俶カ蝓
縲縲蛻怜錐1 蝙1,
縲縲蛻怜錐2 蝙2
縲)
縲菴懈仙郁。ィ鬆伜沺
縲TABLESPACE (陦ィ鬆伜沺蜷)
縲縲DBA 險俶カ蝓/陦ィ鬆伜沺縲榊盾辣ァ
縲
縲繧オ繧、繧コ
縲STORAGE(
縲縲INITIAL 5M縲蛻晄悄繧ィ繧ッ繧ケ繝繝ウ繝医し繧、繧コ
縲縲NEXT 10M縲蠅怜繧ィ繧ッ繧ケ繝繝ウ繝医し繧、繧コ
縲縲MINEXTENTS 4縲譛蟆上お繧ッ繧ケ繝繝ウ繝域焚
縲縲MAXEXTENTS UNLIMITED縲譛螟ァ繧ィ繧ッ繧ケ繝繝ウ繝域焚
縲縲PCTINCREASE 100縲繧ィ繧ッ繧ケ繝繝ウ繝医し繧、繧コ諡。螟ァ邇
縲縲MAXSIZE 100M縲陦ィ縺ョ荳企剞
縲縲縲DBA 險俶カ蝓/繧ィ繧ッ繧ケ繝繝ウ繝縲榊盾辣ァ
縲);
隱ュ蜿門ーら畑螻樊ァ/譖ク霎シ蜿ッ閭ス螻樊ァ
Create/Alter Table 陦ィ蜷
縲縲ス
縲縲Read Only;
窶サINSERT/UPDATE/DELETE/TRUNCATE/SELECT FOR UPDATE荳榊庄
窶サSELECT/DROP TABLE縺ッ蜿ッ閭ス
SELECT FOR UPDATE縺ォ髢「縺励※縺ッ縲SQL DCLシ壹ヨ繝ゥ繝ウ繧カ繧ッ繧キ繝ァ繝ウ繝サ讓ゥ髯千ョ。逅/隱ュ縺ソ蜿悶j縺ョ荳雋ォ諤ァ縲榊盾辣ァ
譖ク霎シ蜿ッ閭ス螻樊ァ
Create/Alter Table 陦ィ蜷
縲縲ス
縲縲Read Write;