No duplicates on multiple colums when creating table
Hello all,
When doing a CREATE TABLE sql statment how do you declare a record
has to be unique? I know how to say a column has to be distinct but
cannot figure how to set a set of columns to be distinct. I can do it
via an index but I'd prefer to do it while creating the table. Here is
the (OpenIngres) SQL statment I'm trying to reproduce:
create table doc_bib(
doc_global_id char(16) not null default ' ',
uis_item_time char(16) not null default ' ',
fam_uis_item_time char(16) not null default ' ',
)
with noduplicates;
That "with no duplicates" is throwing me a curve ball!!!
Richard Krenek
Richard,
I think you would have to use an index (and I suspect OpenIngres probably
did so under the covers). Can you imagine searching a million row table
SEQUENTIALLY every time you wanted to change a row (or insert a new one)?
Doug
"Richard Krenek" <rkrenek@ihs.com> wrote in message
news:39410B9F.707267B9@ihs.com...
> Hello all,
> When doing a CREATE TABLE sql statment how do you declare a record
> has to be unique? I know how to say a column has to be distinct but
> cannot figure how to set a set of columns to be distinct. I can do it
> via an index but I'd prefer to do it while creating the table. Here is
> the (OpenIngres) SQL statment I'm trying to reproduce:
>
> create table doc_bib(
> doc_global_id char(16) not null default ' ',
> uis_item_time char(16) not null default ' ',
> fam_uis_item_time char(16) not null default ' ',
> )
> with noduplicates;>
> That "with no duplicates" is throwing me a curve ball!!!
>
> Richard Krenek
>
The answer to the Ingres creating and index is yes as does Informix (behind
the scenes as an internal index). I know it is possible to do what I want in
the CREATE TABLE statement, it says so in the manual, but it doesn't tell
you how, it assumes I know what I'm doing. It assumed wrong.
Richard Krenek
Doug Agnew wrote:
> Richard,
>
> I think you would have to use an index (and I suspect OpenIngres probably
> did so under the covers). Can you imagine searching a million row table
> SEQUENTIALLY every time you wanted to change a row (or insert a new one)?
>
> Doug
> "Richard Krenek" <rkrenek@ihs.com> wrote in message
> news:39410B9F.707267B9@ihs.com...
> > Hello all,
> > When doing a CREATE TABLE sql statment how do you declare a record
> > has to be unique? I know how to say a column has to be distinct but
> > cannot figure how to set a set of columns to be distinct. I can do it
> > via an index but I'd prefer to do it while creating the table. Here is
> > the (OpenIngres) SQL statment I'm trying to reproduce:
> >
> > create table doc_bib(
> > doc_global_id char(16) not null default ' ',
> > uis_item_time char(16) not null default ' ',
> > fam_uis_item_time char(16) not null default ' ',
> > )
> > with noduplicates;> >
> > That "with no duplicates" is throwing me a curve ball!!!
> >
> > Richard Krenek
> >
Richard Krenek wrote:
>
> Hello all,
> When doing a CREATE TABLE sql statment how do you declare a record
> has to be unique? I know how to say a column has to be distinct but
> cannot figure how to set a set of columns to be distinct. I can do it
> via an index but I'd prefer to do it while creating the table. Here is
> the (OpenIngres) SQL statment I'm trying to reproduce:
>
> create table doc_bib(
> doc_global_id char(16) not null default ' ',
> uis_item_time char(16) not null default ' ',
> fam_uis_item_time char(16) not null default ' ',
> )
> with noduplicates;>
> That "with no duplicates" is throwing me a curve ball!!!
You need a UNIQUE constraint, so make the statement:
create table doc_bib(
doc_global_id char(16) not null default ' ',
uis_item_time char(16) not null default ' ',
fam_uis_item_time char(16) not null default ' ',
unique constraint ( doc_global_id, uis_item_time, fam_uis_item_time )
);
To speed duplicate checking order the columns in the constraint definition
so that the best filter columns (ie the one with the fewest duplicate
values) come first.
Art S. Kagel