Referential Integrity issues--use of triggers? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Referential Integrity issues--use of triggers?

Author

Topic

New2SQL44
Starting Member

1 Post

Posted - 2011-11-20 : 21:01:25

I have a created a database with nearly 20 tables in it, so I cannot post all of my statements. I am having an issue though with two tables' referential integrity when it comes to foreign key constraints. I want to constrain them to cascade updates from the primary key tables, but I keep getting the error msg 1785
--Introducing FOREIGN KEY constraint 'FK__PURCHASE___ITEM___64F971E5' on table 'PURCHASE_ORDER_LINE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Here is the table with the primary key:
create table MERCHANDISE (
ITEM_ID varchar (8) NOT NULL PRIMARY KEY,
DESCRIPTION CHAR (60),
STANDARD_PRICE MONEY,
QUANTITY_ON_HAND INT,
REORDER_POINT INT,
VENDOR_ID DECIMAL (2,0) NOT NULL,
FOREIGN KEY(VENDOR_ID)REFERENCES VENDOR(VENDOR_ID)
ON UPDATE CASCADE ON DELETE NO ACTION);

Here is the table with the referential integrity problems:
create table PURCHASE_ORDER_LINE (
ORDER_LINE_NO DECIMAL (3,0) NOT NULL,
ORDER_NO DECIMAL (6,0) NOT NULL,
ITEM_ID VARCHAR (8),
UNIT_COST MONEY,
QUANTITY_ORDERED INT CHECK(QUANTITY_ORDERED>0),
QUANTITY_RECEIVED INT,
DATE_RECEIVED DATETIME DEFAULT GETDATE(),
PRIMARY KEY(ORDER_LINE_NO,ORDER_NO),
FOREIGN KEY(ORDER_NO)REFERENCES PURCHASE_ORDER(ORDER_NO)
ON UPDATE CASCADE ON DELETE NO ACTION,
FOREIGN KEY(ITEM_ID)REFERENCES MERCHANDISE(ITEM_ID)
ON UPDATE CASCADE ON DELETE NO ACTION);
And this SQL is followed by the error msg.

What can I do to work around this? I have tried making an update trigger but it does not work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-20 : 23:43:35

i guess the problem is this line

FOREIGN KEY(ORDER_NO)REFERENCES PURCHASE_ORDER(ORDER_NO)
ON UPDATE CASCADE ON DELETE NO ACTION

do you've a similar fk in PURCHASE_ORDER which points back to PURCHASE_ORDER_LINE ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-23 : 13:58:07

ON UPDATE CASCADE

Try remove the above part and try again as visakh16 mentioned

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-24 : 09:06:07

So PL/SQL of the view is:

SELECT t.*,
t.volume - LAG(volume) OVER (PARTITION BY group_number
ORDER BY timestamp) AS volume_change
FROM volume_table t;

The partition clause splits the data into different buckets, then each bucket is sorted, with all results returned.

Asking on the NZ .Net User Group mailing list I got a pointer to this MS feedback page, but the solution presented there gives me an error “Incorrect syntax near ‘ROWS’.” when I run this query against SQL 2K5

SELECT MIN(volume) OVER(PARTITION BY group_number
ORDER BY timestamp
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) change
FROM volume_table;
GO

Link below:
http://simeonpilgrim.com/blog/2008/08/11/oracles-lag-in-ms-sql-server-2005/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-25 : 08:47:14

quote:
Originally posted by johntech

So PL/SQL of the view is:

SELECT t.*,
t.volume - LAG(volume) OVER (PARTITION BY group_number
ORDER BY timestamp) AS volume_change
FROM volume_table t;

The partition clause splits the data into different buckets, then each bucket is sorted, with all results returned.

Asking on the NZ .Net User Group mailing list I got a pointer to this MS feedback page, but the solution presented there gives me an error “Incorrect syntax near ‘ROWS’.” when I run this query against SQL 2K5

SELECT MIN(volume) OVER(PARTITION BY group_number
ORDER BY timestamp
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) change
FROM volume_table;
GO

Link below:
http://simeonpilgrim.com/blog/2008/08/11/oracles-lag-in-ms-sql-server-2005/

why are you posting PL/SQL queries here. this is a MS SQL Server forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources