SQL foreign keys

[sn128]

Hi,
am trying to link two tables to one single foreign key iun a further table. I would like Electrical(Part_No ) and Mechnaical (Part_No) to be a single foreign key in the supplies relation, but the last line referencing the two keys brings a "ORA-00907: missing right parentesis" error. If anyone could help please do.

many thanks in advance

Nicholas Skinner



CREATE TABLE Supplier (
Supplier_No NUMBER(3) NOT NULL,
comp_name VARCHAR2(25) NOT NULL,
address VARCHAR2(50) NOT NULL,
town VARCHAR2(30) ,
county VARCHAR2(15) NOT NULL,
postcode VARCHAR2(8) NOT NULL,
telephone NUMBER(14) NOT NULL,
fax NUMBER(14),
payment_terms CHAR(6) CHECK (payment_terms IN ('1-week','2-week','3-week')),
end_date DATE,
CONSTRAINT SUPPLIER_PK PRIMARY KEY (Supplier_No));



CREATE TABLE Electrical (
Part_No NUMBER(4) NOT NULL,
power_rating NUMBER(2) NOT NULL,
voltage NUMBER(2) NOT NULL,
serial_no CHAR(10) UNIQUE NOT NULL,
description CHAR(35) NOT NULL,
quantity_stock NUMBER(4) NOT NULL,
reorder_level NUMBER(4) NOT NULL,
CONSTRAINT ELECTRICAL_PK PRIMARY KEY (Part_No));



CREATE TABLE Mechanical (
Part_No NUMBER(4) NOT NULL,
width NUMBER(3) NOT NULL,
height NUMBER(3) NOT NULL,
length NUMBER(3) NOT NULL,
serial_no CHAR(10) UNIQUE NOT NULL,
description CHAR(35) NOT NULL,
quantity_stock NUMBER(4) NOT NULL,
reorder_level NUMBER(4) NOT NULL,
CONSTRAINT MECHANICAL_PK PRIMARY KEY (Part_No));



CREATE TABLE Supplies(
ID NUMBER(3) NOT NULL,
Supplier_No NUMBER(3) NOT NULL ,
Part_No NUMBER(4) UNIQUE NOT NULL,
end_date DATE,
CONSTRAINT SUPPLIES_PK PRIMARY KEY (ID),
CONSTRAINT SUPPLIES_FK_SUPPLIER FOREIGN KEY (Supplier_No) REFERENCES Supplier (Supplier_No),
FOREIGN KEY (Part_No) REFERENCES Mechanical(Part_No) AND Electrical (Part_No));

 

[sem]

You can not create 1 foreign key to reference 2 different tables. You may create Part_No column and 2 foreign keys, but I suppose this is not the thing you need (I think that the task is to check that Part_No is either Mechanical or Electrical). So, I recommend you to redevelope table structure.
You may join Mechanical and Electrical into 1 common table Parts (in this case you may create views for each specific kind); alternatively you may map superentity Parts to the table containing PK column and ARC (se below)
or
create so-called ARC - 2 mutually exclusive foreign key columns in Supplies: one for Electrical_no and another for Mechanical_no.

Don't be afraid of empty column in join table, the do not use a lot of space (only markers).

Though, if you do not want to change table structure, you'll have to implement referencial integrity by triggers.

 

[sn128]

Hi thanks for the reply
how would you do it using triggers?

 

[sem]

Just create a row-level after insert or update trigger and check in it whether the corresponding record exists in one of the "parent" tables. Regards, Dima

 

[sn128]

Thanks, I am new to oracle SQL please could you help me a bit more on creating a trigger to do this!

Thanks in advance

 

[sem]

Try to start from this article:

http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88856/c18trigs.htm

Regards, Dima

 

[sn128]

So far I have done this

CREATE OR REPLACE
TRIGGER CHECK_SUPPLY_NO
BEFORE INSERT OR UPDATE ON SUPPLIES
FOR EACH ROW
begin
cursor lc_mech_elec (p_no number) is
select *
from mechanical
where part_no = p_no
union
select *
from electrical
where part_no = p_no;
l_dummy pls_integer;
BEGIN
open lc_mech_elecNEW.PART_NO);
fetch lc_mech_elec into l_dummy;
close lc_mech_elec;
IF l_dummy = 0 THEN
raise_application_error(9999, 'Constraint violated');
END IF;
END;


but get this error "Warning: Trigger created with compilation errors"

could anyone help PLEASE

Thanks in advance

 

[sn128]

it was select Part_No not *

 

[sem]

...Or even SELECT 1.
And also you should check lc_mech_elec%found (dont forget to close cursor before raising error) instead of comparing to 0, as YOUR trigger may raise an error for any inserted record. Regards, Dima

 

[sn128]

Many thanks

Please could you help a little bit more. Now I have the following

CREATE OR REPLACE
TRIGGER CHECK_SUPPLY_NO
BEFORE INSERT OR UPDATE ON SUPPLIES
FOR EACH ROW
begin
cursor lc_mech_elec (p_no number) is
select 1
from mechanical
where part_no = p_no
union
select 1
from electrical
where part_no = p_no;
l_dummy pls_integer;
BEGIN
open lc_mech_elecNEW.PART_NO);
fetch lc_mech_elec into l_dummy;
IF l_dummy := lc_mech_elec%found THEN
close lc_mech_elec;
raise_application_error(9999, 'Constraint violated');
END IF;
END;


