T-SQL的CREATE TABLE語法(下)

 

PRIMARY KEY 約束

    * 一個表只能包含一個 PRIMARY KEY 約束。
    * 由 PRIMARY KEY 約束生成的索引不能使表中的非聚集索引超過 249 個，聚集索引超過 1 個。
    * 如果沒有在 PRIMARY KEY 約束中指定 CLUSTERED 或 NONCLUSTERED，並且沒有為 UNIQUE 約束指定聚集索引，則將對該 PRIMARY KEY 約束使用 CLUSTERED。
    * 在 PRIMARY KEY 約束中定義的所有列都必須定義為 NOT NULL。如果沒有指定為空性，加入 PRIMARY KEY 約束的所有列的為空性都將設定為 NOT NULL。

UNIQUE 約束

    * 如果 UNIQUE 約束中沒有指定 CLUSTERED 或 NONCLUSTERED，則預設為 NONCLUSTERED。
    * 每個 UNIQUE 約束都生成一個索引。由 UNIQUE 約束生成的索引不能使表中的非聚集索引超過 249 個，聚集索引超過 1 個。

FOREIGN KEY 約束

    * 如果在 FOREIGN KEY 約束的列中輸入非 NULL 值，則此值必須在被引用的列中存在，否則將返回違反外來鍵約束的錯誤資訊。
    * FOREIGN KEY 約束應用於前面所講的列，除非指定了源列。
    * FOREIGN KEY 約束僅能引用位於同一伺服器上的同一資料庫中的表。資料庫間的引用完整性必須通過觸發器實現。有關更多資訊，請參見 CREATE TRIGGER。
    * FOREIGN KEY 可以引用同一表中的其它列（自引用）。
    * 列級 FOREIGN KEY 約束的 REFERENCES 子句僅能列出一個引用列，且該列必須與定義約束的列具有相同的資料型別。
    * 表級 FOREIGN KEY 約束的 REFERENCES 子句中引用列的數目必須與約束列列表中的列數相同。每個引用列的資料型別也必須與列表中相應列的資料型別相同。
    * 如果 timestamp 型別的列是外來鍵或被引用鍵的一部分，則不能指定 CASCADE。
    * 可以在相互間具有引用關係的表上組合使用 CASCADE 和 NO ACTION。如果 SQL Server 遇到 NO ACTION，將終止執行語句並回滾相關的 CASCADE 操作。當 DELETE 語句導致 CASCADE 和 NO ACTION 組合操作時，在 SQL Server 檢查 NO ACTION 操作之前將執行所有 CASCADE 操作。
    * 一個表最多可包含 253 個 FOREIGN KEY 約束。
    * 對於臨時表不強制 FOREIGN KEY 約束。
    * 每個表在其 FOREIGN KEY 約束中最多可以引用 253 個不同的表。
    * FOREIGN KEY 約束只能引用被引用表的 PRIMARY KEY 或 UNIQUE 約束中的列或被引用表上 UNIQUE INDEX 中的列。

DEFAULT 定義

    * 每列只能有一個 DEFAULT 定義。
    * DEFAULT 定義可以包含常量值、函式、SQL-92 niladic 函式或 NULL。下表顯示 niladic 函式及其在執行 INSERT 語句時返回的預設值。
      SQL-92 niladic 函式     返回的值
      CURRENT_TIMESTAMP     當前日期和時間。
      CURRENT_USER     執行插入操作的使用者名稱。
      SESSION_USER     執行插入操作的使用者名稱。
      SYSTEM_USER     執行插入操作的使用者名稱。
      USER     執行插入操作的使用者名稱。
    * DEFAULT 定義中的 constant_expression 不能引用表中的其它列，也不能引用其它表、檢視或儲存過程。
    * 不能在資料型別為 timestamp 的列或具有 IDENTITY 屬性的列上建立 DEFAULT 定義。
    * 如果使用者定義資料型別繫結到預設物件，則不能在該使用者定義資料型別的列上建立 DEFAULT 定義。

