problem with foreign keys

Hiya, I'm having a prob I'd really appreciate any help. I get the message 'ORA-02273: this unique/primary key is referenced by some foreign keys' every time i try these foreign keys. I read another post on something similar but still need some help.

For example I have 2 tables:

CREATE TABLE EQUIPMENT
(serialno varchar2(7) not null,
equip varchar2(30),
primary key (serialno) );

CREATE TABLE ROOMBOOKING
(bookingdate date not null,
sess char(1) not null,
roomno number(3) not null,
misc varchar2(30),
primary key (bookingdate, sess, roomno) ); <<SO COMPOSITE KEY HERE

Now I need to make foreign keys in another table to some of these columns but I keep getting there error. For example I've got a table called EQUIPMENTBOOKING that contains
sess, bookingdate, roomno, serialno

but I cant get the keys working.

ALTER TABLE EQUIPMENTBOOKING
add constraint fkey_room
foreign key (bookingdate, roomno, sess) references roombooking (bookingdate, roomno, sess) ;

tried one at a time

ALTER TABLE EQUIPMENTBOOKING
add constraint fkey_room
foreign key (bookingdate) references roombooking (bookingdate) ;

still get the error. And still havent put in the other table one yet, its roombooking thats causing the problem. Can one help?

thanks alottry specifying the FK columns in the same order as the PK columns

in fact you should be able simply to reference the table, and it will figure out the PK

ALTER TABLE EQUIPMENTBOOKING
add constraint fkey_room
foreign key (bookingdate, sess, roomno) references roombooking|||Aaah, thanks a lot. That fixed it up. Working now, thankyou.

Problem with foreign key

Hello,
I have a problem with foreign key. What I want to do is , have one foreign key referencing in two different tables! I have created three tables:

create table University(
UniversityID int not null,
Name nvarchar(50),
....
....
....
primary key(UniversityID )
);

create table Person(
PersonID int not null,
Name nvarchar(50),
....
....
....
primary key(PersonID )
);

create table Borrower(
BorrowerID int not null,
Name nvarchar(50),
MemberName int,
....
....
....
primary key(BorrowerID )
foreign key(MemberName) reference University,
foreign key(MemberName) reference Person,

);

So I want to have one column (in my example MemeberName) which referencing in two different tables. How can I do that??I am not entirely sure what you are trying to accomplish, could you please reword you question a little?|||

Quote:

Originally Posted by Motoma

I am not entirely sure what you are trying to accomplish, could you please reword you question a little?

Ok, Sorry for that. Well, I have created three tables University, Person and Borrower. What I want to do is to have one column in table Borrower (lets say with name MemberId) which will be foreign key referencing together in the other two tables.
For example I gave the below syntax in SQL Server but it does not work:

create table Borrower(
BorrowerID ..........,
................,
..................,
...................,

foreign key (MemberID) references University,
foreign key (MemberID) references Person
);|||So you want the key to exist in either one of the tables? I'm not quite sure of the interaction you are looking for.|||

Quote:

Originally Posted by Motoma

So you want the key to exist in either one of the tables? I'm not quite sure of the interaction you are looking for.

ok I see. I will try to find another way. thanks