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 DTS

I am using a DTS package to import data from primary server to secondary
server to update the changes occured in few tables and both located in a
different location. The problem is that whenever this DTS fails to execute i
will loose even the existing datas in secondary server since it delete the
existing data before inserting, how to overcome this? OR Is there any better
ways to implement this?
Thanks in advanceHi
Well, I'd transfer the OLD data before deleting into a temporary table and
in case of failure ( in order to not loose the data) nove the data back.
What's error do you get when you run the DTS and it failed?
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
>I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
> i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any
> better
> ways to implement this?
> Thanks in advance
>|||It just showing "Job Failed". It happens whenever if there any problem with
internet or network.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Hi
> Well, I'd transfer the OLD data before deleting into a temporary table
and
> in case of failure ( in order to not loose the data) nove the data back.
> What's error do you get when you run the DTS and it failed?
>
>
> "imtiaz" <Imtiaz@.microsoft.com> wrote in message
> news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
execute
the
>|||Ok, so you can specify an OUTPUT file under Advanced Tab in the Step's
definition.It will give the error desciption
You will have to introduce some logic behind like if the job's step is
failed go to the next step and do soemthing
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:uFH027KfGHA.2456@.TK2MSFTNGP04.phx.gbl...
> It just showing "Job Failed". It happens whenever if there any problem
> with
> internet or network.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> and
> execute
> the
>|||Can you put Delete and Insert into a single transaction? If one step fails,
just roll back the whole transaction.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>|||Try www.sqlscripter.com to transfer your data.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>

Problem With Distinct clause

Hi

I have the following tables:

Suppliers:
SupplierID int not null primary key auto_increment
SubscriptionTypeID int not null
SubscriptionExpDate Date

Products:
ID int not null primary key auto_increment
SupplierID int not null as foreign key
ProductCode varchar(30) not null
productName varchar(255) not null
ImageID int not null as foreign key

Images:
ImageID int not null primary key auto_increment
Image blob not null

Whats is the correct SQL syntax to retrieve the disticnt Image and ProductName where ProductName is LIKE some user defined string and SubscriptionExpDate >= Date() order by SubscriptionTypeID DESC.

The above query will retrieve multiple records for the same Image if there are multiple suppliers for that product. How do I retrieve the distinct Image? The only other way i can think of solving this is to redesign the table and include the productName in the Images table. However all images are unique but product names supplied by the supplier for a particular image/product can be different. hence to increase the posibility of retrieving a match I have included it in the products Table!

Any help will be appreciated.what does "the distinct image" mean?

if a single product is supplied by multiple suppliers, and you have a query where you join product to suppliers, then why aren't you asking about returning "the distinct product" too?

that is where your difficulty lies ;)|||Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name. Hence I want to retrieve distinct name as well. But that is not possible becuase the product name for a given product is different for each supplier yet it is for the same product. i dont think there is any other way round this than the way I metioned. By including product name in the images table aswell!

You may be wondering what is the purpose of such a query. well it is to be implemented on a web page. The above query should display all items which match the search string but onyl display unique images and any one name. At present I get duplicate images and names per row.

If you have any other suggestions I would be glad to hear them.|||here's the important part of what you just said --Basically I want to check if there is a product that matches the user defined search string on product name, for an item supplied by a supplier whos subcription to the database is still valid. If there is I only want to display one image and product name.so my question is, which one? the item with the shortest name? the supplier with the latest registration date?

to pick one from among many, you need a way to say which one

answers that are not allowed include "any one," "the first one," and "you pick one"

:)

problem with delete statement

Hi guys,

i need some help on this issue, i have a table with a nvarchar type column with primary key constraint. I have made a procedure which deletes record from this table using the primary key. My primary key is TxnID, and when i ran the following statement:

DELETE from mytable where TxnID = '119DA-117440520'

it deleted the record with the id '119DA-1174405208'

I dont want this to happen, i only want to delete records with the exact IDs that i provide. Can any one tell me how i should do that?

Thanks in advance.

sorry! i managed to fix it...actually the width was not defined properly which was causing problem

sorry again