WITH THESE WARNINGS

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER CHECK_SUPPLY_NO:

LINE/COL ERROR
-------- ---------------------------------------------------------------
2/10 PLS-00103: Encountered the symbol "LC_MECH_ELEC" when expecting
one of the following:
:= . ( @ % ;

12/22 PLS-00049: bad bind variable 'NEW.PART_NO'
14/14 PLS-00103: Encountered the symbol "=" when expecting one of the
following:
. ( * @ % & = - + < / > at in is mod not rem then
<an exponent (**)> <> or != or ~= >= <= <> and or like
between ||
The symbol &quot;. was inserted before &quot;=&quot; to continue.

LINE/COL ERROR
-------- ---------------------------------------------------------------

18/4 PLS-00103: Encountered the symbol &quot;end-of-file&quot; when expecting
one of the following:
end not pragma final instantiable order overriding static
member constructor map

 

[sem]

You have 2 Begin statements and none Declare .
You should also close cursor even on succesfull execution. Regards, Dima

 

[sn128]

Sorry wrong one.
I get SQL this error with the following code.
Many thanks in advance

> sho err
Errors for TRIGGER CHECK_SUPPLY_NO:

LINE/COL ERROR
-------- --------------------------------------------------------------
12/19 PLS-00049: bad bind variable 'NEW.PART_NO'
SQL> INSERT INTO Supplies VALUES (supplies_seq.NEXTVAL, 382,2,NULL);
INSERT INTO Supplies VALUES (supplies_seq.NEXTVAL, 382,2,NULL)
*


CREATE OR REPLACE
TRIGGER CHECK_SUPPLY_NO
BEFORE INSERT OR UPDATE ON SUPPLIES
FOR EACH ROW
DECLARE
cursor lc_mech_elec (p_no number) is
select 1
from mechanical
where part_no = p_no
union
select 1
from electrical
where part_no = p_no;
l_dummy pls_integer;
BEGIN
open lc_mech_elecNEW.PART_NO);
fetch lc_mech_elec into l_dummy;
close lc_mech_elec;
IF l_dummy = 0 THEN
raise_application_error(9999, 'Constraint violated');
END IF;
END;

 

[sem]

I didn't not see your final code, but the previous version contained one more error:

IF l_dummy := lc_mech_elec%found

should be

IF lc_mech_elec%found


And the version in your latest post contains


IF l_dummy = 0 THEN


again. Please be more careful.
Regards, Dima

 

[sn128]

Heres the final code but I am still getting a error

Warning: Trigger created with compilation errors.

SQL> sho err
Errors for TRIGGER CHECK_SUPPLY_NO:

LINE/COL ERROR
-------- -----------------------------------------------------
12/19 PLS-00049: bad bind variable 'NEW.PART_NO'



create or replace trigger CHECK_SUPPLY_NO
BEFORE UPDATE OR INSERT ON SUPPLIES
FOR EACH ROW
DECLARE
cursor lc_mech_elec (p_no number) is
select 1
from mechanical
where part_no = p_no
union
select 1
from electrical
where part_no = p_no;
l_dummy number;
BEGIN
open lc_mech_elecNEW.PART_NO);
fetch lc_mech_elec into l_dummy;
close lc_mech_elec;
IF l_dummy = 0 THEN
raise_application_error(9999, 'Constraint violated');
END IF;
END;
/

 

[sn128]

CREATE OR REPLACE
TRIGGER CHECK_SUPPLY_NO
BEFORE INSERT OR UPDATE ON SUPPLIES
FOR EACH ROW
DECLARE
cursor lc_mech_elec (p_no number) is
select 1
from mechanical
sorry this one.

where part_no = p_no
union
select 1
from electrical
where part_no = p_no;
l_dummy pls_integer;
BEGIN
open lc_mech_elecNEW.PART_NO);
fetch lc_mech_elec into l_dummy;
close lc_mech_elec;
IF lc_mech_elec%found
raise_application_error(9999, 'Constraint violated');
END IF;
END;

 

[sn128]

sorry this one.


CREATE OR REPLACE
TRIGGER CHECK_SUPPLY_NO
BEFORE INSERT OR UPDATE ON SUPPLIES
FOR EACH ROW
DECLARE
cursor lc_mech_elec (p_no number) is
select 1
from mechanical
where part_no = p_no
union
select 1
from electrical
where part_no = p_no;
l_dummy pls_integer;
BEGIN
open lc_mech_elecNEW.PART_NO);
fetch lc_mech_elec into l_dummy;
close lc_mech_elec;
IF lc_mech_elec%found
raise_application_error(9999, 'Constraint violated');
END IF;
END;

 

[sem]

If you add
THEN

after

IF lc_mech_elec%found

everything will be OK.
Regards, Dima

 

[sn128]

Thank you, I can create the Trigger now. Do I need to refer to it when I insert records into the table? At the moment it will allow any part_no in the table even if they do not exist in the mechanical table or electrical

Thanks again

nick

 

[sem]

You should close cursor regardless on whether the parent record was found, but AFTER checking this. Regards, Dima