Oracle/PLSQL: 主鍵(Primary Key)用法
通過示例講解如何創建、刪除、禁用和開啟主鍵。
在Oracle中,主鍵指能唯一標識一條記錄的單個數據表列或聯合的數據表列(聯合主鍵|復合主鍵)。主鍵用到的數據表列數據不能包含空值。而且,一張表只能包含一個主鍵。
說明:在Oracle數據庫中,聯合主鍵的列不能超過32個。主鍵可以在創建表時定義或者通過ALTER TABLE語法定義。
單列主鍵示例:
CREATE TABLE TB_PK_EXAMPLE ( ID number, NAME varchar2(50), DESCRIPTION varchar2(300), CONSTRAINT TB_PK_EXAMPLE_PK PRIMARY KEY(ID)--定義主鍵 );聯合主鍵示例:
CREATE TABLE TB_SUPPLIER_EX ( supplier_id number, supplier_name varchar2(50), supplier_description varchar2(300), contact_name varchar2(50), constraint TB_SUPPLIER_EX_PK primary key(supplier_id, supplier_name)--聯合主鍵 );
語法
ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (col1, col2,...coln);
示例准備
先創建兩張表(tb_employees和tb_departments),腳本如下:
create table tb_employees ( employee_id number, employee_name varchar2(50), employee_age number, employee_birth date, department_id number ); create table tb_departments ( department_id number, department_name varchar2(100), location varchar2(300) );同過alter table語法創建主鍵:
--單列主鍵 alter table tb_employees add constraint tb_employees_pk primary key (employee_id); --聯合主鍵 alter table tb_departments add constraint tb_departments_pk primary key (department_id,department_name);
語法:
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;示例:
alter table tb_employees disable constraint tb_employees_pk;
語法:
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
示例:
alter table tb_employees enable constraint tb_employees_pk;
語法:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
示例:
alter table tb_employees drop constraint tb_employees_pk; alter table tb_departments drop constraint tb_departments_pk; alter table TB_PK_EXAMPLE drop constraint TB_PK_EXAMPLE_PK; alter table TB_SUPPLIER_EX drop constraint TB_SUPPLIER_EX_PK;-------------------------------------------------------------------------------------------------------------------
如果您們在嘗試的過程中遇到什麼問題或者我的代碼有錯誤的地方,請給予指正,非常感謝!
聯系方式:[email protected]
版權@:轉載請標明出處!