January 12, 2009 at 7:54 pm
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
(
CreditCardIDINTNOT NULLPRIMARY KEY IDENTITY(1,1),
CardNumberNVARCHAR(50) NOT NULL,
CardHoldersNameNVARCHAR(50) NOT NULL,
CardStartDateDATETIME NOT NULL,
CardEndDateDATETIME NOT NULL,
SecuritycodeINTNOT NULL,
CustomerID INT ReferencesCustomer(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
(
CustomerIDintNOT NULL PRIMARY KEY IDENTITY(1,1),
CompanyNamenvarchar(50),
ContactFirstNamenvarchar(50)NOT NULL,
ContactSecondNamenvarchar(50),
BilingAddressnvarchar(50),
City nvarchar(50),
StateOrProvincenvarchar(30),
PostalCodenvarchar(10),
Countrynvarchar(50),
Title nvarchar(10),
PhoneNumbernvarchar(50),
FaxNumbernvarchar(50)
)
January 12, 2009 at 8:47 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 12, 2009 at 9:18 pm
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
(
PaymentIDintNOT NULL PRIMARY KEYIDENTITY(1,1),
PaymentAmountsmallmoneyNOT NULL,
PaymentDatedatetime,
OrderID intREFERENCESOrders(OrderID) ON DELETE CASCADE
)
*** OrderDetails Table
CREATE TABLE OrderDetails
(
OrderDetailIDintNOT NULLPRIMARY KEYIDENTITY(1,1),
OrderIDintReferencesOrders(OrderID)ON DELETE CASCADE ON UPDATE CASCADE,
ProductIDintReferencesProduct(ProductID)ON DELETE CASCADE ,
PaymentIDintReferencesPayments(PaymentId) ON DELETE CASCADE ,
Quantityint,
UnitPricesmallmoney,
Discountsmallmoney,
)
**** orders table
CREATE TABLE Orders
(
OrderIDintNOT NULL PRIMARY KEYIDENTITY(1,1),
CustomerIDintREFERENCES Customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE,
EmployeeIDintREFERENCESEmployee(EmployeeID) ON DELETE CASCADE,
OrderDatedatetime,
PurchaseOrderNumberint,
Shippernvarchar(50),
ShipAddressnvarchar(50),
Shipcitynvarchar(50),
ShipStatenvarchar(30),
ShipPostalCodenvarchar(10),
ShipCountrynvarchar(50),
ShipDatedatetime,
)
January 13, 2009 at 8:37 am
Krishna_DBA (1/12/2009)
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply