.:: Jasa Membuat Aplikasi Website,Desktop,Android Order Now..!! | | Order Now..!! Jasa Membuat Project Arduino,Robotic,Print 3D ::.

Different Foreign Keys for Different Tables

0 komentar


بِسْــــــــــــــــمِ اﷲِالرَّحْمَنِ اارَّحِيم
bismillaahirrahmaanirrahiim

السَّلاَمُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
Assalamu'alaikum warahmatullahi wabarakatuh

A foreign key can be used to implement table design
patterns that span multiple tables. By choosing how
a foreign key handles a DELETE attempt on the parent
table, you can structure your table designs to
follow two standard patterns.



Welcome to the Database Programmer blog. This series
of essays is for anybody who wants to learn about
databases on their own terms. There is a complete
"http://database-programmer.blogspot.com/2007/12/database-skills-complete-contents.html"
>Table of Contents
, as well as a summary of
"http://database-programmer.blogspot.com/2008/01/table-design-patterns.html"
>Table Design Patterns
. There is a new essay in
this spot each Monday morning.



A Simple Example of Two Foreign Keys



Picture a basic shopping cart, with its two basic tables
of CART and CART_LINES (or ORDERS and ORDER_LINES if you
are more old-fashioned). The table CUSTOMERS is also
in there as a parent to CARTS. Our three tables look
something like this:




CUSTOMERS
|
|
/|\
CART Cart is child of customers
|
|
/|\
CART_LINES Lines is child of Cart


There are two foreign keys here. CART has a foreign key
to CUSTOMERS, and CART_LINES has a foreign key to CART,
but the two foreign keys should behave very differently.



Table Types and Table Design Patterns



In "http://database-programmer.blogspot.com/2008/01/database-skills-sane-approach-to.html"
>A Sane Approach To Choosing Primary Keys
we saw
that table design begins with identifying the basic
kinds of tables: Reference and Small Master Tables,
Large Master Tables, Transactions, and Cross-References.
Just as we picked different kinds of primary keys
for the different tables, so will we pick different
kinds of foreign keys between these tables.



Deleting a Customer



Imagine you have a customer who has made 10 orders
in 2 years. A system administrator, who is allowed
to basically do anything, goes into your admin
screens, looks up the customer, and clicks [DELETE].
What should happen?



The near-universal answer is that the user should
be denied the action. An error should come back that
says "That customer has orders, cannot delete."
We want it this way because we never want to delete
any parent row and "orphan" the child rows.
Database programmers know from long experience that
if you allow the DELETE, your queries will give incorrect
results, or you will work extremely hard with lots
of weird LEFT JOINS and UNIONS trying to
get them to come back correctly.



This is not an issue of "flexibility", where a more
robust system would allow the deletion. This is a
basic question of record-keeping. If the customer has
orders on file then the customer must be kept on file.
Enforcing this rule keeps code clean and simple, and
trying to avoid this rule in the name of "flexibility"
just makes heaps of work for everybody.



Going further, the administrator in question, who
supposedly can do anything, may not violate the rule.
An administrator is simply somebody who can do anything
that would not produce bad data. Administrators
should not be given the ability to violate the basic
structure of the data
, they simply have full
rights to do anything within the structure of the
data
.




The DELETE RESTRICT Foreign Key




The behavior we want here is called DELETE RESTRICT.
On most database servers this is the default
behavior for a foreign key. It means that you cannot
delete a parent table row if there are matching
rows in the child table.



The DELETE RESTRICT pattern is almost universally used
when the child table is a transaction table and the
parent table is a master table or reference table.



The syntax looks something like this:




-- Most database servers implement DELETE RESTRICT
-- by default, so this syntax:
Create table CART (
customer integer REFERENCES customers
,order integer.....
)

-- ...is the same as this explicit syntax:
Create table CART (
customer integer REFERENCES customers
ON DELETE RESTRICT
,order integer.....
)



Deleting An Order and DELETE CASCADE




Now let us say a staff member is on the phone with
a customer, enters an order, enters five lines,
and then the customers says "forget it" and the user
needs to delete the entire order from the CART.



In this case the user wants to go delete the order,
and he expects the computer to also delete the
lines
. This makes perfect sense, why keep the
lines if we don't want the order?



It may seem strange that in the case of deleting
a customer it makes perfect sense to stop the user,
but when deleting an order it makes perfect sense
to delete the lines as well.



The difference is that an entry in the CART table
is a transaction entry. When a user deletes a
transaction they almost always want to automatically
delete all of the relevant rows from all child tables
as well. The two rules basically are:



  • The user cannot delete a master entry that
    has transactions.
  • Deleting a transaction means deleting the
    entire transaction.


NOTE: By "transaction" here I mean financial transaction
or other interaction between master elements. I do not
mean a database transaction.



The syntax for DELETE CASCADE looks something like this:




-- if the user deletes a row from CART,
-- do them the favor of deleting all of the
-- lines as well
Create table CART_LINES (
order integer REFERENCES CART
ON DELETE CASCADE
,order_line integer....
)


Conclusion: Different Tables Types, Different Foreign Key Types



I have said many times in these essays that the foreign key
is the only meaningful way to connect data in different
tables. This week we have seen that the kind of foreign
key you choose depends on what kind of tables you are
connecting together. Children of master tables generally
get DELETE RESTRICT, and children of transaction tables
generally get DELETE CASCADE.



"http://database-programmer.blogspot.com/2008/08/javascript-as-foreign-language.html"
>Next Essay: Javascript as a Foreign Language


Update Contact :
No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email : Fajarudinsidik@gmail.com
NB :: Bila Sobat tertarik Ingin membuat software, membeli software, membeli source code, membeli hardware elektronika untuk kepentingan Perusahaan maupun Tugas Akhir (TA/SKRIPSI), Insyaallah Saya siap membantu, untuk Respon Cepat dapat menghubungi kami, melalui :

No Wa/Telepon (puat) : 085267792168
No Wa/Telepon (fajar) : 085369237896
Email: Fajarudinsidik@gmail.com


atau Kirimkan Private messanger melalui email dengan klik tombol order dibawah ini :

ٱلْحَمْدُ لِلَّهِ رَبِّ ٱلْعَٰلَمِين
Alhamdulilah hirobil alamin

وَ السَّلاَمُ عَلَيْكُمْ وَرَحْمَةُ اللهِ وَبَرَكَاتُهُ
wassalamualaikum warahmatullahi wabarakatuh


Artikel Different Foreign Keys for Different Tables, Diterbitkan oleh scodeaplikasi pada Minggu, 27 Juli 2008. Semoga artikel ini dapat menambah wawasan Anda. Website ini dipost dari beberapa sumber, bisa cek disini sumber, Sobat diperbolehkan mengcopy paste / menyebar luaskan artikel ini, karena segala yang dipost di public adalah milik public. Bila Sobat tertarik Ingin membuat software, membeli software, membeli source code ,Dengan Cara menghubungi saya Ke Email: Fajarudinsidik@gmail.com, atau No Hp/WA : (fajar) : 085369237896, (puat) : 085267792168.

Tawk.to