Thread: how to know this row has been referenced or not????
Assume I have one table: id name comment 1 stupid blabla 2 idiot blabla These row has been referenced by other table..... Then I add one row to that table, so the table become: id name comment 1 stupid blabla 2 idiot blabla 3 dumb bleble The row with id "3" has not been referenced by other table because I have just insert it ( it is still fresh ).... How do I know for sure that "this row" or "that row" has or has not been referenced by other table???? Of cource I can test it by trying to delete it.... if it has been referenced, the delete command will fail, and if it has not been referenced the delete command will success..... Thank you.
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? > Of cource I can test it by trying to delete it.... if it has > been referenced, the delete command will fail, and if it has not been > referenced the delete command will success..... If the referencing table uses ON DELETE CASCADE then the delete will succeed and it'll also delete the rows in the other table. Even if you do find out that nobody's referencing the row, the situation could change immediately after the check unless you take steps to prevent that from happening (e.g., through locking). -- Michael Fuhr http://www.fuhr.org/~mfuhr/
--- Akbar <tuxer@myrealbox.com> wrote: > Assume I have one table: > id name comment > 1 stupid blabla > 2 idiot blabla > > These row has been referenced by other table..... > Then I add one row to > that table, so the table become: > id name comment > 1 stupid blabla > 2 idiot blabla > 3 dumb bleble > > The row with id "3" has not been referenced by other > table because I > have just insert it ( it is still fresh ).... How do > I know for sure > that "this row" or "that row" has or has not been > referenced by other > table???? Of cource I can test it by trying to > delete it.... if it has > been referenced, the delete command will fail, and > if it has not been > referenced the delete command will success..... > > Thank you. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > akbar, run a query that pulls both the data you listed and the referenced data. if it shows up, it is referenced, if it does not, it isn't referenced. you could set up a query to only display the information in the table you listed where there is no referenced data. i'm just getting into the sql part of this business, so the experts would have to verify this, but i think you you could query your listed table and list those listed rows where the referenced data is null. you would then get a list of all your rows where the referenced data doesn't exist, which is what you want, right? you could do this in your application or you could use pgadmin3, depending on your purpose for gathering this information. __________________________________ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250
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.....
--- 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