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 duplicate keys

Hello,

There is a program which performs some scripted actions via ODBC on tables
in some database on mssql 2000. Sometimes that program tries to insert
record with key that is already present in the database. The error comes up
and the program stops.

Is there any way to globally configure the database or the whole mssql
server to ignore such attempts and let the script continue without any error
when the script tries to insert duplicate-key records?

Thank you for any suggestions.

Pawel BanysOn Thu, 5 Feb 2004 09:13:28 +0100 in comp.databases.ms-sqlserver,
"Pawel Banys" <voland@.dmz.com.pl> wrote:

>Hello,
>There is a program which performs some scripted actions via ODBC on tables
>in some database on mssql 2000. Sometimes that program tries to insert
>record with key that is already present in the database. The error comes up
>and the program stops.
>Is there any way to globally configure the database or the whole mssql
>server to ignore such attempts and let the script continue without any error
>when the script tries to insert duplicate-key records?

Maybe you should change your script to check for the keys first,
instead of blindly whacking stuff into them e.g.

insert into table1 (pk, column)
select pk, column from table2
where table1.pk not in (select pk from table2)

--
A)bort, R)etry, I)nfluence with large hammer.|||"Pawel Banys" <voland@.dmz.com.pl> wrote in message news:<bvsu0g$3fp$1@.atlantis.news.tpi.pl>...
> Hello,
> There is a program which performs some scripted actions via ODBC on tables
> in some database on mssql 2000. Sometimes that program tries to insert
> record with key that is already present in the database. The error comes up
> and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any error
> when the script tries to insert duplicate-key records?
> Thank you for any suggestions.
> Pawel Banys

Are you talking about error 2627 (violation of primary key
constraint)? If so, then the current batch should continue anyway,
unless of course the code has error handling to stop immediately on an
error.

Assuming you have control of the code, you should really avoid the
error instead:

insert into dbo.Destination (col1, col2, ...)
select col1, col2, ...
from dbo.Source s
where not exists (select * from dbo.Destination d
where d.PrimaryKey = s.PrimaryKey)

If you don't control the code, then I don't think there's much you can
do - the error can't be suppressed, and needs to be handled on the
client. Perhaps you can clean up the data somehow before passing it to
the program?

Simon|||Pawel Banys (voland@.dmz.com.pl) writes:
> There is a program which performs some scripted actions via ODBC on
> tables in some database on mssql 2000. Sometimes that program tries to
> insert record with key that is already present in the database. The
> error comes up and the program stops.
> Is there any way to globally configure the database or the whole mssql
> server to ignore such attempts and let the script continue without any
> error when the script tries to insert duplicate-key records?

For a unique index - but not a PK or UNIQUE constraint - you can include
the option IGNORE_DUP_KEY. With this setting, SQL Server does not find
it an error condition if you try to insert a duplicate, but leaves @.@.error
unchanged. Unfortunately, though, the message that is raised, incorrectly
has severity level 16 and not 10, so a client may still belive that
things went wrong.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I too would like to be able to have it ignore errors thrown on an
insert. I'm doing the insert over a WAN, so checking for all records on
the destination server takes forever.

insert into server2.database.dbo.table select * from
server1.database.dbo.table

Adding a where clause would take much MUCH longer to perform vs. a
straight insert.

I've been deleting all records from the destination table, but that's
not a great way of doing it. Any way of having the insert simply ignore
errors and continue?

================
Jordan Bowness
================

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Jordan Bowness (jb_at_bizeworld_dot_ca) writes:
> I too would like to be able to have it ignore errors thrown on an
> insert. I'm doing the insert over a WAN, so checking for all records on
> the destination server takes forever.
> insert into server2.database.dbo.table select * from
> server1.database.dbo.table
> Adding a where clause would take much MUCH longer to perform vs. a
> straight insert.
> I've been deleting all records from the destination table, but that's
> not a great way of doing it. Any way of having the insert simply ignore
> errors and continue?

There is the option that I discussed in the article you replied to. That
is, replace the primary key with a unique index with IGNORE_DUP_KEY.

Another possibility is to insert the data into a staging table on the
target server, and then call a stored procedure on the target server
which performs an INSERT targettbl SELECT * FROM staging WHERE NOT EXISTS.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm having a similar issue here, i'm using a Insert Trigger to add a row to an EXCEPTIONS table when a condition is met during the insert process. The condition could be met multiple times, but I only need 1 record to go into the EXCEPTIONS table per PK combination. I could limit this using a WHERE clause however the EXCEPTIONS table has a composite primary key and I'm not sure how to check for that.

INSERT INTO STAFFING_EXCEPTIONS ...
SELECT ...
FROM INSERTED I

INNER JOIN STAFFING_3W_REQ REQ ON (I.PROJECT_CODE = REQ.PROJECT_CODE AND I.LOB_CODE = REQ.LOB_CODE AND I.ROW_DATETIME = REQ.ROW_DATETIME AND I.FTE_GOAL != REQ.FTE_GOAL)