Re: how to know this row has been referenced or not????
От | |
---|---|
Тема | Re: how to know this row has been referenced or not???? |
Дата | |
Msg-id | 20050211190752.51255.qmail@web52403.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: how to know this row has been referenced or not???? (Akbar <tuxer@myrealbox.com>) |
Список | pgsql-novice |
--- Akbar <tuxer@myrealbox.com> wrote: > On Wed, 2005-02-09 at 22:22 -0700, Michael Fuhr > wrote: > > On Thu, Feb 10, 2005 at 07:00:14PM +0700, Akbar > wrote: > > > > > How do I know for sure that "this row" or "that > row" has or has not > > > been referenced by other table???? > > > > Why do you need to know? What are you trying to > do? > > Well, I made application using postgresql > database.... there is a list > of employee.... if user want to delete the employee > data, I have to > check whether this employee data has been referenced > or not.... if no > body need this employee data ( has not been > referenced ), my application > will delete it... but if somebody need this employee > data ( has been > referenced / maybe transaction table need it ), I > just move the employee > data to not-active list of employee..... > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > akbar, get pgadmin3. in the help section you will find the following which i believe will answer your question... 5.4. Constraints ... We know that the foreign keys disallow creation of orders that do not relate to any products. But what if a product is removed after an order is created that references it? SQL allows you to specify that as well. Intuitively, we have a few options: Disallow deleting a referenced product Delete the orders as well Something else? To illustrate this, let's implement the following policy on the many-to-many relationship example above: when someone wants to remove a product that is still referenced by an order (via order_items), we disallow it. If someone removes an order, the order items are removed as well. CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, ... ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) ); Restricting and cascading deletes are the two most common options. RESTRICT prevents a statement from deleting a referenced row. NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default if you do not specify anything. (The essential difference between these choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.) There are two other options: SET NULL and SET DEFAULT. These cause the referencing columns to be set to nulls or default values, respectively, when the referenced row is deleted. Note that these do not excuse you from observing any constraints. For example, if an action specifies SET DEFAULT but the default value would not satisfy the foreign key, the deletion of the primary key will fail. Analogous to ON DELETE there is also ON UPDATE which is invoked when a primary key is changed (updated). The possible actions are the same. More information about updating and deleting data is in Chapter 6, Data Manipulation. Finally, we should mention that a foreign key must reference columns that either are a primary key or form a unique constraint. If the foreign key references a unique constraint, there are some additional possibilities regarding how null values are matched. These are explained in the reference documentation for CREATE TABLE. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
В списке pgsql-novice по дате отправления: