Home » SQL & PL/SQL » SQL & PL/SQL » question about foreign keys
question about foreign keys [message #441822] Wed, 03 February 2010 04:55 Go to next message
bmayr
Messages: 8
Registered: February 2010
Junior Member
Hi!
is the following create statement legal?
imp_id and eng_area_id are referencing foreign keys and are together the primary key in table IUS_EngAreas_Imp_Rel.

CREATE TABLE IUS_EngAreas_Imp_Rel(
	imp_id		INTEGER NOT NULL,
	eng_area_id	INTEGER NOT NULL,
	substatus	VARCHAR2(128) NOT NULL,
	CONSTRAINT pk_engAreasImpl PRIMARY KEY(imp_id, eng_area_id),
	CONSTRAINT fk_engAreaRel_impeti  FOREIGN KEY (imp_id) 	   REFERENCES IUS_Impeti(id),
	CONSTRAINT fk_engAreaRel_engArea FOREIGN KEY (eng_area_id) REFERENCES IUS_EngAreas(id)
);


kind regards!
Re: question about foreign keys [message #441826 is a reply to message #441822] Wed, 03 February 2010 05:02 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks ok, why don't you try it.
Re: question about foreign keys [message #441827 is a reply to message #441822] Wed, 03 February 2010 05:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is legal and it is always the case for a table implementing a N-N relation.

Regards
Michel
Re: question about foreign keys [message #441838 is a reply to message #441822] Wed, 03 February 2010 06:04 Go to previous messageGo to next message
bmayr
Messages: 8
Registered: February 2010
Junior Member
Thanks for your replies!

The CREATE statement works fine, but problems occur when inserting data

ORA-00001: Unique Constraint (DBGEROL.PK_ENGAREASIMPL)


this occurs when I try the following insert statement

INSERT INTO IUS_EngAreas_Imp_Rel(imp_id, eng_area_id, substatus)
VALUES (1,3,'neu');


imp_id = 1 --> id=1 exists in table IUS_Impeti
eng_area_id = 3 --> id=3 exists in table IUS_IUS_EngAreas

...?
Re: question about foreign keys [message #441839 is a reply to message #441838] Wed, 03 February 2010 06:10 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-00001: unique constraint (%s.%s) violated
 *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
         For Trusted Oracle configured in DBMS MAC mode, you may see
         this message if a duplicate entry exists at a different level.
 *Action: Either remove the unique restriction or do not insert the key.

Do not insert twice (1,3).

Regards
Michel

[Updated on: Wed, 03 February 2010 06:11]

Report message to a moderator

Previous Topic: Reference complete new-row in trigger (merged 2)
Next Topic: ORA-01792: maximum number of columns in a table or view is 1000 (merged again by CM)
Goto Forum:
  


Current Time: Tue Sep 27 22:59:59 CDT 2016

Total time taken to generate the page: 0.09390 seconds