如何使JDBC MYSQL中的主键区分大小写？(How to make the primary key in JDBC MYSQL case sensitive?)

如何使JDBC MYSQL中的主键区分大小写？(How to make the primary key in JDBC MYSQL case sensitive?)

我有一个主键定义为字符串值。 所以存在两个主键为“Hello”和“hello”的元组，因为主键不区分大小写。

创建一个主键不区分大小写的表的sql语句是什么？

I have a primary key defined as a string value. So two tuples with primary keys as "Hello" and "hello" cant exist because the primary key is case insensitive.

What will be the sql statement to create a table whose primary key is case insensitive?

最满意答案

您基本上需要使用子查询的检查约束。 但是由于在检查约束中不允许子查询，您可以编写一个运行查询并返回结果的函数:(假设您的PK被称为ID）

create or replace FUNCTION check_id(ID_INPUT IN VARCHAR2) RETURN NUMBER IS count_id NUMBER; Begin SELECT count(*) INTO count_id FROM table WHERE UPPER(ID_INPUT) = UPPER(ID); Return(Count_Id); END;

然后在主键的检查约束中使用此函数

CHECK check_id(ID) = 0

试试这个让我知道。 尚未验证它的语法。

You basically need a check constraint that uses a sub query. But since sub-queries are not permitted in check constraints you can write a function that runs the query and returns a result: (Assuming your PK is called ID)

create or replace FUNCTION check_id(ID_INPUT IN VARCHAR2) RETURN NUMBER IS count_id NUMBER; Begin SELECT count(*) INTO count_id FROM table WHERE UPPER(ID_INPUT) = UPPER(ID); Return(Count_Id); END;

Then use this function in your check constraint for your primary key

CHECK check_id(ID) = 0

Try this and let me know. Have not verified it for syntax.