我有一个主键定义为字符串值。 所以存在两个主键为“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) = 0Try this and let me know. Have not verified it for syntax.
更多推荐
发布评论