Non unique keys

[gregwga50]

I am trying to create a table, MYLIB/FILE1 with fields FLD1,FLD2................FLD9 with key fields, FLD1,FLD2,FLD3,FLD4. I was able to create the table OK but the table is created with the requirment of unique keys. How do I create it Unique keys = *NO

[TedHolt]

Re: Non unique keys

Unless you told it to, the table should have been created without unique keys. How did you create it? If you used PRIMARY KEY on a CREATE TABLE command, that would have given you unique keys.

[gregwga50]

Re: Non unique keys

Yes, I did specify primary keys. How can I re-create it with those keys as not unique

[arrow483]

Re: Non unique keys

Create a new table without keys (or non-unique) and copy the data to it. You could use CREATE TABLE or INSERT INTO. Then rename the files as needed back to the original names. Note: You will have to recompile any RPG programs.

[tomliotta]

Re: Non unique keys

A "primary key" will be unique (and non-null, even though it can be null-capable). If you don't want a unique key on a table, don't assign a primary key.

SQL has rules. In a major sense, that's the point of using SQL. A general rule of SQL is that a table should have a 'primary key', but it's not required. In the world of SQL, a non-unique primary key wouldn't make sense.

If you need to create a non-unique key with SQL, use CREATE INDEX.

How are you intending to use the key?

[B.Hauser]

Re: Non unique keys

If you really need a table without an unique key (even though you should at least include an "artificial" unique key).
OK if you really need a table without a unique key for example for transaction data, just create your table without any key constraint.
Create an additional index containing your NON unique keys.
BTW an SQL-index can be used in native I/O like any keyed logical file, but it is not possible to specify an index in an SQL statement.

Birgitta

[danlong005]

Re: Non unique keys

It is possible to create an index in a SQL statement. You probably meant in the same SQL statement that you created the table with. You will need to run the create table SQL and then run the create index SQL afterwards.

CREATE INDEX index_name
ON table_name (column_name)