Dear Jamie,
Thanks a lot for your response.
I've been busy on another project and couldn't check out your solution right
away.
I've automated the executing of the sql statements you sent me. While
testing the first statement created the Trevors table and now I want to
delete it but when doing so i get this error:
DDL cannot be completed on this table because it is referenced by constraint
<name> on table <name>. (Error 3803)
This is an unexpected error. Please contact Microsoft Product Support
Services for more information.
And so I can't delete the table...
What must I do prior to deleting the table ? I've already deleted the
indexes but this doesn't makes a difference...
Thanks.
JG
This is the situation:
I have several objects: Mail, Depot and Action.
Each Mail can have 1 or more Actions, which are specific for the Mail
object
(f.i. create, delete, check).
On the other hand a Depot can have 1 or more Actions, which are specific
for the Depot object BUT NOT the same as the Mail object (f.i. create,
delete,validate, update).
For this I've set up a table Mails (PK MailID), MailActions (PK
MailActionID, FK MailID) and Depots (PK DepotID), DepotActions (PK
DepotActionID, FK DepotID).
I've splitted up the Actions because actions for Mail and Depot are quite
distinct.
So far so good.
Now comes the fuzzy part: I have to create a relationship between Mail and
Depot and the two possibilities are the following.
One situation is that the Mail is the master: so one or many Depots can
point towards one Mail.
Mail1 <- Depot1
<- Depot2
or
Mail2 <- Depot3
Second situation is that the Depot is the master: so one or many Mails can
point towards one Depot
Depot4 <- Mail3
<- Mail4
or
Depot5 <- Mail5
I thought to set up a intermediate table MasterSlaveRelation with a
combined PK: FK MailID, FK Depot ID.
But that doesn't satisfy the need. Because once a Mail or a Depot has been
set as Master it can only occur once !
So for instance: Depot4 <- Mail1 is not valid ! although its occurence
will
be accepted in the table.
A clue ?
Do a google search on subclassing in SQL. Using such an approach, you
would have a superclass that comprises both mail and depot entities; I
have no idea what such an entity could be so I'll use my default name
of 'Trevor' (the following is Access/Jet SQL in ANSI-92 Query Mode):
CREATE TABLE Trevors (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type IN ('Mail', 'Depot')),
UNIQUE (trevor_type, trevor_name)
)
;
CREATE TABLE Depots (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type = 'Depot'),
FOREIGN KEY (trevor_type, trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
;
CREATE TABLE Mail (
trevor_name VARCHAR(15) NOT NULL UNIQUE,
trevor_type VARCHAR(5) NOT NULL,
CHECK (trevor_type = 'Mail'),
FOREIGN KEY (trevor_type, trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION
)
;
CREATE TABLE MasterSlaveTrevors (
master_trevor_name VARCHAR(15) NOT NULL,
master_trevor_type VARCHAR(5) NOT NULL,
FOREIGN KEY (master_trevor_type, master_trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION,
slave_trevor_name VARCHAR(15) NOT NULL UNIQUE,
slave_trevor_type VARCHAR(5) NOT NULL,
FOREIGN KEY (slave_trevor_type, slave_trevor_name)
REFERENCES Trevors (trevor_type, trevor_name)
ON DELETE CASCADE
ON UPDATE NO ACTION,
UNIQUE (master_trevor_type, master_trevor_name, slave_trevor_type,
slave_trevor_name),
CHECK (master_trevor_name <> slave_trevor_name)
)
;
And some test data:
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot1',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot2',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot3',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot4',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Depot5',
'Depot')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail1',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail2',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail3',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail4',
'Mail')
;
INSERT INTO Trevors (trevor_name, trevor_type) VALUES ('Mail5',
'Mail')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot1',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot2',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot3',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot4',
'Depot')
;
INSERT INTO Depots (trevor_name, trevor_type) VALUES ('Depot5',
'Depot')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail1', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail2', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail3', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail4', 'Mail')
;
INSERT INTO Mail (trevor_name, trevor_type) VALUES ('Mail5', 'Mail')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail1', 'Mail', 'Depot1', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail1', 'Mail', 'Depot2', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Mail2', 'Mail', 'Depot3', 'Depot')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot4', 'Depot', 'Mail3', 'Mail')
;
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot4', 'Depot', 'Mail4', 'Mail')
;
So far so good.
Your problem is this:
INSERT INTO MasterSlaveTrevors (master_trevor_name,
master_trevor_type, slave_trevor_name, slave_trevor_type) VALUES
('Depot5', 'Depot', 'Mail1', 'Mail')
;
Mail1 should be prevented from being a slave because it is a master. A
simple fix to such a design is a table-level CHECK constraint:
DELETE FROM MasterSlaveTrevors
WHERE master_trevor_name = 'Depot5'
;
ALTER TABLE MasterSlaveTrevors ADD
CONSTRAINT master_cannot_be_slave
CHECK (NOT EXISTS (
SELECT *
FROM MasterSlaveTrevors AS M1,
MasterSlaveTrevors AS M2
WHERE M1.master_trevor_name = M2.slave_trevor_name))
;
Jamie.
--
