DDL : Alter Primary Key

如何使用 DDL 修改 Primary Key 。
說明:
1.沒有找到直接修改的指令。
2.所以使用方法為先刪除後重新建立。

刪除 Primary key :
語法 : ALTER TABLE [table name]_SETTING DROP CONSTRAINT [primary key index name];
範例 : ALTER TABLE POS_OL_VIP_SETTING DROP CONSTRAINT PrimaryKey;
說明 : POS_OL_VIP_SETTING 為 Table name
PrimaryKey 為 Access 內建的 Primary key index Name 。

重新建立 Primary key :
語法 : ALTER TABLE [table name] ADD CONSTRAINT [primary key index name] Primary Key( [column name],[column name],[column name]);
範例 : ALTER TABLE POS_OL_VIP_SETTING ADD CONSTRAINT PrimaryKey Primary Key ( org_no, type, class_code);
說明 : POS_OL_VIP_SETTING 為 Table name
PrimaryKey 為 Access 內建的 Primary key index Name 。



參考資料如下:http://www.w3schools.com/SQl/sql_primarykey.asp
========== 重點摘要如下 =================
SQL PRIMARY KEY Constraint on ALTER TABLE

To create a PRIMARY KEY constraint on the "P_Id" column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must already have been declared to not contain NULL values (when the table was first created).

To DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID