Thread: Adding constraint to existing table.

I need some help with the following. I'm following script in 7.1.3 and 7.1.2



create table sales_sales (
              salesid   serial,
              recid   integer,
              rep_id   integer,
              team_id   integer,
              process_date   datetime,
              total_lines   integer,
              total_revenue   float4,
              cancelled   integer,
              disconnected   integer,
              locked   integer,
              last_updated   datetime,
              updated_by   varchar(15),
              contract_date   datetime,
              clines   integer,
              cyear   integer,
              primary key ( salesid )
);

create table sales_sales_detail (
              detid   serial,
              sales_id   integer,
              prod_id   integer,
              qty   integer,
              ext_price   float4,
              primary key ( detid )
);

create index recid29 on sales_sales ( recid );

create index sales_id30 on sales_sales_detail ( sales_id );

alter table sales_sales add constraint sales_sales_con foreign key
(salesid) references sales_sales_detail(sales_id) on delete cascade;

everything goes fine until I attempt to add the constraint. I get an error
that the unique key is not found. I know that a unique key is generated
with the serial definition.

What is wrong with my SQL statement.

Thanks.

How about:

> create index recid29 on sales_sales ( recid );

create unique index recid29 on sales_sales ( recid );

> create index sales_id30 on sales_sales_detail ( sales_id );

create unique index sales_id30 on sales_sales_detail ( sales_id );

?

Patrick