ON DELETE CASCADE ON UPDATE CASCADE

Hi,

I have got a simple question about below two tables relationship.

Does the two tables ensure that every corresponding Update and delete on Customers table updates and deletes the credit card details as well???

your replies are much appreciated.

Cheers!!!!

**** Credit CardDetails Table ******
USE DWDatabase
GO

IF EXISTS(SELECT name FROM sys.tables WHERE name='CreditCardDetails') DROP TABLE CreditCardDetails
GO

CREATE TABLE CreditCardDetails
(

CreditCardID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
CardNumber NVARCHAR(50) NOT NULL,
CardHoldersName NVARCHAR(50) NOT NULL,
CardStartDate DATETIME NOT NULL,
CardEndDate DATETIME NOT NULL,
Securitycode INT NOT NULL,
CustomerID INT References Customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE,
)



USE DWDatabase
GO

IF EXISTS( SELECT name FROM sys.tables WHERE name='Customer')DROP TABLE Customer
GO

**** Customer Table*******

CREATE TABLE Customer
(

CustomerID int NOT NULL PRIMARY KEY IDENTITY(1,1),
CompanyName nvarchar(50),
ContactFirstName nvarchar(50) NOT NULL,
ContactSecondName nvarchar(50),
BilingAddress nvarchar(50),
City nvarchar(50),
StateOrProvince nvarchar(30),
PostalCode nvarchar(10),
Country nvarchar(50),
Title nvarchar(10),
PhoneNumber nvarchar(50),
FaxNumber nvarchar(50)

)

Yes, Delete Cascade does work that way. Update Cascade means that if you change the CustomerID in the Customer record, then it will automatically make that same CustomerID change in CreditDetails.

Though it makes me wonder why you would be changing an identity key field's value. 

-- RBarryYoung, [phone] blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc. "Performance is our middle name."

Oh thanks, That's true I shall delete the ON Update Cascade.

But i want to work it out this way:

If a customer changes the card details, I would like that to be updated on the carddetails table as well? How could I do that?

And also I have got a Payments table, I want this table to pick up the carddetails automatically for the customer who made that order? Have I designed the tables right way?

I am sorry if this is a basic question, this is my first table designs on my own. your help would be valuable for me.

**Payments Table
CREATE TABLE Payments
(
PaymentID int NOT NULL PRIMARY KEY IDENTITY(1,1),
PaymentAmount smallmoney NOT NULL,
PaymentDate datetime,
OrderID int REFERENCES Orders(OrderID) ON DELETE CASCADE
)

*** OrderDetails Table
CREATE TABLE OrderDetails
(

OrderDetailID int NOT NULL PRIMARY KEY IDENTITY(1,1),
OrderID int References Orders(OrderID) ON DELETE CASCADE ON UPDATE CASCADE,
ProductID int References Product(ProductID) ON DELETE CASCADE ,
PaymentID int References Payments(PaymentId) ON DELETE CASCADE ,
Quantity int,
UnitPrice smallmoney,
Discount smallmoney,

)

**** orders table

CREATE TABLE Orders
(

OrderID int NOT NULL PRIMARY KEY IDENTITY(1,1),
CustomerID int REFERENCES Customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE,
EmployeeID int REFERENCES Employee(EmployeeID) ON DELETE CASCADE,
OrderDate datetime,
PurchaseOrderNumber int,
Shipper nvarchar(50),
ShipAddress nvarchar(50),
Shipcity nvarchar(50),
ShipState nvarchar(30),
ShipPostalCode nvarchar(10),
ShipCountry nvarchar(50),
ShipDate datetime,
)

If a customer changes the card details, I would like that to be updated on the carddetails table as well? How could I do that?

If a customer changes the credit card details, the changes/updates reside in the CreditCardDetails table itself. I didn't get your question. 

And also I have got a Payments table, I want this table to pick up the carddetails automatically for the customer who made that order? Have I designed the tables right way?

What exactly do you mean by Payments table being able to pick up the carddetails automatically? According to your table design, the foreign key enables you to pick the carddetails,payment details for the order using a proper query with proper joins.

You don't have to give ON DELETE CASCADE or ON UPDATE CASCADE to foreign key. Basically, this is given on the primary key to say that if the record (in primary key table) is deleted or updated, reflect the changes in all the tables where the primary key table is referenced via foriegn key.