SQL2005: Referential integrity twice between two tables?

[maclmu]

I am trying to set up a new database in SQL Server 2005 and i have a problem with the referential integrity.

If i have two tables (e.g.) i need two update two fields automatically:

tbl_address
addressnumber
name
....

tbl_items
itemname
vendor [-> 1. addressnumber]
manufacturer [-> 2. addressnumber]
...

I can connect one of the two fields from tbl_items to tbl_address with referential integrity. But if i try to add the connection for the second field i will get an error message. It states that it would be in conflict due to a new cascade path.

How do i resolve this?

PS: I did this in Access before. It was not a problem there because Access used a secondary "dummy" table.

 

[r937]

please show the actual sql that you are using to try to define the foreign keys (presumably this is what you mean when you say "connect")

also, please show the exact error message

r937.com | rudy.ca

 

[maclmu]

Well, the db is in german, but i will try.

Here is the error message i get in the database diagramme if i try to add the second relation:

tbl_adressen-Tabelle wurde gespeichert
tbl_fahrzeuge-Tabelle
- Beziehung "FK_tbl_fahrzeuge_tbl_adressen1" kann nicht erstellt werden.
Das Einführen der FOREIGN KEY-Einschränkung 'FK_tbl_fahrzeuge_tbl_adressen1' für die 'tbl_fahrzeuge'-Tabelle kann Schleifen oder mehrere Kaskadepfade verursachen. Geben Sie ON DELETE NO ACTION oder ON UPDATE NO ACTION an, oder ändern Sie andere FOREIGN KEY-Einschränkungen.
Die Einschränkung konnte nicht erstellt werden. Siehe vorherige Fehler.

I will try to translate this:

tbl_adressen-table has been saved
tbl_fahrzeuge-Tabelle
- relation "FK_tbl_fahrzeuge_tbl_adressen1" cannot be created.
The introduction of the FOREIGN KEY-restriction 'FK_tbl_fahrzeuge_tbl_adressen1' for the 'tbl_fahrzeuge'-table can cause loop or several cascade paths. Enter ON DELETE NO ACTION or ON UPDATE NO ACTION, or change other FOREIGN KEY-restrictions.
The restriction could not be created. See preceding error messages.


Next, the sql for the two tables:

