10、Oracle中的約 束constraint

画个一样的我發表於2024-07-14
OracleAI

最近專案要用到Oracle，奈何之前沒有使用過，所以在B站上面找了一個學習影片，用於記錄學習過程以及自己的思考。
影片連結：
【尚矽谷】Oracle資料庫全套教程，oracle從安裝到實戰應用
如果有侵權，請聯絡刪除，謝謝。

學習目標：

  • 描述約束

  • 建立和維護約束

1、什麼是約束

約束是表級的強制規定

有以下五種約束:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK

注意事項

  • 如果不指定約束名 ，Oracle server 自動按照 SYS_Cn 的格式指定約束名

  • 建立和修改約束:

    • 建表的同時
    • 建表之後

  • 可以在表級或列級定義約束

  • 可以透過資料字典檢視檢視約束

1.1、表級約束和列級約束

作用範圍：
①列級約束只能作用在一個列上
②表級約束可以作用在多個列上（當然表級約束也可以作用在一個列上）

定義方式： 列約束必須跟在列的定義後面，表約束不與列一起，而是單獨定義。

非空(not null) 約束只能定義在列上

2、定義約束

格式：

CREATE TABLE [schema.]table
	    (column datatype [DEFAULT expr]
		[column_constraint],
		...
		[table_constraint][,...]);

示例：

CREATE TABLE employees(
  	     employee_id  NUMBER(6),
    	     first_name   VARCHAR2(20),
  	     ...
  	     job_id       VARCHAR2(10) NOT NULL,
	     CONSTRAINT emp_emp_id_pk 
		           	PRIMARY KEY (EMPLOYEE_ID));

列級

column [CONSTRAINT constraint_name] constraint_type,

表級

column,...
  [CONSTRAINT constraint_name] constraint_type
  (column, ...),

3、NOT NULL 約束

保證列值不能為空:

只能定義在列級:

DBeaver 透過下面方式檢視：選中檢視的表 -> 屬性 -> 約束 即可檢視。

4、UNIQUE 約束

唯一約束，允許出現多個空值：NULL。

可以定義在表級或列級:

CREATE TABLE employees(
    employee_id      NUMBER(6),
    last_name        VARCHAR2(25) UNIQUE,   -- 系統命名
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    commission_pct   NUMBER(2,2),
    hire_date        DATE NOT NULL,
...  
    CONSTRAINT emp_email_uk UNIQUE(email));  -- 使用者自定義命名

可以宣告在email後面：CONSTRAINT emp_email_uk UNIQUE,也可以如此末處宣告。

5、PRIMARY KEY 約束

可以定義在表級或列級:

CREATE TABLE   departments(
    department_id        NUMBER(4),
    department_name      VARCHAR2(30)
      CONSTRAINT dept_name_nn NOT NULL,
    manager_id           NUMBER(6),
    location_id          NUMBER(4),
      CONSTRAINT dept_id_pk PRIMARY KEY(department_id));  -- 定義主鍵

6、FOREIGN KEY 約束

可以定義在表級或列級:

CREATE TABLE employees(
    employee_id      NUMBER(6),
    last_name        VARCHAR2(25) NOT NULL,
    email            VARCHAR2(25),
    salary           NUMBER(8,2),
    commission_pct   NUMBER(2,2),
    hire_date        DATE NOT NULL,
...
    department_id    NUMBER(4),
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
      REFERENCES departments(department_id), -- 定義外來鍵
    CONSTRAINT emp_email_uk UNIQUE(email));

6.1、FOREIGN KEY 約束的關鍵字

  • FOREIGN KEY: 在表級指定子表中的列
  • REFERENCES: 標示在父表中的列
  • ON DELETE CASCADE: 級聯刪除。當父表中的列被刪除時，子表中相對應的列也被刪除
  • ON DELETE SET NULL: 級聯置空。子表中相應的列置空
create table emp (
  id number(10) primary key,
  name varchar2(26) unique,
  sal number(8, 2),
  
  dept_id number(4),

  constraint dept_fk foreign key(dept_id) references dept(dept_id) on delete cascade
);

7、CHECK 約束

定義每一行必須滿足的條件

..., salary	NUMBER(2)
     CONSTRAINT emp_salary_min  
            CHECK (salary > 0),...

示例：

create table emp (
  id number(10) primary key,
  name varchar2(26) unique,
  sal number(8, 2) check(sal > 0 and sal < 1000000),
  
  dept_id number(4),

  constraint dept_fk foreign key(dept_id) references dept(dept_id) on delete set null
);

8、新增約束的語法

使用 ALTER TABLE 語句:

  • 新增或刪除約束,但是不能修改約束
  • 有效化或無效化約束
  • 新增 NOT NULL 約束要使用 MODIFY 語句
  ALTER TABLE	 table
  ADD [CONSTRAINT constraint] type (column);

以create table emp as select * from employees;為例，新增和刪除約束

alter table emp modify(empname varchar2(50) not null);

8.1、新增約束

新增約束舉例

ALTER TABLE     employees
ADD CONSTRAINT  emp_manager_fk 
  FOREIGN KEY(manager_id) 
  REFERENCES employees(employee_id);
Table altered.

8.2、刪除約束

從表 EMPLOYEES 中刪除約束

ALTER TABLE      employees
DROP CONSTRAINT  emp_manager_fk;
Table altered.

8.3、無效化約束

在ALTER TABLE 語句中使用 DISABLE 子句將約束無效化。

ALTER TABLE		employees
DISABLE CONSTRAINT	emp_emp_id_pk;
Table altered.

8.4、啟用約束

  • ENABLE 子句可將當前無效的約束啟用
ALTER TABLE		employees
ENABLE CONSTRAINT	emp_emp_id_pk;
Table altered.
  • 當定義或啟用UNIQUE 或 PRIMARY KEY 約束時系統會自動建立UNIQUE 或 PRIMARY KEY索引

8.5、查詢約束

查詢資料字典檢視 USER_CONSTRAINTS

SELECT	constraint_name, constraint_type,
	search_condition
FROM	user_constraints
WHERE	table_name = 'EMPLOYEES';


結果：
CONSTRAINT_NAME |CONSTRAINT_TYPE|SEARCH_CONDITION       |
----------------+---------------+-----------------------+
EMP_LAST_NAME_NN|C              |"LAST_NAME" IS NOT NULL|
EMP_EMAIL_NN    |C              |"EMAIL" IS NOT NULL    |
EMP_HIRE_DATE_NN|C              |"HIRE_DATE" IS NOT NULL|
EMP_JOB_NN      |C              |"JOB_ID" IS NOT NULL   |
EMP_SALARY_MIN  |C              |salary > 0             |
EMP_EMAIL_UK    |U              |                       |
EMP_EMP_ID_PK   |P              |                       |
EMP_DEPT_FK     |R              |                       |
EMP_JOB_FK      |R              |                       |
EMP_MANAGER_FK  |R              |                       |

8.6、查詢定義約束的列

查詢資料字典檢視 USER_CONS_COLUMNS

SELECT	constraint_name, column_name
FROM	user_cons_columns
WHERE	table_name = 'EMPLOYEES';



結果：
CONSTRAINT_NAME |COLUMN_NAME  |
----------------+-------------+
EMP_LAST_NAME_NN|LAST_NAME    |
EMP_EMAIL_NN    |EMAIL        |
EMP_HIRE_DATE_NN|HIRE_DATE    |
EMP_JOB_NN      |JOB_ID       |
EMP_SALARY_MIN  |SALARY       |
EMP_EMAIL_UK    |EMAIL        |
EMP_EMP_ID_PK   |EMPLOYEE_ID  |
EMP_DEPT_FK     |DEPARTMENT_ID|
EMP_JOB_FK      |JOB_ID       |
EMP_MANAGER_FK  |MANAGER_ID   |

相關文章