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側で主キーを持たせていない場合とか、見受けられることもありますね。後から主キーを追加したりするときに、覚えておくといいのではないかと思います。

今日は以上まで

にほんブログ村

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

ぽかぽか天気、あったかそうでしょ！

  ＜自己紹介＞

10年前、「ＩＴコーディネータは、真に経営に役立つＩＴ利活用に向け、経営者の立場に立った助言・支援を行い、ＩＴ経営を実現する人材です。」という紹介にあるような人材となるべく、高額のセミナーに参加し（ただし会社に出してもらったのだけど）、必死になって新しいことを勉強した日々。。。
ITCのホームページ：http://www.itc.or.jp

けれども、ずっと委託業務で他の会社に常駐しての仕事だったため、ITコーディネータを前面に出すような仕事は一切なく、システムの開発とデータベースからのデータの抽出・分析などの仕事をする毎日でした。 ただ、資格を継続するのも実は大変なんですね。

過去3年間で30ポイントを取っていることが継続の条件ですが、セミナー代金や資格更新料など、経費もかかっちゃいます。

  自己紹介はこれくらいにして・・・

せっかくなので、システムの開発とデータベースからのデータの抽出・分析などの仕事で学んだ内容を、少し整理しようと思っています。

＜本日の題材＞
【テーブル作成後の定義変更のSQL】

仕事によってOracle、SQL Serverを使うことがよくありますが、テーブル作成後の定義変更の仕方が、OracleとSQL ServerなどRDBによって微妙に異なるため、ときにすぐに思い出せなくて困ってしまうことがあります。
それで、ちょっと覚書の意味もあわせてまとめてみます。

【項目の追加】
Oracle：
ALTER  TABLE  テーブル名  ADD (列名 列定義);
SQL Server：
ALTER  TABLE テーブル名  ADD  列名 列定義;

例）追加する項目が複数の場合
Oracle：
ALTER  TABLE  社員マスタ  ADD (性別 VARCHAR2(4), 備考 VARCHAR2(100));

SQL Server：
ALTER  TABLE  社員マスタ  ADD  性別 VARCHAR(4), 備考 VARCHAR(100);

追加する項目にNOT NULL制約をつけて、デフォルト値を設定したい場合

例）test_tabテーブルにcol_a という数値項目をNOT NULL制約(デフォルト値 0)をつける

Oracle：
ALTER  TABLE  test_tab  ADD (col_a  NUMBER(4) DEFAULT 0  NOT NULL);

SQL Server：
ALTER  TABLE  test_tab  ADD  col_a  INT  DEFAULT 0 NOT NULL;

【列定義の変更】
Oracle： ALTER  TABLE  テーブル名 MODIFY (列名 列定義); SQL Server： ALTER  TABLE  テーブル名 ALTER COLUMN  列名 列定義;

例）文字項目の桁数を増やす場合（社員名を40バイトから50バイトに変更）
Oracle：
ALTER TABLE 社員マスタ MODIFY (社員名 VARCHAR2(50));

SQL Server：
ALTER TABLE  社員マスタ  ALTER  COLUMN  社員名 VARCHAR(50);

【項目の削除】
Oracle： ALTER  TABLE  テーブル名 DROP (列名);
SQL Server： ALTER  TABLE  テーブル名 DROP COLUMN 列名;

例）社員マスタに「性別」「備考」の削除
Oracle： ALTER  TABLE  社員マスタ  DROP (性別, 備考);
SQL Server： ALTER  TABLE 社員マスタ  DROP  COLUMN  性別, 備考;

【項目名の変更】
Oracle：
ALTER  TABLE  テーブル名 RENAME COLUMN  列名 TO  新列名;

SQL Server：
sp_rename @objname='テーブル名.元項目名', @newname='新項目名', @objtype='COLUMN';

例) 社員マスタの「備考」という項目名を「備考１」という名前に変更する。
Oracle：
ALTER  TABLE  社員マスタ RENAME COLUMN 備考 TO 備考１;

SQL Server：
sp_rename @objname='dbo.社員マスタ.備考', @newname='備考１', @objtype='COLUMN';

※ただし、SQLServerではManagement StudioなどのGUIツールで変更できますし、実際の運用では、項目名の変更を後から行うことはあまりないとは思いますが。

今日は以上まで

にほんブログ村