英文:
What is the maximum column varchar size for Primary columns in Oracle
问题
我正在尝试创建一个varchar(4000)列，并在其上创建主键，但出现错误。然而，当我尝试创建一个varchar(1599)列并在其上添加主键时，一切正常。
那么，这是否意味着Oracle中主键列的最大大小为varchar(1599)？
Oracle版本 => 19.16.0.0.0
我尝试的方式如下：
CREATE TABLE 表名( PK1_name varchar(4000 char) PRIMARY KEY NOT NULL);
英文:
I am trying to create a column with varchar(4000) and creating a primary key on it, is throwing error. While when I am trying to create a column with varchar(1599) and adding a primary key on it, it works fine.
So a does that mean the max size of the primary column in oracle is varchar(1599) ?
Oracle version => 19.16.0.0.0
I am trying like:
CREATE TABLE table( PK1_name varchar(4000 char) PRIMARY KEY NOT NULL);
答案1
得分: 3
-
ORA-01450: maximum key length (string) exceeded
-
错误: 在CREATE INDEX语句中指定的所有列的组合长度超过了最大索引长度。最大索引长度因操作系统而异。
-
总索引长度计算为所有索引列的宽度之和加上索引列的数量。
-
日期字段的长度为7，字符字段具有其定义的长度，数字字段的长度为22。数字长度=（精度/2）+1。如果为负数，则加上+1。
-
操作: 选择要索引的列，以便总索引长度不超过操作系统的最大索引长度。
-
也请参阅您的操作系统特定的Oracle文档。
-
-
在我的数据库中，它可以工作：
SQL> CREATE TABLE test (PK1_name varchar(4000 char) PRIMARY KEY NOT NULL); 表已创建。 SQL>不过，请注意，您发布的代码是无效的（表名不能为
table）。此外，主键不允许空值，因此在指定该列为not null是没有用的。最后，Oracle建议我们使用varchar2数据类型而不是varchar。 -
正如我在评论中提到的，将
varchar2(4000)列作为主键通常不是一个好主意；Oracle的优化器可能选择使用全表扫描而不是使用如此长的索引。我建议您放弃这个想法，为主键创建另一个列；因为您使用的是19c版本，一个选择是使用identity列，所以您可以：SQL> CREATE TABLE test2 2 (id number generated always as identity primary key, 3 name varchar2(4000) 4 ); 表已创建。 SQL> -
[编辑]
至于您的评论：这是在我的数据库中发生的情况：
SQL> CREATE TABLE test1 2 ( 3 col1 VARCHAR (4000) NOT NULL, 4 col2 VARCHAR (4000) NOT NULL, 5 col3 VARCHAR (4000), 6 col4 VARCHAR (4000), 7 col5 VARCHAR (4000) NOT NULL, 8 col6 VARCHAR (4000), 9 col7 VARCHAR (4000), 10 PRIMARY KEY (col1, col2, col3) 11 ); 创建表 test1 * 在行 1 处出现错误: ORA-01450: 最大键长度 (6398) 超出范围 SQL>但是，再次强调：当我看到一堆VARCHAR2(4000)列中的一列，或者其中的几列成为主键时，这似乎是不好的、不好的、不好的数据模型。重新考虑您正在做的事情。
英文:
> ORA-01450: maximum key length (string) exceeded
>
> Cause: The combined length of all the columns specified in a CREATE
> INDEX statement exceeded the maximum index length. The maximum index
> length varies by operating system.
>
> The total index length is computed as the sum of the width of all
> indexed columns plus the number of indexed columns.
>
> Date fields have a length of 7, character fields have their defined
> length, and numeric fields have a length of 22. Numeric length =
> (precision/2) + 1. If negative, add +1.
>
> Action: Select columns to be indexed so the total index length does
> not exceed the maximum index length for the operating system.
>
> See also your operating system-specific Oracle documentation.
In my database it works:
SQL> CREATE TABLE test ( PK1_name varchar(4000 char) PRIMARY KEY NOT NULL);
Table created.
SQL>
Though, note that code you posted is invalid (table name can't be table). Also, primary keys don't allow nulls anyway, so there's no use in specifying that column to be not null. Finally, Oracle recommends we use varchar2 datatype instead of varchar.
As I commented, choosing varchar2(4000) column as a primary key is rarely a good idea; Oracle's optimizer might choose to use full table scan instead of using such a long index. I'd suggest you to abandon this idea and create another column for a primary key; as you're on 19c, one option is to use an identity column, so you'd then
SQL> create table test2
2 (id number generated always as identity primary key,
3 name varchar2(4000)
4 );
Table created.
SQL>
[EDIT]
As of your comment: this is what happens in my database:
SQL> CREATE TABLE test1
2 (
3 col1 VARCHAR (4000) NOT NULL,
4 col2 VARCHAR (4000) NOT NULL,
5 col3 VARCHAR (4000),
6 col4 VARCHAR (4000),
7 col5 VARCHAR (4000) NOT NULL,
8 col6 VARCHAR (4000),
9 col7 VARCHAR (4000),
10 PRIMARY KEY (col1, col2, col3)
11 );
CREATE TABLE test1
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded
SQL>
But, once again: when I see bunch of VARCHAR2(4000) columns and one - or a few of them - making a primary key, that smells like bad, Bad, BAD data model. Reconsider what you're doing.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论