USE [SpedCalc]
GO
/****** Objekt: Table [dbo].[tbl_adressen] Skriptdatum: 05/27/2008 08:16:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_adressen](
[adr_adressnr] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
[adr_name_1] [char](50) COLLATE Latin1_General_CI_AS NOT NULL,
[adr_name_2] [char](50) COLLATE Latin1_General_CI_AS NULL,
[adr_strasse] [char](50) COLLATE Latin1_General_CI_AS NOT NULL,
[adr_land] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[adr_plz] [char](7) COLLATE Latin1_General_CI_AS NOT NULL,
[adr_ort] [char](40) COLLATE Latin1_General_CI_AS NOT NULL,
[adr_debitorenkonto] [int] NULL,
[adr_kreditorenkonto] [int] NULL,
[adr_website] [varchar](254) COLLATE Latin1_General_CI_AS NULL,
[adr_bemerkung] [varchar](254) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_tbl_adressen] PRIMARY KEY CLUSTERED
(
[adr_adressnr] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Adressen' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tbl_adressen'



USE [SpedCalc]
GO
/****** Objekt: Table [dbo].[tbl_fahrzeuge] Skriptdatum: 05/27/2008 08:18:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_fahrzeuge](
[fzg_fahrgestellnr] [char](30) COLLATE Latin1_General_CI_AS NOT NULL,
[fzg_eigner] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
[fzg_abteilung] [char](10) COLLATE Latin1_General_CI_AS NULL,
[fzg_sachkonto] [int] NULL,
[fzg_kostenstelle] [int] NULL,
[fzg_inventarnr] [int] NULL,
[fzg_hersteller] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
[fzg_typ] [varchar](254) COLLATE Latin1_General_CI_AS NULL,
[fzg_fahrzeuggruppe] [char](10) COLLATE Latin1_General_CI_AS NOT NULL,
[fzg_fahrzeugart] [char](20) COLLATE Latin1_General_CI_AS NOT NULL,
[fzg_kmstand] [int] NULL,
[fzg_kmdatum] [datetime] NULL,
[fzg_bemerkung] [varchar](254) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_tbl_fahrzeuge] PRIMARY KEY CLUSTERED
(
[fzg_fahrgestellnr] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Letzter bekannter KM-Stand' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tbl_fahrzeuge', @level2type=N'COLUMN', @level2name=N'fzg_kmstand'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datum des letzten bekannten KM-Standes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tbl_fahrzeuge', @level2type=N'COLUMN', @level2name=N'fzg_kmdatum'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Fahrzeuge' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'tbl_fahrzeuge'

GO
USE [SpedCalc]
GO
ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_abteilungen] FOREIGN KEY([fzg_abteilung])
REFERENCES [dbo].[tbl_abteilungen] ([abt_abteilung])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_adressen] FOREIGN KEY([fzg_hersteller])
REFERENCES [dbo].[tbl_adressen] ([adr_adressnr])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_adressen1] FOREIGN KEY([fzg_eigner])
REFERENCES [dbo].[tbl_adressen] ([adi_adressnr])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_fahrzeugarten] FOREIGN KEY([fzg_fahrzeugart])
REFERENCES [dbo].[tbl_fahrzeugarten] ([far_fahrzeugart])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_fahrzeuggruppen] FOREIGN KEY([fzg_fahrzeuggruppe])
REFERENCES [dbo].[tbl_fahrzeuggruppen] ([fgr_fahrzeuggruppe])
ON UPDATE CASCADE


This is the relation part which will cause the problem:

ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_adressen] FOREIGN KEY([fzg_eigner])
REFERENCES [dbo].[tbl_adressen] ([adi_adressnr])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [FK_tbl_fahrzeuge_tbl_fahrzeugarten1] FOREIGN KEY([fzg_fahrzeugart])
REFERENCES [dbo].[tbl_fahrzeugarten] ([far_fahrzeugart])
ON UPDATE CASCADE
GO

I hope, this will help.

 

[ESquared]

FK_tbl_fahrzeuge_tbl_adressen
<sad face>

First, if you have the room to rename these tables, I recommend you remove the "tbl_" part. Training wheels get will eventually get in your way. Can you imagine the Tour de France with all the bicycles having training wheels on? They could still do it but their top performance would be hindered. Also, remove the table prefix from the columns, unless the remaining name makes no sense without it. (For example, column InvoiceDate in table Invoice makes sense, but that's because it truly qualifies the date with some kind of meaning rather than just telling everyone what table it's in, which can always be known in any query by the best practice use of aliases.)

Second, why are you updating primary keys? That shouldn't be necessary. If your primary keys MUST change then use surrogate keys (artifical, meaningless, system-generated numbers such as identity columns).

Last, I'm not sure if we have enough information. We can't see the potential multiple update path. Usually this involves some kind of circular reference where the system can't guarantee that if you update the key in one table it won't circle around and update it again (which would kick off another cycle ad infinitum, which is bad). Honestly, it's very difficult for me to scan your code with all the extra tbl_ and fzg_ and adr_ prefixes. Though some of it might be the German, too.

Can you provide simplified descriptions of just the primary and foreign key columns of the involved tables?

 

[maclmu]

The "tbl_" training wheels might come of. It is just an additional information. I am not sure yet if it could be of any use to distinguish views and tables.

But i will keep the table prefix within the tables. Since i could run into trouble having a "remark" column in different tables. I would have to differentiate these anyway.

Some background on this.

There one table "tbl_fahrzeuge". These are basically trucks. Each truck has a manufacturer (hersteller) and an owner (eigner). There is an address for both. The primary key is a matchcode (eg. DC for Daimler-Chrysler). Since companies tend to rename these days, i want to make sure, that changes are forwarded within the database.

Since the database is in development i just removed all foreign key relations from the database. Except those two between the trucks and the addresses. Still the SQL server wont allow me to add the update relation.

 

[r937]

here is one foreign key --

ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [red][FK_tbl_fahrzeuge_tbl_adressen1][/red] FOREIGN KEY([[red]fzg_eigner][/red])
REFERENCES [dbo].[tbl_adressen] ([adi_adressnr])
ON UPDATE CASCADE

here is the one that's causing the problem --

ALTER TABLE [dbo].[tbl_fahrzeuge] WITH CHECK ADD CONSTRAINT [red][FK_tbl_fahrzeuge_tbl_adressen][/red] FOREIGN KEY([[red]fzg_eigner][/red])
REFERENCES [dbo].[tbl_adressen] ([adi_adressnr])
ON UPDATE CASCADE

my conclusion is that one of these should be for [red]hersteller[/red]





r937.com | rudy.ca

 

[maclmu]

Yes, you are right.

It is a fault in the text i posted

One is for eigner and the other one is for hersteller.

I tried to remove and reinstall these foreign keys. But that did not help.

Does the SQL Server have a problem with this kind of multiple references on the same tables?

Could i ensure the integrity somehow else?

 

[r937]

Does the SQL Server have a problem with this kind of multiple references on the same tables?

apparently only if CASCADE is specified


without the CASCADEs, it will accept two FKs in the same table referencing the same other table

r937.com | rudy.ca

 

[ESquared]

But i will keep the table prefix within the tables. Since i could run into trouble having a "remark" column in different tables. I would have to differentiate these anyway.

This is very common in databases to have columns in different tables named the same. Any conflict is solved through the use of proper prefixes.

SELECT
   B.FullName As BusinessName,
   O.FullName As OwnerName
FROM
   Business B
   INNER JOIN Owner O ON B.OwnerID = O.OwnerID

or better:

SELECT
   B.FullName As BusinessName,
   O.FullName As OwnerName
FROM
   Party B
   INNER JOIN Party O ON B.ParentID = O.PartyID
WHERE
   B.Type = 'Business'
   AND O.Type = 'Person'

You can of course take or leave my advice, but I very strongly recommend that you don't include the table name your columns.

 

[SQLSister]

Suggest you use a trigger to keep the second relationship in synch. Just make sure that you create the trigger to handle multiple record updates, inserts or deletes.

"NOTHING is more important in a database than integrity." ESquared

 

[maclmu]

Would it be a wise idea to use triggers instead of cascade for all relationships. This would lead to only one technique being used.

PS: I haven't done that yet but i will try how it works

 

[ESquared]

Please don't use triggers when a built-in database constraint is possible!

Also, do you have a response about why you are updating primary keys?

 

[maclmu]

Ok, then i will use triggers only for those cases where several cascades collide.

Here is an example why i have to update primary keys:

The primary key for a truck is its chassis number. When a new truck is brought that number is still unknown. But it must be stored in the database to track its order. Thus it will get a temporary chassis number (=primary key). As soon as the real number is known it will be adjusted in the database and all related tables have to be updated.

 

[SQLSister]

It would be better to have an identity key be your prinmary key and store the chassis number in only the main table. Then you can simply update it in one place. No need to update other tables becasue they are actaully linked using the identity key field not the chassis number.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

You've hit on the main reason why not to use "natural keys" in databases (I put that in quotes because I think they are unnatural!). Rework your data to use surrogate keys (identity columns work great!) and make the chassis number a non-primary key. It can still function as a business key.

This gets rid of the entire cascade problem. Also, there is a real performance benefit in having primary keys that are int (4 bytes) instead of 30 character bytes. Making the data type of your keys as short as possible is best practice.

Another thing I notice is that you have a lot of char columns. This is usually not necessary. Can you make them varchar?

The address table you showed above has an address number. Can't it be an integer? Why does it have to be char(10)?