Oracle/PLSQL: 主键(Primary Key)用法
1 目标
通过示例讲解如何创建、删除、禁用和开启主键。
2 前言之-什么是主键
在Oracle中,主键指能唯一标识一条记录的单个数据表列或联合的数据表列(联合主键|复合主键)。主键用到的数据表列数据不能包含空值。而且,一张表只能包含一个主键。
说明:在Oracle数据库中,联合主键的列不能超过32个。主键可以在创建表时定义或者通过ALTER TABLE语法定义。
3 创建主键之 - 在创建表时定义主键
单列主键示例:
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)--联合主键);
4 创建主键之 - 使用alter table语法
语法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);
5 禁用主键
语法:ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;示例:alter table tb_employees disable constraint tb_employees_pk;
6 启用主键
语法:ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
示例:alter table tb_employees enable constraint tb_employees_pk;
7 删除主键
语法: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;-------------------------------------------------------------------------------------------------------------------