Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What am I missing here?

I have two tables:

CREATE TABLE source
(
  "sourceid" serial,
  "state" character varying(255) REFERENCES states (state),
  "leadtime" character varying(255),
  CONSTRAINT source_pkey PRIMARY KEY (sourceid)
)
CREATE INDEX 
   ON source (state ASC NULLS LAST);

CREATE TABLE updates
(
  "updateid" serial,
  "sourceid" integer REFERENCES source (sourceid) ON DELETE CASCADE,
  "skip" boolean,
  CONSTRAINT update_pkey PRIMARY KEY (updateid)
)
CREATE INDEX 
   ON updates (sourceid ASC NULLS LAST);

source is the parent table and updates the child. I want any records deleted from source to cascade down and delete all records in updates that have the same sourceid.

I have also tried adding the REFERENCES constraint to the sourceid field of the source table.

Thanks! Brad

share|improve this question
    
The definition of the updates table is invalid (sourceid does not have a type). Furthermore, could you show some example data and the DELETE statement you use? You can use SQLFiddle if you want. –  dezso Oct 10 '13 at 18:13
    
When fixing the many syntax errors, this works for me: sqlfiddle.com/#!12/d9ec6/1 –  a_horse_with_no_name Oct 10 '13 at 18:17
    
Sorry, I accidentally deleted the type when editing the table def to make it more manageable to post here. It does indeed work and I have data in it and everything. –  Brad Mathews Oct 10 '13 at 18:51
    
As to my delete statement - DELETE FROM source WHERE sourceid=2. The related records in the updates table are still there. –  Brad Mathews Oct 10 '13 at 18:53
    
Does the DELETE (of the one row in source) succeed? Or does it fail - for any other reason? You may have other foreign key constraints, referencing the source, that do not allow the DELETE. –  ypercube Oct 10 '13 at 19:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.