CREATE TABLE æ–‡

CREATE TABLEæ–‡ã?«ã‚ˆã‚Šè¡¨ã‚’作æˆ?ã?—ã?¾ã?™ã€‚表ã?«ã?¯åˆ—ã?Šã‚ˆã?³åˆ¶ç´„ã?Œã?‚ã?£ã?¦ã€?情報ã?Œæ²¿ã?†ã?¹ã??è¦?則ã?Œæ±ºã‚?られã?¦ã?„ã?¾ã?™ã€‚表ã?”ã?¨ã?®åˆ¶ç´„ã?¯åˆ—ã?®é›†å?ˆã?ªã?„ã?—ã?¯åˆ—を指定ã?—ã?¾ã?™ã€‚列ã?«ã?¯ãƒ‡ãƒ¼ã‚¿åž‹ã?Œæ±ºã?¾ã?£ã?¦ã?„ã?¦ã€?列ã?®åˆ¶ç´„(列ã?”ã?¨ã?®åˆ¶ç´„)ã?Œã?‚りã?¾ã?™ã€‚

表やデータベースã?®æ‰€æœ‰è€…ã?¯è‡ªå‹•çš„ã?«ä»¥ä¸‹ã?®æ¨©é™?ã‚’æŒ?ã?¡ã€?ã?¾ã?Ÿä»–ã?®ãƒ¦ãƒ¼ã‚¶ã?«ã?“れらã?®æ¨©é™?を与ã?ˆã‚‹äº‹ã?Œã?§ã??ã?¾ã?™ã€‚
  • INSERT
  • SELECT
  • REFERENCES
  • TRIGGER
  • UPDATE
ã?“れらã?®æ¨©é™?を表やデータベースã?®æ‰€æœ‰è€…ã?‹ã‚‰å‰¥å¥ªã?™ã‚‹ã?“ã?¨ã?¯ã?§ã??ã?¾ã?›ã‚“。

制約ã?«ã?¤ã?„ã?¦ã?®æƒ…å ±ã?¯ã€?CONSTRAINT 節をå?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。

列ã?«ã?¯æ—¢å®šå€¤ã‚’与ã?ˆã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚既定値ã?¨ã?¯å€¤ã?ŒæŒ‡å®šã?•れã?ªã?‹ã?£ã?Ÿã?¨ã??ã€?列ã?«æŒ¿å…¥ã?•れる値ã?§ã?™ã€‚明示ã?•れã?ªã?„å ´å?ˆåˆ—ã?®æ—¢å®šå€¤ã?¯NULLã?§ã?™ã€‚詳細ã?¯åˆ—ã?®æ—¢å®šå€¤ã‚’å?‚ç…§ã?—ã?¦ã??ã? ã?•ã?„。

SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTYã?¨ã?„ã?†ã‚·ã‚¹ãƒ†ãƒ ã?®æ‰‹ç¶šã‚’呼ã?¶ã?“ã?¨ã?§ã€?ページサイズã?®ã‚ˆã?†ã?ªè¨˜éŒ²ã?®å±žæ€§ã‚’設定ã?™ã‚‹ã?“ã?¨ã?Œã?§ã??ã?¾ã?™ã€‚

表をé™?定ã?™ã‚‹å ´å?ˆã€?スキーマå??ã?¯SYSã?§å§‹ã?¾ã?£ã?¦ã?¯ã?ªã‚Šã?¾ã?›ã‚“。

æ§‹æ–‡

CREATE TABLEã?«ã?¯äºŒé€šã‚Šã?®æ›¸ã??æ–¹ã?Œã?‚りã?¾ã?™ã€‚ã?“れã?¯ã€?列ã?®å®šç¾©ã?¨åˆ¶ç´„を指定ã?—よã?†ã?¨ã?™ã‚‹ã?‹ã€?å•?ã?„å?ˆã‚?ã?›å¼?ã?®çµ?æžœã?«åŸºã?¥ã??列を定義ã?™ã‚‹ã?‹ã?«ã‚ˆã‚Šã?¾ã?™ã€‚

CREATE TABLE 表å??
    ( {列定義 | 表毎制約}
    [ , {列定義 | 表毎制約} ] * )
  |
      [ ( 列å?? [ , 列å?? ] * ) ]
      AS ������
      WITH NO DATA
   }

例

CREATE TABLE HOTELAVAILABILITY
     (HOTEL_ID INT NOT NULL, BOOKING_DATE DATE NOT NULL,
	ROOMS_TAKEN INT DEFAULT 0, PRIMARY KEY (HOTEL_ID, BOOKING_DATE));
-- 表ã?§ä¸»ã‚­ãƒ¼ã‚’定義ã?™ã‚‹ã?“ã?¨ã?§ã€?2列ã?‹ã‚‰ã?ªã‚‹ä¸»ã‚­ãƒ¼ã‚’定義ã?§ã??ã?¾ã?™ã€‚
PRIMARY KEY (hotel_id, booking_date))
-- 識別�列�属性を�INTEGER�列�与��
-- �ら�列�主キー制約を定義���。
CREATE TABLE PEOPLE
	(PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY
	CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26));
-- 識別�列�属性を�SMALLINT�列�与��
-- �期値を5�増分値を5�設定���。
CREATE TABLE GROUPS
	(GROUP_ID SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY 
	(START WITH 5, INCREMENT BY 5), ADDRESS VARCHAR(100), PHONE VARCHAR(15));
注: ã?•ã?¾ã?–ã?¾ã?ªåˆ¶ç´„を使ã?£ã?Ÿã€?より多ã??ã?®CREATE TABLEæ–‡ã?®ä¾‹ã?ŒCONSTRAINT 節ã?«ã?‚りã?¾ã?™ã€‚

CREATE TABLE ... AS ...

