Hello,
Can anyone help me for this I am currently working on a DB, where the main table is linked to many other tables, the main table is linked to the other tables in the foreign keys.
The main table has information of a product which is required to be produced for a client, and another table the dispatch has information on the dispatch details of the product when it gets finished.
If in the process i still do not have have any information on the dispatch yet, and i want to know what is not shipped yet, (by a query) i am not being able to filter the non shipped products from the dispatch table, because it still does not have the record in for the produced products in it yet. so i am wondering if possible, to once a new record is created in the main table, that its related record in other corresponding tables be created automatically.
Reema,
If all you want to do is report on the products that do not have a record yet in the dispatch table, you would be better served to use a query, unless you really do want to add every Product as it is created..
The query uses two tables: tableProducts and tableProductDispatch - where ProductID is foreign key field in
tableProductDispatch.
Query to show all Products that do not have a record on tblProductDispatch:
SELECT tblProducts.ProductID
FROM tblProducts LEFT JOIN tblProductDispatch ON tblProducts.ProductID = tblProductDispatch.ProductID
WHERE (((tblProductDispatch.DispatchID) Is Null));
You can use Forign Key in this way:
The foreign key (because it was defined as DEFERRABLE) is not actually checked until you try to COMMIT, at which point you have updated both tables to match so it succeeds.Code:SQL> create table p(pid number primary key); Table created. SQL> create table c(cid number primary key, pid number); Table created. SQL> alter table c add constraint c_p_fk foreign key(pid) references p 2 deferrable initially deferred; Table altered. SQL> insert into p values (1); 1 row created. SQL> insert into c values (10,1); 1 row created. SQL> commit; Commit complete. SQL> update p set pid=2; 1 row updated. SQL> update c set pid=2; 1 row updated. SQL> commit; Commit complete.
ExlGuru
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks