在两个varchar字段上创建Unqiue不区分大小写的约束

在两个varchar字段上创建Unqiue不区分大小写的约束

本文介绍了在两个varchar字段上创建Unqiue不区分大小写的约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

在Oracle 10g中,如何在两个varchar字段上添加唯一的不区分大小写的约束?例如,给定表中已存在以下记录:

In Oracle 10g, how do I add a unique case-insensitive constraint on two varchar fields? For example, given the following records already in the table:

"Stephen", "Swensen"
"John", "Smith"

以下插入内容无效:

"stephen", "Swensen"
"John", "smith"
"stephen", "swensen"

但是以下插入内容将是有效的:

But the following inserts would be valid:

"Stephen", "Smith"
"John", "Swensen"

推荐答案

假设您的表名为 person ,名字和姓氏列称为 first_name last_name ,添加以下唯一约束:

Assuming your table is called person, and the first and last name columns are called first_name and last_name, add this unique constraint:

ALTER TABLE person ADD CONSTRAINT person_name_unique
    UNIQUE(LOWER(first_name),LOWER(last_name));

让我知道我是否正确理解了您的问题,并对表的布局做出了正确的假设.

Let me know if I understood your question correctly and made the correct assumptions about your table layout.

这篇关于在两个varchar字段上创建Unqiue不区分大小写的约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-27 21:00