ã‚‚ã?†ä¸€ã?¤ã?®CREATE TABLEæ–‡ã?®æ›¸ã??æ–¹ã?¯ã€?å•?ã?„å?ˆã‚?ã?›ã?§åˆ—ã?®å??å‰?ã?¨/ã?‚ã‚‹ã?„ã?¯åˆ—ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã‚’指定ã?—ã?¾ã?™ã€‚å•?ã?„å?ˆã‚?ã?›çµ?æžœã?®åˆ—ã?¯ã€?æ–°ã?—ã?„表ã?®å?„列を作æˆ?ã?™ã‚‹ã?¨ã??ã?«ãƒ¢ãƒ‡ãƒ«ã?¨ã?—ã?¦åˆ©ç”¨ã?•れã?¾ã?™ã€‚

æ–°ã?—ã?„表ã?«åˆ—å??ã?ŒæŒ‡å®šã?•れã?ªã?‹ã?£ã?Ÿå ´å?ˆã€?å•?ã?„å?ˆã‚?ã?›ã?®çµ?æžœã?®å…¨åˆ—ã?Œã€?æ–°ã?—ã?„表ã?«å?Œã?˜å??å‰?ã?¨å¯¾å¿œã?™ã‚‹ãƒ‡ãƒ¼ã‚¿åž‹ã?§ä½œæˆ?ã?•れã?¾ã?™ã€‚ ã?²ã?¨ã?¤ã?‚ã‚‹ã?„ã?¯ã??れ以上ã?®åˆ—ã?®å??å‰?ã?Œæ–°ã?—ã?„表ã?«æŒ‡å®šã?•れã?Ÿå ´å?ˆã€?å•?ã?„å?ˆã‚?ã?›å¼?ã?®çµ?æžœã?«ã?¯å?Œæ•°ã?®åˆ—ã?Œç„¡ã?‘れã?°ã?ªã‚Šã?¾ã?›ã‚“。å•?ã?„å?ˆã‚?ã?›çµ?æžœã?®åˆ—ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?¯æ–°ã?—ã?„表ã?®å¯¾å¿œã?™ã‚‹åˆ—ã?«å¼•ã??ç¶™ã?Œã‚Œã?¾ã?™ã€‚

WITH NO DATA節ã?¯å•?ã?„å?ˆã‚?ã?›çµ?果行ã?®ãƒ‡ãƒ¼ã‚¿ã?¯ä½¿ã‚?れã?šã€?列ã?®å??ç§°ã?¨åž‹ã? ã?‘ã?Œåˆ©ç”¨ã?•れるã?“ã?¨ã‚’表ã?—ã?¾ã?™ã€‚ WITH NO DATAã?¯å¿…ã?šæŒ‡å®šã?—ã?¦ã??ã? ã?•ã?„。今後ã?®ãƒªãƒªãƒ¼ã‚¹ã?«ã?¦ã€?Derbyã?§ã‚‚WITH DATAを指定ã?—ã?¦ã€?å•?ã?„å?ˆã‚?ã?›å¼?ã?®çµ?果を新è¦?作æˆ?ã?•れã?Ÿè¡¨ã?«æŒ¿å…¥ã?™ã‚‹ã‚ˆã?†ã?«å‡ºæ?¥ã‚‹ã‚ˆã?†ã?«ã?ªã‚‹ã?‹ã‚‚ã?—れã?¾ã?›ã‚“。ã?—ã?‹ã?—ç?¾åœ¨ã?®ãƒªãƒªãƒ¼ã‚¹ã?§ã?¯ã€?WITH NO DATAã?®æ–‡ã?—ã?‹å‡¦ç?†ã?•れã?¾ã?›ã‚“。

例


-- 既存�表�全列�データ型を使��新��表を作��る。
CREATE TABLE T3 AS SELECT * FROM T1 WITH NO DATA;
-- 列ã?«å??å‰?を指定ã?—ã?¦è¡¨ã‚’作æˆ?ã?™ã‚‹ã€‚ã??ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?¯æ—¢å­˜è¡¨ã?®åˆ—ã?«æ²¿ã?£ã?Ÿã‚‚ã?®ã?¨ã?™ã‚‹ã€‚
CREATE TABLE T3 (A,B,C,D,E) AS SELECT * FROM T1 WITH NO DATA;
-- 列ã?«å??å‰?を指定ã?—ã?¦è¡¨ã‚’作æˆ?ã?™ã‚‹ã€‚ã??ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?¯æ—¢å­˜è¡¨ã?®æŒ‡å®šã?•れã?Ÿåˆ—ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?«æ²¿ã?£ã?Ÿã‚‚ã?®ã?¨ã?™ã‚‹ã€‚
CREATE TABLE T3 (A,B,C) AS SELECT V,DP,I FROM T1 WITH NO DATA;
-- ã?“ã?®ä¾‹ã?§ã?¯å•?ã?„å?ˆã‚?ã?›å¼?ã?®çµ?æžœã?«ã?‚る列ã?«å??å‰?ã?Œã?ªã?„ã?Œã€?ã??ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?¯æ–°è¦?作æˆ?ã?•れã?Ÿè¡¨ã?«ã?¦å¯¾å¿œã?™ã‚‹åˆ—ã?®ãƒ‡ãƒ¼ã‚¿åž‹ã?¨ã?ªã‚‹ã€‚
CREATE TABLE T3 (X,Y) AS SELECT 2*I,2.0*F FROM T1 WITH NO DATA;
関連資料
CREATE FUNCTION æ–‡
CREATE INDEX æ–‡
CREATE PROCEDURE æ–‡
CREATE SCHEMA æ–‡
CREATE SYNONYM æ–‡
CREATE TRIGGER æ–‡
CREATE VIEW æ–‡