CHECK 約束

    * 列可以有任意多個 CHECK 約束，並且約束條件中可以包含用 AND 和 OR 組合起來的多個邏輯表示式。列上的多個 CHECK 約束按建立順序進行驗證。
    * 搜尋條件必須取值為布林表示式，並且不能引用其它表。
    * 列級 CHECK 約束只能引用被約束的列，表級 CHECK 約束只能引用同一表中的列。

      當執行 INSERT 和 DELETE 語句時，CHECK CONSTRAINTS 和規則具有相同的資料驗證功能。
    * 當列上存在規則和一個或多個 CHECK 約束時，將驗證所有限制。

其它約束資訊

    * 為約束建立的索引不能用 DROP INDEX 語句除去；必須用 ALTER TABLE 語句除去約束。可以用 DBCC DBREINDEX 語句重建為約束建立的並由其使用的索引。
    * 約束的名稱必須符合識別符號規則，但其名稱的首字元不能為 #。如果沒有提供 constraint_name，則使用系統生成的名稱。約束名將出現在所有與違反約束有關的錯誤資訊中。
    * 當 INSERT、UPDATE 或 DELETE 語句違反約束時，將終止執行該語句。但將繼續處理事務（如果此語句為顯式事務的組成部分）。可以通過檢查系統函式 @@ERROR，在事務定義中使用 ROLLBACK TRANSACTION 語句。

如果某個表具有 FOREIGN KEY 或 CHECK CONSTRAINTS 及觸發器，則將在觸發器執行前先檢查約束條件。

若要獲得關於表及其列的報表，請使用 sp_help 或 sp_helpconstraint。若要重新命名錶，請使用 sp_rename。若要獲得與表相關的檢視和儲存過程的報表，請使用 sp_depends。

通常情況下，為表和索引分配空間時，每次以一個擴充套件盤區為增量單位。當建立表或索引時，首先從混合擴充套件盤區為其分配頁，直到它具有足夠的頁填滿一個統一擴充套件盤區。當有足夠的頁填滿統一擴充套件盤區後，每當當前分配的擴充套件盤區填滿時，將再為其分配另一個擴充套件盤區。若要獲得關於由表分配和佔用的空間量的報表，請執行 sp_spaceused。
表定義中的為空性規則

列的為空性規則決定該列中是否允許以空值 (NULL) 作為其資料。NULL 不是零或空白：它表示沒有輸入任何內容，或提供了一個顯式 NULL 值，通常表示該值未知或不適用。

當用 CREATE TABLE 或 ALTER TABLE 語句建立或更改表時，資料庫或會話設定會影響且可能替代列定義中資料型別的為空性。建議始終將列顯式定義為非計算列的 NULL 或 NOT NULL，如果使用使用者定義資料型別，則建議允許該列使用此資料型別的預設為空性。

在沒有顯式指定時，列的為空性遵循以下規則：

    * 如果該列以使用者定義資料型別定義：
          o SQL Server 使用在建立資料型別時指定的為空性。使用 sp_help 可獲得該資料型別的預設為空性。
    * 如果該列以系統提供的資料型別定義：
          o 如果系統提供的資料型別只有一個選項，則優先使用該選項。timestamp 資料型別只能定義為 NOT NULL。
          o 如果 sp_dbcmptlevel 的設定是 65 或更小，且列沒有顯式定義 NULL 或 NOT NULL，則 bit 資料型別預設為 NOT NULL。有關更多資訊，請參見 sp_dbcmptlevel。
          o 如果有任何會話設定為 ON（用 SET 語句開啟），那麼：

            如果 ANSI_NULL_DFLT_ON 是 ON，則指派 NULL。

            如果 ANSI_NULL_DFLT_OFF 是 ON，則指派 NOT NULL。
          o 如果配置了任何資料庫設定（用 sp_dboption 更改），那麼：

            如果 ANSI null default 為 true，則指派 NULL。

            如果 ANSI null default 為 false，則指派 NOT NULL。
    * 當會話的兩個 ANSI_NULL_DFLT 選項都未設定，且資料庫設定為預設值（ANSI null default 為 false）時，將指派 SQL Server 的預設設定 NOT NULL。
    * 如果該列是計算列，則其為空性總是由 SQL Server 自動確定。使用 COLUMNPROPERTY 函式（AllowsNull 屬性）查詢這類列的為空性。

說明   預設情況下，SQL Server ODBC 驅動程式和用於 SQL Server 的 Microsoft OLE DB 提供程式都將 ANSI_NULL_DFLT_ON 設定為 ON。ODBC 和 OLE DB 使用者可以在 ODBC 資料來源中配置該設定，或通過應用程式設定的連線特性或屬性配置該設定。

許可權

CREATE TABLE 許可權預設授予 db_owner 和 db_ddladmin 固定資料庫角色成員。db_owner 固定資料庫角色成員和 sysadmin 固定伺服器角色成員可以將 CREATE TABLE 許可權轉讓給其他使用者。
示例
A. 使用 PRIMARY KEY 約束

下例顯示在示例資料庫 pubs 的 jobs 表中，job_id 列中具有聚集索引的 PRIMARY KEY 約束的列定義；此例由系統提供約束名。

job_id   smallint
      PRIMARY KEY CLUSTERED

下例顯示如何為 PRIMARY KEY 約束提供名稱。此約束用於 employee 表中的 emp_id 列。此列基於使用者定義資料型別。

emp_id   empid
      CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED

B. 使用 FOREIGN KEY 約束

FOREIGN KEY 約束用於引用其它表。FOREIGN KEY 可以是單列鍵或多列鍵。下例顯示 employee 表上引用 jobs 表的單列 FOREIGN KEY 約束。對於單列 FOREIGN KEY 約束，只需要 REFERENCES 子句。

job_id   smallint      NOT NULL
      DEFAULT 1
      REFERENCES jobs(job_id)

也可以顯式使用 FOREIGN KEY 子句並複述列特性。注意在這兩個表中列名不必相同。

FOREIGN KEY (job_id) REFERENCES jobs(job_id)

多列鍵約束作為表約束建立。在 pubs 資料庫中，sales 表包含多列 PRIMARY KEY。下例顯示如何從其它表中引用此鍵（可選擇顯式約束名）。

CONSTRAINT FK_sales_backorder FOREIGN KEY (stor_id, ord_num, title_id)
   REFERENCES sales (stor_id, ord_num, title_id)

C. 使用 UNIQUE 約束

UNIQUE 約束用於強制非主鍵列的唯一性。PRIMARY KEY 約束列自動包含唯一性限制；但是，UNIQUE 約束允許存在空值。下例顯示錶 authors 中名為 pseudonym 的列。該列強制作者筆名必須唯一。

pseudonym varchar(30)   NULL
UNIQUE NONCLUSTERED

下例顯示在 stor_id 實際上是 PRIMARY KEY 的 stores 表中，stor_name 列和 city 列上建立的 UNIQUE 約束；同一個城市中的商店不應同名。

CONSTRAINT U_store UNIQUE NONCLUSTERED (stor_name, city)

D. 使用 DEFAULT 定義

使用 INSERT 和 UPDATE 語句時，如果沒有提供值，則預設值會提供值。在 pubs 資料庫中，使用了許多 DEFAULT 定義以確保輸入有效的資料或佔位符。

在 jobs 表上，當沒有顯式輸入實際的描述資訊時，預設的字串將提供描述資訊（列 job_desc）。

DEFAULT ''''New Position - title not formalized yet''''

在 employee 表中，員工可以受僱於子公司或母公司。如果沒有顯式提供公司資訊，則輸入母公司（注意在表定義中可以巢狀註釋，如下所示）。

