(quick)constraint syntax question...

Dev Shed Forums Sponsor:

February 14th, 2005, 05:20 PM

mikestan21

Registered User

Join Date: Aug 2003

Posts: 21

Time spent in forums: 1 h 52 m 7 sec
Reputation Power: 0

(quick)constraint syntax question...

hi,
I'm trying to add a constraint for a primary key and a foreign key to the same table, but can't quite figure out the syntax? I can get the PK to work fine by itself, but when I add the FK in like below, it gives me errors?
Any help is appreciated,
-stan

Code:

create table image_number
(
number               int NOT NULL,
image_id                int NOT NULL,
CONSTRAINT be_num_pk PRIMARY KEY (number),
fk_image_id FOREIGN KEY (image_id) REFERENCES image(image_id)
);

February 15th, 2005, 03:30 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Code:

create table image_number
(
number               int NOT NULL,
image_id                int NOT NULL,
CONSTRAINT be_num_pk PRIMARY KEY (number),
CONSTRAINT fk_image_id FOREIGN KEY (image_id) REFERENCES image(image_id)
);

February 15th, 2005, 10:36 AM

mikestan21

Registered User

Join Date: Aug 2003

Posts: 21

Time spent in forums: 1 h 52 m 7 sec
Reputation Power: 0

swamp,
thanks for the help, I had tried that earlier, but it also gave me an error:

Code:

ORA-02449: unique/primary keys in table referenced by foreign keys

ORA-00955: name is already used by an existing object

Is this something that has to do with cascading delete? I have a drop table at the top of my script, is there something I should add to it?

Code:

drop table image;
drop table image_be_number;
drop table image_jpg;
drop table image_request;
drop table user_name;
drop table role;
drop table source;
drop table department;

-stan

Quote:

Originally Posted by swampBoogie

Code:

create table image_number
(
number               int NOT NULL,
image_id                int NOT NULL,
CONSTRAINT be_num_pk PRIMARY KEY (number),
CONSTRAINT fk_image_id FOREIGN KEY (image_id) REFERENCES image(image_id)
);

February 15th, 2005, 10:51 AM

mikestan21

Registered User

Join Date: Aug 2003

Posts: 21

Time spent in forums: 1 h 52 m 7 sec
Reputation Power: 0

swamp,
would this be close?

Code:

CONSTRAINT be_num_pk PRIMARY KEY (be_number),
CONSTRAINT fk_image_id FOREIGN KEY (image_id) REFERENCES image(image_id) ONDELETE CASCADE

it still gives me a 942 and 922 error?

Quote:

Originally Posted by mikestan21

swamp,
thanks for the help, I had tried that earlier, but it also gave me an error:

Code:

ORA-02449: unique/primary keys in table referenced by foreign keys

ORA-00955: name is already used by an existing object

Is this something that has to do with cascading delete? I have a drop table at the top of my script, is there something I should add to it?

Code:

drop table image;
drop table image_be_number;
drop table image_jpg;
drop table image_request;
drop table user_name;
drop table role;
drop table source;
drop table department;

-stan

February 16th, 2005, 03:25 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Constraint names must be unique within a schema. So either be_num_pk or fk_image_id has already been used as a constraint name within the schema.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: