February 25, 2009 at 7:47 am
Hi.
I think I'm getting a bogus message on the creation of a table RI constraint and need some sage advice about how to work around this.
My DB platform is SQL 2005. My UI is MS Access.
Here is what I'm trying to do;
I have a table called [Company] which lists all of the companies in my business domain. I have a table called [Coal_Mine] which
contains a record for each coal mine in my business domain. Each mine has a relationship to [Company] as the company to contact
for inquires regarding that mine.
I have a third table called [Coal_Mine_Company_Relationship] which defines a set of relationships between
[Coal_Mine] and [Company] - there are several relationship types (Owner, Operator, Lessee) and I have to maintain
the time period of each relationship.
When I create the RI constraint (with cascading updates, which is really the issue - I would really like
to have cascading updates of Company name if it were to change) between;
- [Coal_Mine] and [Company]
- [Coal_Mine] and [Coal_Mine_Company_Relationship]
- [Company] and [Coal_Mine_Company_Relationship]
even though the relationships are discrete - there are no circular references or multiple cascade paths I get the following message.
Msg 1785, Level 16, State 0, Line 7
Introducing FOREIGN KEY constraint 'FK_Coal_Mine_Company_Relationship_to_Company' on table
'Coal_Mine_Company_Relationship' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 7
Could not create constraint. See previous errors.
I think I understand the message but I think that in this case it's wrong since, in this case, there are no
circular references or multiple cascade paths. Can someone help me with this?
The code to create these tables is below if anyone is interested in seeing exactly what I'm trying to do.
Thanks in advance to anyone who can spare some cycles to look at this with me.
-----------------------------------------------------------------------------------------------
-- GET STAFF_ID (User defined function)
-----------------------------------------------------------------------------------------------
--USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Mine',N'U') IS NOT NULL DROP TABLE Coal_Mine
IF OBJECT_ID(N'Mining_Method',N'U') IS NOT NULL DROP TABLE Mining_Method
IF OBJECT_ID(N'Company',N'U') IS NOT NULL DROP TABLE Company
IF OBJECT_ID(N'Get_Staff_Id',N'FN') IS NOT NULL DROP FUNCTION Get_Staff_Id
GO
CREATE FUNCTION Get_Staff_Id() RETURNS INT AS
BEGIN
DECLARE @RETURN INT
SET @RETURN = (SELECT Staff_Id FROM [QryDbCorp\qry1].Corporate_Query.dbo.EUB_Staff WHERE Staff_User_Id = RIGHT(suser_sname(),5))
IF @RETURN IS NULL SET @RETURN = -1 -- To handle the possibility that the [QryDbCorp\qry1].Corporate_Query may not be available
RETURN @RETURN
END
GO
------------------------------------------------------------------------------------------------
-- COMPANY
------------------------------------------------------------------------------------------------
--USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Mine',N'U') IS NOT NULL DROP TABLE Coal_Mine
IF OBJECT_ID(N'Company',N'U') IS NOT NULL DROP TABLE Company
CREATE TABLE dbo.Company(
Company_Name varchar(40) NOT NULL
,Update_Date_Time DATETIME NOT NULL CONSTRAINT DF_Company_Update_Date_Time DEFAULT GETDATE()
,Updated_By_Id INT NOT NULL CONSTRAINT DF_Company_Updated_By_Id DEFAULT dbo.Get_Staff_Id()
,CONSTRAINT PK_COMPANY PRIMARY KEY CLUSTERED (Company_Name))
GO
CREATE TRIGGER Company_Update ON Company FOR UPDATE AS
UPDATEt1 SET
Update_Date_Time = GETDATE()
,Updated_By_Id = dbo.Get_Staff_Id()
FROM Company t1
JOIN inserted i
ON i.Company_Name = t1.Company_Name
GO
------------------------------------------------------------------------------------------------
-- MINING METHOD
------------------------------------------------------------------------------------------------
--USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Mine_Company_Relationship',N'U') IS NOT NULL DROP TABLE Coal_Mine_Company_Relationship
IF OBJECT_ID(N'Coal_Mine',N'U') IS NOT NULL DROP TABLE Coal_Mine
IF OBJECT_ID(N'Mining_Method',N'U') IS NOT NULL DROP TABLE Mining_Method
CREATE TABLE Mining_Method (
Mining_Method VARCHAR(30)
,Update_Date_Time DATETIME NOT NULL CONSTRAINT DF_Mining_Method_Update_Date_Time DEFAULT GETDATE()
,Updated_By_Id INT NOT NULL CONSTRAINT DF_Mining_Method_Updated_By_Id DEFAULT dbo.Get_Staff_Id()
,CONSTRAINT PK_Mining_Method PRIMARY KEY CLUSTERED (Mining_Method))
GO
CREATE TRIGGER Mining_Method_Update ON Mining_Method FOR UPDATE AS
UPDATE t1 SET
Update_Date_Time = GETDATE()
,Updated_By_Id = dbo.Get_Staff_Id()
FROM Mining_Method t1
JOIN inserted i
ON i.Mining_Method = t1.Mining_Method
GO
------------------------------------------------------------------------------------------------
-- COAL MINE
------------------------------------------------------------------------------------------------
--USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Mine_Company_Relationship',N'U') IS NOT NULL DROP TABLE Coal_Mine_Company_Relationship
IF OBJECT_ID(N'Coal_Mine',N'U') IS NOT NULL DROP TABLE Coal_Mine
CREATE TABLE Coal_Mine (
Coal_Mine_Number INT NOT NULL
,Coal_Mine_Number_Modifier VARCHAR(8) NOT NULL CONSTRAINT DF_Coal_Mine_Number_Modifier DEFAULT '~'
,Name VARCHAR(55) NULL
,Coal_Mine_Inquiries_Contact_Company_Name varchar(40) NOT NULL
,Mine_Type VARCHAR(11)
,Mining_Method VARCHAR(30)
,Mine_Status VARCHAR(10)
,Mine_Plan_Status VARCHAR(10)
,Mine_Location_Status VARCHAR(10)
,Uncertain_Information VARCHAR(3)
,Comments VARCHAR(MAX)
,Township INT
,Range INT
,Meridian INT
,Section INT
,West INT
,South INT
,Latitude NUMERIC(8,6)
,Longitude NUMERIC(10,6)
,Mined_Depth NUMERIC(4,1)
,Thickness NUMERIC(3,1)
,Graphic_Type VARCHAR(7)
,Update_Date_Time DATETIME NOT NULL CONSTRAINT DF_Coal_Mine_Update_Date_Time DEFAULT GETDATE()
,Updated_By_Id INT NOT NULL CONSTRAINT DF_Coal_Mine_Updated_By_Id DEFAULT dbo.Get_Staff_Id()
,CONSTRAINT PK_Coal_Mine PRIMARY KEY CLUSTERED (Coal_Mine_Number, Coal_Mine_Number_Modifier)
,CONSTRAINT FK_Coal_Mine_Inquiries_Contact_to_Company FOREIGN KEY (Coal_Mine_Inquiries_Contact_Company_Name) REFERENCES Company (Company_Name) ON UPDATE CASCADE
,CONSTRAINT FK_Coal_Mine_Mining_Method FOREIGN KEY (Mining_Method) REFERENCES Mining_Method (Mining_Method) ON UPDATE CASCADE
,CONSTRAINT CK_Coal_Mine_Number CHECK (Coal_Mine_Number BETWEEN 0 AND 9999)
,CONSTRAINT CK_Mine_Type CHECK (Mine_Type IN ('Surface', 'Underground'))
,CONSTRAINT CK_Mine_Status CHECK (Mine_Status IN ('Abandoned', 'Operating', 'Suspended'))
,CONSTRAINT CK_Mine_Plan_Status CHECK (Mine_Plan_Status IN ('None', 'Complete', 'Incomplete'))
,CONSTRAINT CK_Mine_Location_Status CHECK (Mine_Location_Status IN ('Unknown', 'Known', 'Uncertain'))
,CONSTRAINT CK_Uncertain_Information CHECK (Uncertain_Information IN ('Yes', 'No'))
,CONSTRAINT CK_Township CHECK (TOWNSHIP BETWEEN 1 AND 126)
,CONSTRAINT CK_Range CHECK (RANGE BETWEEN 1 AND 30)
,CONSTRAINT CK_Meridian CHECK (Meridian BETWEEN 4 AND 6)
,CONSTRAINT CK_Section CHECK (Section BETWEEN 1 AND 36)
,CONSTRAINT CK_West CHECK (West BETWEEN 0 AND 1609)
,CONSTRAINT CK_South CHECK (South BETWEEN 0 AND 1609)
,CONSTRAINT CK_Latitude CHECK (Latitude BETWEEN 49 AND 60)
,CONSTRAINT CK_Longitude CHECK (Longitude BETWEEN -120 AND -110)
,CONSTRAINT CK_Mined_Depth CHECK (Mined_Depth BETWEEN 0 AND 600)
,CONSTRAINT CK_Thickness CHECK (Thickness BETWEEN 0 AND 10)
,CONSTRAINT CK_Graphic_Type CHECK (Graphic_Type IN ('Symbol', 'Polygon'))
)
GO
CREATE TRIGGER Coal_Mine_Update ON Coal_Mine FOR UPDATE AS
UPDATE t1 SET
t1.Update_Date_Time = GETDATE()
,t1.Updated_By_Id = dbo.Get_Staff_Id()
FROM COAL_Mine t1
JOIN inserted i
ON i.Coal_Mine_Number = t1.Coal_Mine_Number
AND i.Coal_Mine_Number_Modifier = t1.Coal_Mine_Number_Modifier
GO
-----------------------------------------------------------------------------------------------
-- Check_Coal_Mine_Owner_For_Overlapping_Date_Ranges (User defined function)
-----------------------------------------------------------------------------------------------
--USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Mine_Company_Relationship',N'U') IS NOT NULL DROP TABLE Coal_Mine_Company_Relationship
IF OBJECT_ID(N'Check_For_Overlapping_Date_Ranges',N'FN') IS NOT NULL DROP FUNCTION Check_For_Overlapping_Date_Ranges
GO
CREATE FUNCTION Check_For_Overlapping_Date_Ranges(@RecNo INT, @Mine_Number INT, @Mine_Number_Modifier VARCHAR(8), @Begin_Date DATETIME, @End_Date DATETIME) RETURNS INT AS
BEGIN
DECLARE @Result INT
SET @Result = 0
IF EXISTS (
SELECT
*
FROM Coal_Mine_Company_Relationship
WHERE
Coal_Mine_Number = @Mine_Number
AND Coal_Mine_Number_Modifier = @Mine_Number_Modifier
AND RecNo <> @RecNo
AND ( (@Begin_Date BETWEEN Effective_Date AND Superseded_Date)
OR (@End_Date BETWEEN Effective_Date AND Superseded_Date)
OR (Effective_Date BETWEEN @Begin_Date AND @End_Date)
)
)
SET @Result = 1
ELSE
SET @Result = 0
RETURN @Result
END
GO
-----------------------------------------------------------------------------------------------
-- Coal_Mine_Company_Relationship
-----------------------------------------------------------------------------------------------
--USE Coal_Production_Test
IF OBJECT_ID(N'Coal_Mine_Company_Relationship',N'U') IS NOT NULL DROP TABLE Coal_Mine_Company_Relationship
CREATE TABLE Coal_Mine_Company_Relationship (
RecNo INT IDENTITY
,Coal_Mine_Number INT NOT NULL
,Coal_Mine_Number_Modifier VARCHAR(8)
,Company_Name varchar(40) NOT NULL
,Relationship_Type VARCHAR(10) NOT NULL
,Effective_Date DATETIME NOT NULL
,Superseded_Date DATETIME NULL
,Update_Date_Time DATETIME NOT NULL CONSTRAINT DF_Coal_Mine_Company_Relationship_Update_Date_Time DEFAULT GETDATE()
,Updated_By_Id INT NOT NULL CONSTRAINT DF_Coal_Mine_Company_Relationship_Updated_By_Id DEFAULT dbo.Get_Staff_Id()
,CONSTRAINT PK_Coal_Mine_Company_Relationship PRIMARY KEY CLUSTERED (Coal_Mine_Number, Coal_Mine_Number_Modifier, Relationship_Type, Effective_Date)
,CONSTRAINT FK_Coal_Mine_Company_Relationship_to_Coal_Mine FOREIGN KEY (Coal_Mine_Number, Coal_Mine_Number_Modifier) REFERENCES Coal_Mine (Coal_Mine_Number, Coal_Mine_Number_Modifier) ON UPDATE CASCADE
,CONSTRAINT FK_Coal_Mine_Company_Relationship_to_Company FOREIGN KEY (Company_Name) REFERENCES Company (Company_Name) ON UPDATE CASCADE
,CONSTRAINT UK_Current_Coal_Mine_Company_Relationship UNIQUE (Coal_Mine_Number, Coal_Mine_Number_Modifier, Relationship_Type, Superseded_Date)
,CONSTRAINT CK_Coal_Mine_Company_Relationship_Superseded_Date_ge_Effective_Date CHECK (Superseded_Date >= Effective_Date)
,CONSTRAINT CK_Coal_Mine_Company_Relationship_Relationship_Type CHECK (Relationship_Type IN ('Owner', 'Operator', 'Lessee'))
,CONSTRAINT CK_Coal_Mine_Company_Relationship_Overlapping_Date_Range CHECK (dbo.Check_For_Overlapping_Date_Ranges(RecNo, Coal_Mine_Number, Coal_Mine_Number_Modifier ,Effective_Date, Superseded_Date) = 0))
GO
CREATE TRIGGER Coal_Mine_Owner_UPDATE ON Coal_Mine_Company_Relationship FOR INSERT AS
UPDATE t1 SET
Update_Date_Time = GETDATE()
,Updated_By_Id = dbo.Get_Staff_Id()
FROM Coal_Mine_Owner t1
JOIN inserted i
ON t1.RecNo = i.RecNo
GO
February 25, 2009 at 1:24 pm
I don't know what you are doing but the error is not wrong because SQL Server being RDBMS can only Cascade to one child table because DRI means if A references B B must exist. What you are doing is valid in Access but not SQL Server because Access being end user tool uses a Wizard to create the Trigger that you need. So you need to write a trigger for multiple Cascade.
http://msdn.microsoft.com/en-us/library/ms189799(SQL.90).aspx
Kind regards,
Gift Peddie
February 25, 2009 at 2:30 pm
Thanks for the reply.
I'm not sure what you're saying. Am I reading you to say that cascading can only go to one table? That's not correct otherwise the code below would not work (and it does). The example below shows one "parent" table and three "child" tables with cascading DRI. This works just fine and is effectively what I'm trying to do
CREATE TABLE A (
C1 INT
,R1 INT NOT NULL
,CONSTRAINT PK_A PRIMARY KEY CLUSTERED (C1))
CREATE TABLE B (
C1 INT
,R1 INT NOT NULL
,CONSTRAINT PK_B PRIMARY KEY CLUSTERED (C1)
,CONSTRAINT FK_B_to_A FOREIGN KEY (R1) REFERENCES A(C1) ON UPDATE CASCADE)
CREATE TABLE C (
C1 INT
,R1 INT NOT NULL
,R2 INT NOT NULL
,CONSTRAINT PK_C PRIMARY KEY CLUSTERED (C1)
,CONSTRAINT FK_C_to_A FOREIGN KEY (R1) REFERENCES A(C1) ON UPDATE CASCADE)
Here's the twist I'm adding (in bold).
CREATE TABLE A (
C1 INT
,R1 INT NOT NULL
,CONSTRAINT PK_A PRIMARY KEY CLUSTERED (C1))
CREATE TABLE B (
C1 INT
,R1 INT NOT NULL
,CONSTRAINT PK_B PRIMARY KEY CLUSTERED (C1)
,CONSTRAINT FK_B_to_A FOREIGN KEY (R1) REFERENCES A(C1) ON UPDATE CASCADE)
CREATE TABLE C (
C1 INT
,R1 INT NOT NULL
,R2 INT NOT NULL
,CONSTRAINT PK_C PRIMARY KEY CLUSTERED (C1)
,CONSTRAINT FK_C_to_A FOREIGN KEY (R1) REFERENCES A(C1) ON UPDATE CASCADE
,CONSTRAINT FK_C_to_B FOREIGN KEY (R2) REFERENCES B(C1) ON UPDATE CASCADE)
I still can not see how this would cause "cycles" or "multiple cascade paths".
The FK on table B is the PK from table A. The first FK on table C is also the PK from Table A (This works just fine). The second FK on table C (the one that SQL Server is crying about) is the PK from table B. This should be ok since a change to table A would cascade to table B and to Table C independently. There is no cascade from table B to table C when there is a change to table A which is what I think SQL server is complaining about.
As it turns out, I've resolved the problem by using surogate keys rather than natural keys.
February 25, 2009 at 2:48 pm
This should be ok since a change to table A would cascade to table B and to Table C independently.
That is not OK because only one foreign key qualifies for Cascade in a table, it is working by tricks but you could run into data integrity issues because such operations require a trigger.
Kind regards,
Gift Peddie
February 25, 2009 at 3:07 pm
...only one foreign key qualifies for Cascade in a table...
Please forgive me, I still don't understand what you're saying.
In the example below Table C has two cascading references. SQL Server doesn't complain about this at all. The only difference here is that table D has no reference to table A.
CREATE TABLE A (
C1 INT
,R1 INT NOT NULL
,CONSTRAINT PK_A PRIMARY KEY CLUSTERED (C1))
CREATE TABLE B (
C1 INT
,R1 INT NOT NULL
,CONSTRAINT PK_B PRIMARY KEY CLUSTERED (C1)
,CONSTRAINT FK_B_to_A FOREIGN KEY (R1) REFERENCES A(C1) ON UPDATE CASCADE)
CREATE TABLE D (
C1 INT
,CONSTRAINT PK_D PRIMARY KEY CLUSTERED (C1))
CREATE TABLE C (
C1 INT
,R1 INT NOT NULL
,R2 INT NOT NULL
,CONSTRAINT PK_C PRIMARY KEY CLUSTERED (C1)
,CONSTRAINT FK_C_to_A FOREIGN KEY (R1) REFERENCES A(C1) ON UPDATE CASCADE
,CONSTRAINT FK_C_to_D FOREIGN KEY (R2) REFERENCES D(C1) ON UPDATE CASCADE)
February 25, 2009 at 3:13 pm
What I am telling you is ANSI SQL definition Microsoft and other RDBMS implementation takes versions to be compliant. That is the reason code migration is not simple what works when not fully compliant blows when it is compliant.
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply