Compiled Messages: ------------------------------------------------------------ Message no. 6954 Posted by Rhonda Nichols (renichols2) on Monday, March 20, 2006 12:59pm Subject: Project 7 Does anyone know what tablespace we are suppose to put the tables we create in project 7? -Rhonda ------------------------------------------------------------ Message no. 6955[Branch from no. 6954] Posted by Krishnamurth Ashwini (kashwini) on Monday, March 20, 2006 1:07pm Subject: Re: Project 7 Sorry Rhonda, haven't started with the project-7 yet. Ash ------------------------------------------------------------ Message no. 6962[Branch from no. 6954] Posted by Krupa Kandavalli (kkandavalli) on Monday, March 20, 2006 10:31pm Subject: Re: Project 7 Same here Rhonda...I dint start the project yet. Praneeth ------------------------------------------------------------ Message no. 6964[Branch from no. 6954] Posted by Harold Harris (hjharris2) on Tuesday, March 21, 2006 12:48am Subject: Re: Project 7 I believe that by default Oracle will store all user data in the USERS tablespace. HH ------------------------------------------------------------ Message no. 6968[Branch from no. 6962] Posted by Bikash Adhikari (badhikari) on Tuesday, March 21, 2006 2:12am Subject: Re: Project 7 I havent either ...sorry Rhonda ------------------------------------------------------------ Message no. 6970[Branch from no. 6964] Posted by Rhonda Nichols (renichols2) on Tuesday, March 21, 2006 11:08am Subject: Re: Project 7 Actually the default on mine came up to SYSTEM tablespace but didn't really want to put it there. I think I will put it in the USERS as HH suggested unless anyone finds out otherwise. Thanks! ------------------------------------------------------------ Message no. 6973 Posted by Muhammad Latif (mlatif) on Tuesday, March 21, 2006 12:22pm Subject: Constraints Hey everyone: If we drop a table containing link to a foreign key, are all the constraints dropped automatically? Arsal ------------------------------------------------------------ Message no. 6974[Branch from no. 6973] Posted by Krishnamurth Ashwini (kashwini) on Tuesday, March 21, 2006 12:30pm Subject: Re: Constraints According to what I understood from your question..here is my answer.. Consider this example: Order table has field ID which is a primary key. Items table has a field o_id, which is a foreign key(linking orders table). One major rule to be followed with constraints is: You can never drop orders table that has a field(ID) which is referenced as a foreign key in items table. On the other hand, You can first drop the items table and then, if you are sure that no other table has any fields referencing orders table, you can drop Orders table without any constraint violation. Ash ------------------------------------------------------------ Message no. 6975[Branch from no. 6973] Posted by Waseem Manzoor (wmanzoor) on Tuesday, March 21, 2006 12:41pm Subject: Re: Constraints Hi Arsal I think first we have to drop the child table where foreign key exist and after dropping that table then we can drop the parent table. Waseem Manzoor ------------------------------------------------------------ Message no. 6976[Branch from no. 6973] Posted by Kelsey Pooley (kjpooley) on Tuesday, March 21, 2006 12:44pm Subject: Re: Constraints I believe you have to disable the constraints before you can drop the table. According to the text, when you truncate the table (remove all records from it) you have to disable the constraints first. I think it is the same for dropping the table. You cannot drop a table if it is referenced by another table. ------------------------------------------------------------ Message no. 6977[Branch from no. 6964] Posted by Waseem Manzoor (wmanzoor) on Tuesday, March 21, 2006 2:49pm Subject: Re: Project 7 I Think Dr. Liu mentioned it we will connect it as scott/tiger and it will automatically store it in scott table space user. Waseem manzoor ------------------------------------------------------------ Message no. 6978[Branch from no. 6976] Posted by Harold Harris (hjharris2) on Tuesday, March 21, 2006 2:54pm Subject: Re: Constraints I would also use the CASCADE option to ensure that constraints refering to the dropped table within other tables (foreign keys) are also removed by the DROP. HH ------------------------------------------------------------ Message no. 6979[Branch from no. 6977] Posted by Rhonda Nichols (renichols2) on Tuesday, March 21, 2006 3:05pm Subject: Re: Project 7 Thanks Waseem! ------------------------------------------------------------ Message no. 6980[Branch from no. 6978] Posted by Rhonda Nichols (renichols2) on Tuesday, March 21, 2006 3:07pm Subject: Re: Constraints I'm with Harold on this one. If you drop a table and use the CASCADE option it should delete all constraints and keys associated with it. _Rhonda ------------------------------------------------------------ Message no. 6983[Branch from no. 6978] Posted by Rohini Shrestha (rshrestha) on Tuesday, March 21, 2006 7:19pm Subject: Re: Constraints I agree with you, Harold. With a help of following code, we can drop primary key with a foreign key dependency ALTER TABLE DROP CONSTRAINT CASCADE CONSTRAINTS; Visit this website for an example: http://www.psoug.org/reference/constraints.html Hope it helps :-) Rohini ------------------------------------------------------------ Message no. 6984[Branch from no. 6979] Posted by Waseem Manzoor (wmanzoor) on Wednesday, March 22, 2006 8:28am Subject: Re: Project 7 You welcome Rhonda ------------------------------------------------------------ Message no. 6991[Branch from no. 6973] Posted by Sagun Piya (srpiya2) on Wednesday, March 22, 2006 10:18pm Subject: Re: Constraints Hey Arsal, First we have to drop the child table, only then we can drop the parent table. Or we can use ON DELETE CASCADE. ON DELETE clause specifies the action to be taken when a row in the parent table is deleted. Sagun ------------------------------------------------------------ Message no. 6995 Posted by Antoinette Lockett (arlockett) on Wednesday, March 22, 2006 11:24pm Subject: Foreign Key What is the signficant of a foreign key? ------------------------------------------------------------ Message no. 6996[Branch from no. 6995] Posted by Krishnamurth Ashwini (kashwini) on Wednesday, March 22, 2006 11:44pm Subject: Re: Foreign Key Hi Antoinette, Without a foreign key, there is no link/relation between two tables at all. It is practically not feasible to put all the required data in one single table. You need to maintain different tables for different requirements and in order to make use of relevant data present in different tables. For ex: Linking, "customer name" from customer table and the "item" that he/she purchased from the items table. According to me, there is no sense in maintaining a database if you are not able to relate/link to other tables to retrieve required data. Asht ------------------------------------------------------------ Message no. 6997[Branch from no. 6996] Posted by Krishnamurth Ashwini (kashwini) on Wednesday, March 22, 2006 11:45pm Subject: Re: Foreign Key-sorry for the typos Hi Antoinette, Without a foreign key, there is no link/relation between two tables at all. It is practically not feasible to put all the required data in one single table. You need to maintain different tables for different requirements and in order to make use of relevant data present in different tables you need to have a foreign key. For ex: Linking, "customer name" from customer table and the "item" that he/she purchased from the items table. According to me, there is no sense in maintaining a database if you are not able to relate/link to other tables to retrieve required data. Ash ------------------------------------------------------------ Message no. 6998[Branch from no. 6995] Posted by Rohini Shrestha (rshrestha) on Thursday, March 23, 2006 2:08am Subject: Re: Foreign Key Hi Ann, Any table in the database that has a primary key or unique key can be referenced by another table by setting up a rule that relates those tables and governs the relationship. In this relationship, there is what is known as a parent table and a child table. The child table uses a foreign key to reference the parent table's primary key or unique key. Through the use of the relationship both parent tables and child tables can effect each other is positive and negative ways. Hope it helps. Rohini ------------------------------------------------------------ Message no. 7000[Branch from no. 6995] Posted by Gnaneshwar Bukka (gbukka) on Thursday, March 23, 2006 12:37pm Subject: Re: Foreign Key Hey Antoinette, The main reason for using foriegn key is to relate tables, oracle is know for its relational database, so to keep this going we need foreign key to link the tables. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 7001[Branch from no. 6995] Posted by Venkat Munagala (vrmunagala) on Thursday, March 23, 2006 2:42pm Subject: Re: Foreign Key A foreign key in one table refers to the primary key of another table. This way references can be made to link information together and it is an essential part of database normalization. Rohit ------------------------------------------------------------ Message no. 7003[Branch from no. 6995] Posted by Kelsey Pooley (kjpooley) on Thursday, March 23, 2006 3:20pm Subject: Re: Foreign Key Without the foreign key we would have to go into each table individually and make the update. A foreign key link allows us to make the update only once. It is kind of like networking the tables together so that they communicate with one another. ------------------------------------------------------------ Message no. 7006[Branch from no. 6995] Posted by Waseem Manzoor (wmanzoor) on Thursday, March 23, 2006 3:44pm Subject: Re: Foreign Key Another benefit of foreign key is consistency of data. Waseem Manzoor ------------------------------------------------------------ Message no. 7007[Branch from no. 6995] Posted by Imran Pathan (ipathan) on Thursday, March 23, 2006 3:47pm Subject: Re: Foreign Key It is not possible to store all the data in a single table. Hence we need to have a link or relation between two tables so that we can have the data stored in different tables.The foreign key is the one which provides the link to the tables.Thus, a foreign key is significant. Imran ------------------------------------------------------------ Message no. 7008[Branch from no. 6991] Posted by Imran Pathan (ipathan) on Thursday, March 23, 2006 3:47pm Subject: Re: Constraints I agree with shagun. U can use either technique. Imran ------------------------------------------------------------ Message no. 7009 Posted by Waseem Manzoor (wmanzoor) on Thursday, March 23, 2006 3:58pm Subject: A real case scenerio In real time projects I saw lot of databses those do not have any constraints.Means tables without foreign keys and primary keys. Mostly programmer handles these things through front end programming? Which way is efficient? should we define these constraints on database level or is it ok to handle through front end languages? Waseem Manzoor ------------------------------------------------------------ Message no. 7010[Branch from no. 7009] Posted by Sagun Piya (srpiya2) on Thursday, March 23, 2006 7:19pm Subject: Re: A real case scenerio hi Waseem, we can do it both ways. but in my view it is better to define constraint first while creating a table Sagun ------------------------------------------------------------ Message no. 7011[Branch from no. 7010] Posted by Sagun Piya (srpiya2) on Thursday, March 23, 2006 7:21pm Subject: Re: A real case scenerio it is efficient if we define the constraint at database level Sagun ------------------------------------------------------------ Message no. 7012[Branch from no. 7009] Posted by Krishnamurth Ashwini (kashwini) on Thursday, March 23, 2006 7:55pm Subject: Re: A real case scenerio Hi Waseem, I think, by specifying the constraints you are essentially maintaining the integrity and consistency on the database (for example when the contraints are specified for a table, you cannot accidently delete a table or to be more precise, it prevently accidental deletion) On the other hand, when you use front end programming you may end up losing crucial data due to accidently errors in coding. Ash ------------------------------------------------------------ Message no. 7016[Branch from no. 6975] Posted by Phanindra Maddhi (pmaddhi) on Friday, March 24, 2006 11:31am Subject: Re: Constraints Yaaa waseem is right , we have to first drop the child table where foreign exists and then we can drop the parent table. ------------------------------------------------------------ Message no. 7017[Branch from no. 7012] Posted by Rhonda Nichols (renichols2) on Friday, March 24, 2006 1:30pm Subject: Re: A real case scenerio I agree. I think the constraints should be at datbase level so that they are AUTOMATICALLY inforced and not dependent on some code some programmer wrote. Data integrity is too important to rely on human programming. -Rhonda ------------------------------------------------------------ Message no. 7018[Branch from no. 7009] Posted by Gnaneshwar Bukka (gbukka) on Friday, March 24, 2006 2:15pm Subject: Re: A real case scenerio I think constraints would be a good option waseem, coz human errors are quite common, anything can happen, constraints take care of the data base and losing data is avoided. Gnaneshwar Bukka. ------------------------------------------------------------ Message no. 7019[Branch from no. 7018] Posted by Muhammad Latif (mlatif) on Saturday, March 25, 2006 2:00am Subject: Re: A real case scenerio Hey Waseem: It depends on the situation and complexity of the environment. But I believe if we have a well developed database, it makes the front end programming less complex. It is always better to have a strong back end. Arsal ------------------------------------------------------------ Message no. 7023[Branch from no. 6978] Posted by Alex Auffenorde (aaauffenorde) on Saturday, March 25, 2006 2:51am Subject: Re: Constraints Right on Harold. The CASCADE command ensures all foreign key constraints are dropped. ------------------------------------------------------------ Message no. 7025[Branch from no. 6973] Posted by Venkat Munagala (vrmunagala) on Saturday, March 25, 2006 11:17am Subject: Re: Constraints I too agree with u sagun. Either of the technique can be used. Rohit ------------------------------------------------------------ Message no. 7028[Branch from no. 7009] Posted by Swarna Bangaru (sbangaru) on Saturday, March 25, 2006 1:19pm Subject: Re: A real case scenerio I thinks defining the constraints at the database level is more efficient than hnadling it with the front end programming. Using constraints at the database level will keep the data integretiy intact and moreover front end programming is prone to errors and these can effect the database. ------------------------------------------------------------ Message no. 7030[Branch from no. 7028] Posted by Swarna Bangaru (sbangaru) on Saturday, March 25, 2006 4:42pm Subject: Re: A real case scenerio Sorry for the typo in my last posting ------------------------------------------------------------ Message no. 7031[Branch from no. 6995] Posted by Krupa Kandavalli (kkandavalli) on Saturday, March 25, 2006 4:49pm Subject: Re: Foreign Key Foreign key is used to link tables in a database and to maintain data integrity.. ------------------------------------------------------------ Message no. 7050[Branch from no. 7025] Posted by Abhash Bhandary (abhandary) on Saturday, March 25, 2006 11:50pm Subject: Re: Constraints Both the ways will surely work Abhash ------------------------------------------------------------ Message no. 7051[Branch from no. 7031] Posted by Abhash Bhandary (abhandary) on Saturday, March 25, 2006 11:53pm Subject: Re: Foreign Key A foreign key is a table column that establishes a link from the table it resides in to the primary key or a candidate key in another, related table Abhash ------------------------------------------------------------ Message no. 7057[Branch from no. 6995] Posted by Phanindra Maddhi (pmaddhi) on Sunday, March 26, 2006 1:24pm Subject: Re: Foreign Key It is the primary key of other table.... ------------------------------------------------------------ Message no. 7058[Branch from no. 6973] Posted by Phanindra Maddhi (pmaddhi) on Sunday, March 26, 2006 1:26pm Subject: Re: Constraints I agree with ash.... ------------------------------------------------------------