primary key - cascade problem - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

primary key - cascade problem

Author

Topic

programer
Posting Yak Master

221 Posts

Posted - 2010-09-17 : 12:30:26

Hi,

I have
table1
table2

Table1
ID primary key
UserId primary key

Table2
Id primary key
PaymentId ... foreign key
UserId ... foreign key

Relationship:
If the link Id (primary key - table1) -> PaymentId (foreign key - table2)
and
UserId (primary key - table1) -> UserId (foreign key - table2)

deletes the information only in Table1.

If you connect only the Id (primary key - table1) -> PaymentId (foreign key - table2) works, but the table must not have two keys.

I want the two keys in Table1 (Id and UserId).

What am I doing wrong?

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 12:34:43

you mean you want delete information from table1 based only relationship of userid?

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

programer
Posting Yak Master

221 Posts

Posted - 2010-09-17 : 12:38:08

quote:
Originally posted by visakh16

you mean you want delete information from table1 based only relationship of userid?

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

Example:
table1:
Id | UserId
1 dasodoa

table2:
Id | UserId
1 dasodoa
1 dasodoa
1
1

If the data in Table1 is deleted, you must delete in Table2.

programer
Posting Yak Master

221 Posts

Posted - 2010-09-17 : 12:40:25

quote:
Originally posted by programer

quote:
Originally posted by visakh16

you mean you want delete information from table1 based only relationship of userid?

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

My carelessness:
Instead of PaymentId, I number 1, written in the ID.

Example:
table1:
Id | UserId
1 dasodoa

table2:
Id | UserId
1 dasodoa
1 dasodoa
1
1

If the data in Table1 is deleted, you must delete in Table2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-17 : 13:09:31

you can handle both of them in single procedure using OUTPUT clause

like


DECLARE @DELETED_ITEMS table
(
UserID varchar(100)
)

DELETE 
FROM table1
OUTPUT DELETED.UserID INTO @DELETED_ITEMS
WHERE UserID=@UserID

DELETE t
FROM table2 t
INNER JOIN @DELETED_ITEMS d
ON d.UserId= t.UserID

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

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-17 : 13:23:40

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

What you posted is useless. The table names are too vague. You do not DDL for tables in your code. You have magical generic id's floating everywhere.

SQL does not use links and connects. We have references which is a different concpet. You are writing assembly language in SQL. Even worse, I think that you might being IDENTITY to build your fake pointer chains.

Did you mean to say:

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
..);

CREATE TABLE Payements
(payment_nbr INTEGER NOT NULL PRIMARY KEY,
payment_amt DECIMAL (12,4) NOT NULL,
..);

Is this the business rule you want to enforce?

CREATE TABLE LedgerPostings
(user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
payment_nbr INTEGER NOT NULL
REFERENCES Payements(payment_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,,
PRIMARY KEY (user_id, payment_nbr),
..);

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources