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.
David,
I think your trying to apply the Foreign key to the wrong table try either:
CREATE TABLE "sales_sales_detail" (
"detid" int4 DEFAULT nextval('"sales_sales_detail_detid_seq"'::text) NOT
NULL,
"sales_id" int4,
"prod_id" int4,
"qty" int4,
"ext_price" float4,
CONSTRAINT "sales_sales_detail_pkey" PRIMARY KEY ("detid"),
CONSTRAINT "sales_sales_con" FOREIGN KEY (sales_id) REFERENCES "sales_sales"
(salesid)
);
(The above was generated by pgAdmin II)
OR
alter table sales_sales_detail add constraint sales_sales_con foreign key
(sales_id) references sales_sales (salesid) on delete cascade;
hih
steve boyle
"David Bryan" <d_bryan_remove@onebox.com> wrote in message
news:xpu_7.5576$vA2.1800243218@newssvr30.news.prodigy.com...
I need some help with the following. I'm following script in 7.1.3 and
7.1.2
Show quoted text
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.
