ITコーディネータのシュウです。

京都に行ったときに撮った梅の花です。

今年も資格更新のためのポイント取得の期限が迫ってきました。3月末までに、何とか頑張って、3年間30ポイントに不足している分を取らないと！　やっと何とかクリアできそうなところに来たんですけどね。。。

会社の仕事もまた忙しくなって来たところなんだけど、頑張るぞ！

ちなみに、ITコーディネータは、プロセスガイドラインというものがあり、IT経営を推進するため、ITコーディネータの備えるべき実践力や仕事の進め方についてガイドラインとしてまとめ、これを公開しています。かなり広範囲でボリュームもあります。
http://www.itc.or.jp/about/guideline/

＜本日の題材＞
前回のテーマだった、テーブル作成後の定義変更のSQLの続き

【主キーの追加】

Oracle/SQL Server：
ALTER TABLE テーブル名 ADD CONSTRAINT 主キー制約名 PRIMARY KEY (列名);

例）社員マスタの社員コードを主キーにする
Oracle：
ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);

Oracleの場合はNOT NULL制約をつけていない項目でも一発で主キーを追加できる（自動的にNOT NULLに変更してくれる)。

SQL> desc 社員マスタ

名前 NULL? 型
------------------ -------- ----------------------------
社員コード VARCHAR2(10)
社員名 VARCHAR2(40)

SQL> ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);

表が変更されました。

SQL> desc 社員マスタ

名前 NULL? 型
------------------- -------------- ----------------------------
社員コード NOT NULL VARCHAR2(10)
社員名 VARCHAR2(40)

しかし、SQL ServerではNOT NULL制約がない場合には、一旦NOT NULLに変更してからでないと主キーを追加できない。

SQL Server：

ALTER TABLE 社員マスタ ALTER COLUMN 社員コード VARCHAR(10) NOT NULL;

ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);

最初にNOT NULL制約をつけてないと、PRIMARY KEYを追加しようとしたときに、エラーになってしまう。

ALTER TABLE 社員マスタ ADD CONSTRAINT PK_社員 PRIMARY KEY (社員コード);
メッセージ8111、レベル16、状態1、行1

テーブル'社員マスタ' のNULL 値が許可される列にPRIMARY KEY 制約を定義できません。

※SQL Serverでも、最初のCREATE TABLE文で主キーを設定する場合は、NOT NULLは記述してなくても自動でNOT NULLになります。

【主キーの削除】

Oracle：以下のどちらかで可能
ALTER TABLE テーブル名 DROP PRIMARY KEY;
ALTER TABLE テーブル名 DROP CONSTRAINT 主キー制約名;

例）
ALTER TABLE 社員マスタ DROP PRIMARY KEY;
または、
ALTER TABLE 社員マスタ DROP CONSTRAINT PK_社員;

ちなみに、制約名がわからないときは、下記のコマンドで検索できる。
SQL>
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE,TABLE_NAME
FROM user_constraints
WHERE TABLE_NAME = '社員マスタ';

CONSTRAINT_NAME C TABLE_NAME
---------------------------- -- ------------------------------
PK_社員 P 社員マスタ

ちなみに、CONSTRAINT_TYPEというのは制約定義のタイプで、値は以下のようなものなどがあります。
「C」CHECK制約
「P」主キー制約（プライマリキー）
「U」一意のキー
「R」参照整合性
「V」ビューでのチェック・オプション付き
「O」ビューで読取り専用

SQL Server：
ALTER TABLE テーブル名 DROP CONSTRAINT 主キー制約名;

例）
ALTER TABLE 社員マスタ DROP CONSTRAINT PK_社員;
もちろん、SQL Serverの場合はGUIツールが使いやすいので、Enterprise Managerでもキーを簡単に削除できる。

主キーの設定し直しも、削除⇒追加で基本問題ないはずです。

RDBの場合、テーブルに主キーを持たせるのは多いとは思いますが、今までACCESSやEXCELで行ってきた業務を、SQL ServerやOracleに移行する場合に、もともとのmdb側で主キーを持たせていない場合とか、見受けられることもありますね。後から主キーを追加したりするときに、覚えておくといいのではないかと思います。

今日は以上まで

にほんブログ村