Win NT strange problem

[st2000]

I am creating a table and I get this error.
This is exclusively happening in one NT machine..
I am able to create the same table on another NT machine and a sun solaris machine..
one of the fileds is unique and has a length of 2k..
on the same machine that had a problem if i reduce the size say varchar2(20), it works.......
what can be done..

FILE_KEY VARCHAR2(2048) CONSTRAINT UNQ_FILE UNIQUE ,


CREATE TABLE FILE_DATASOURCE
*
ERROR at line 1:
ORA-01450: maximum key length (758) exceeded

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.

[marist89]

[QUOTE][i]Originally posted by st2000 [/i]
[B]The maximum index length varies by operating system.
[/B][/QUOTE]
This is probably your problem.

[st2000]

Thanks Jeff.. but how do I find it that the index length is something of that sort or how do I solve it..

[marist89]

I think you have already proven it is an index length problem when you created the index on a varchar2(20). What is the length on NT? I don't know.

The only way you are going to resolve this is by a re-design of the table involved. I would have a table that contains a number as a PK and the file_name as a column. You then change the PK in the file_datasource to the artificial PK in the other table.

[st2000]

Jeff:

I could not interpret your answer on this..

The only way you are going to resolve this is by a re-design of the table involved. I would have a table that contains a number as a PK and the file_name as a column. You then change the PK in the file_datasource to the artificial PK in the other table.

Can you explain again differently..

Thx..

[marist89]

You have a table now:
FILE_DATASOURCE:
FILE_KEY VARCHAR2(2048) PRIMARY KEY,
other fields....

You would break up FILE_DATASOURCE into:
FILE_NAMES:
FILE_ID number(10) PRIMARY KEY,
FILE_NAME varchar2(2048)

and
FILE_DATASOURCE:
FILE_ID number(10),
other fields...

Then, whenever you wanted to get all the information you would have a join on FILE_DATASOURCE and FILE_NAMES via the FILE_ID field.

For simpler queries, you could create a view called FILE_INFO that was defined as:
CREATE VIEW file_info AS
SELECT a.file_name file_key, b.*
FROM file_names a, file_datasource b
WHERE a.file_id = b.file_id

and it would look like your original FILE_DATASOURCE table.

[st2000]

I tried various combos Jeff..
You are right. It cannot exist as a KEY anymore..
thanks for your idea..