DEFAULT (''''9952'''')
/* By default the Parent Company Publisher is the company
to whom each employee reports. */

除了常量以外，DEFAULT 定義還可以包含函式。使用下例獲取輸入項的當前日期：

DEFAULT (getdate())

niladic 函式也可以提高資料的完整性。若要跟蹤插入行的使用者，請使用 niladic 函式 USER（niladic 函式不使用括號）：

DEFAULT USER

E. 使用 CHECK 約束

下例顯示對輸入到 jobs 表中的 min_lvl 列和 max_lvl 列的值的限制。這兩個約束都未命名：

CHECK (min_lvl >= 10)

與

CHECK (max_lvl <= 250)

下例顯示對輸入到 employee 表的 emp_id 列中的字元資料具有模式限制的命名約束。

CONSTRAINT CK_emp_id CHECK (emp_id LIKE
   ''''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''' OR
   emp_id LIKE ''''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''')

下例指定 pub_id 必須在特定的列表中或遵循給定的模式。此約束用於 publishers 表中的 pub_id 列。

CHECK (pub_id IN (''''1389'''', ''''0736'''', ''''0877'''', ''''1622'''', ''''1756'''')
   OR pub_id LIKE ''''99[0-9][0-9]'''')

F. 完整的表定義

下例顯示 pubs 資料庫中所建立的三個表（jobs、employee 和 publishers）的完整表定義，其中包含所有的約束定義。

/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
   job_id  smallint
      IDENTITY(1,1)
      PRIMARY KEY CLUSTERED,
   job_desc        varchar(50)     NOT NULL
      DEFAULT ''''New Position - title not formalized yet'''',
   min_lvl tinyint NOT NULL
      CHECK (min_lvl >= 10),
   max_lvl tinyint NOT NULL
      CHECK (max_lvl <= 250)
)

/* ************************* employee table ************************* */
CREATE TABLE employee
(
   emp_id  empid
      CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
      CONSTRAINT CK_emp_id CHECK (emp_id LIKE
         ''''[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]'''' or
         emp_id LIKE ''''[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]''''),
      /* Each employee ID consists of three characters that
      represent the employee''''s initials, followed by a five
      digit number ranging from 10000 through 99999 and then the
      employee''''s gender (M or F). A (hyphen) - is acceptable
      for the middle initial. */
   fname   varchar(20)     NOT NULL,
   minit   char(1) NULL,
   lname   varchar(30)     NOT NULL,
   job_id  smallint        NOT NULL
      DEFAULT 1
      /* Entry job_id for new hires. */
      REFERENCES jobs(job_id),
   job_lvl tinyint
      DEFAULT 10,
      /* Entry job_lvl for new hires. */
   pub_id  char(4) NOT NULL
      DEFAULT (''''9952'''')
      REFERENCES publishers(pub_id),
      /* By default, the Parent Company Publisher is the company
      to whom each employee reports. */
   hire_date       datetime        NOT NULL
      DEFAULT (getdate())
      /* By default, the current system date is entered. */
)

/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
   pub_id  char(4) NOT NULL
         CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
         CHECK (pub_id IN (''''1389'''', ''''0736'''', ''''0877'''', ''''1622'''', ''''1756'''')
            OR pub_id LIKE ''''99[0-9][0-9]''''),
   pub_name      varchar(40)     NULL,
   city         varchar(20)     NULL,
   state      char(2) NULL,
   country      varchar(30)     NULL
            DEFAULT(''''USA'''')
)

G. 在列中使用 uniqueidentifier 資料型別

下例建立含有 uniqueidentifier 列的表。該表使用 PRIMARY KEY 約束以確保使用者不會在表中插入重複的值，

.s0%ON6#D理育

並在 DEFAULT 約束中使用 NEWID() 函式為新行提供值。

CREATE TABLE Globally_Unique_Data
(guid uniqueidentifier
   CONSTRAINT Guid_Default
   DEFAULT NEWID(),
Employee_Name varchar(60),
CONSTRAINT Guid_PK PRIMARY KEY (Guid)
)

H. 對計算列使用表示式

下例顯示如何使用表示式 ((low + high)/2) 計算 myavg 計算列。

CREATE TABLE mytable
   (
    low int,
    high int,
    myavg AS (low + high)/2
   )

I. 對計算列使用 USER_NAME 函式

下例在 myuser_name 列中使用 USER_NAME 函式。

CREATE TABLE mylogintable
   (
    date_in datetime,
    user_id int,
    myuser_name AS USER_NAME()
   )

J. 使用 NOT FOR REPLICATION

下例顯示如何在訂閱了複製的表中使用 IDENTITY 屬性。此表包含 CHECK 約束，7無hw網yP*)4?r以確保此係統生成的 SaleID 值不會增長到為複製釋出伺服器指派的範圍內。

CREATE TABLE Sales
   (SaleID INT IDENTITY(100000,1) NOT FOR REPLICATION,
             CHECK NOT FOR REPLICATION (SaleID <= 199999),
    SalesRegion CHAR(2),
   CONSTRAINT ID_PK PRIMARY KEY (SaleID)
   )

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-584393/，如需轉載，請註明出處，否則將追究法律責任。