Re: Best way to represent a many to many with optionality?

Tech-Archive recommends: Fix windows errors by optimizing your registry

So my question is what is the proper way to set the INSERT/UPDATE and DELETE Rules of the foreign key to enforce the above mentioned "business rules" without getting the "circularity/default" errors? SQL Server lets you specify any of the following rules to apply to either INSERT/UPDATE or DELETE:

One method is to introduce an address type into your schema. You might consider moving the mandatory main address to the Customers table since that would then make it required.

CREATE TABLE dbo.Customers(
CustomerID bigint NOT NULL identity(1,1)
CONSTRAINT PK_CustomerID PRIMARY KEY
)

CREATE TABLE dbo.AddressTypes(
AddressTypeID tinyint NOT NULL
CONSTRAINT PK_AddressTypeID PRIMARY KEY,
Description varchar(20) NOT NULL
)
INSERT INTO dbo.AddressTypes VALUES(1, 'Main')
INSERT INTO dbo.AddressTypes VALUES(2, 'Shipping')
INSERT INTO dbo.AddressTypes VALUES(3, 'Mail')

CREATE TABLE dbo.CustomerAddresses(
CustomerID bigint NOT NULL,
AddressTypeID tinyint NOT NULL,
Address1 nvarchar(75) NOT NULL,
Address2 nvarchar(75) NULL,
CONSTRAINT PK_CustomerAddresses PRIMARY KEY
(CustomerID, AddressTypeID),
CONSTRAINT FK_CustomerAddresses_Customers FOREIGN KEY
(CustomerID)
REFERENCES dbo.Customers(CustomerID)
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT FK_CustomerAddresses_AddressTypes FOREIGN KEY
(AddressTypeID)
REFERENCES dbo.AddressTypes(AddressTypeID)
ON UPDATE CASCADE
ON DELETE CASCADE
)

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"....DotNet4Ever...." <hate.spam@xxxxxxxxxxx> wrote in message news:umjVbkd3JHA.140@xxxxxxxxxxxxxxxxxxxxxxx
I was hoping to have an answer by now but for some reason my post from last week never made it to the server... Here is the situation I want to have but have run aground in trying to implement it properly so suggestions are welcome (and not, it is NOT in SQL Syntax for the syntacticly obsessive!).

Table Customers {
PK CustomerID bigint identity(1,1),
FK AddressID NOT NULL, -- the main (home/business) address of a customer
FK ShippingAddressID NULL, -- customer's shipping address or NULL if same as main address
FK MailingAddressID NULL -- customer's mail address or NULL if same as main address
}

Table Addresses {
PK AddressID bigint identity(0,1),
Address1 nvarchar(75) NOT NULL,
Address2 nvarchar(75) NULL,
PostalCode nvarchar(20) NOT NULL,
.... blah blah blah
}

Foreign Key Relationships:
FK_Customers_Addresses : Customers.AddressID -> Addresses.AddressID
FK_Customers_Addresses_Shipping : Customers.ShippingAddressID -> Addresses.AddressID
FK_Customers_Addresses_Mailing : Customers.MailingAddressID -> Addresses.AddressID

So far so good. I tried to implement the foreign key constraints with DELETE and INSERT/UPDATE Rules but kept on getting all sort of errors about "circular" problems (which I did not see as circular), etc.

Basically the logic I wanted behind this was:

a) The main address is compulsory (Customers.AddressID)
b) The shipping and maling addresses are optional, they are NULL by default unless it is chosen to
specify a different address than the main address. So a single customer may have 1..3 different
addreses on the Addresses table.
c) If the record corresponding to the Shipping OR Mailing address is DELETED the corresponding column in
the Customer's record should automatically be set to NULL (i.e. the FK rules).
d) If the record corresponding to the Shipping OR Mailing adress is UPDATED the change should "cascade"
to the FK column on the Customers table
e) If the record corresponding to the MAIN address is UPDATED the change should "cascade" to the FK
on the FK column Customers.AddressID
f) If an attempt to DELETE record corresponding to the MAIN address is made the action should be prohibited
by SQL (foreign key violation).


So my question is what is the proper way to set the INSERT/UPDATE and DELETE Rules of the foreign key to enforce the above mentioned "business rules" without getting the "circularity/default" errors? SQL Server lets you specify any of the following rules to apply to either INSERT/UPDATE or DELETE:

1. None (???)
2. Set to Default - (tried that but then I was getting errors about not being to do it on Shipping/Mailing if there was
3. Set to NULL
4. Cascade

Suggestions are greatly appreciated as I tried all sort of things I could imagine without being able to enforce all rules above.

Emil
a default constraint or something like that.


.

Relevant Pages

  • Re: Mailing Address vs. Courier Address
    ... Where does the Courier Address fields go--I ... add that number as a foreign key in your other existing tables. ... AddressTypeID - Foreign Key to tblAddressType - Long Integer ... and when you start a mailing, you will be using Mailing or Both as the ...
    (microsoft.public.access.gettingstarted)
  • Re: Cascading Changes in SQL Server
    ... is this article saying that SQL server ... >> relationship had cascading deletes and updates. ... or modify other FOREIGN KEY constraints. ...
    (microsoft.public.sqlserver.server)
  • MS SQL Server - a plethora of limitations...
    ... there are limitations in SQL Server, ... still cannot produce a decent version of MS SQL Server, ... No create or replace for functions / procs. ... > RefID1 int not null foreign key references ForKey ...
    (microsoft.public.sqlserver.programming)
  • Re: Nullable Foreign key constriants
    ... complexity of data operations when using Null values is not a valid argument ... database is better than having null values speaks by itself. ... foreign key will tell him about the nature of this foreign key relationship. ... contrary to the above excerpt from SQL Server 2008 Books Online? ...
    (microsoft.public.sqlserver.programming)
  • Re: Cascading Referential Integrity Constraints
    ... That functionality won't be available until the next release of SQL Server ... I have the foreign key set to ... always delete the detail/many records when the master/one record is deleted. ...
    (microsoft.public.sqlserver.server)