1) What is the maximum number of constraints that can be defined on a table? I think it is 32.
2) What are the logical and physical activities within the database?
3) How do I declare a cursor that selects only the set of duplicate rows? I don't want to use group by clause. How do I do the reverse of the query:
select distinct * from emp;
1) There is no limit to the number of constraints that can be defined on a table. A table can have only one PRIMARY KEY constraint. There is no limit on the number of FOREIGN KEY constraints on that table. But according to the Oracle documentation, "Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32 columns." And also from the Oracle documentation, "There is no limit to the number of CHECK constraints that can be defined that reference a column." This statement tells me that there is no limit to the number of total constraints on a table.
2) All database vendors build their database software to separate Logical and Physical activities in the database. This separation of powers helps to give the system flexibility and control. The physical activities are how the database software interacts with disk files, or memory, or networking protocols. The logical activities are create tables, selecting data, crunching data. When a user issues a SELECT, INSERT, UPDATE, or DELETE statement to the database, they are doing a logical operation. They don't care how it gets done. The database software then must perform the physical implementation of this operation.
3) In order to select duplicate rows, you must use the GROUP BY clause, if you plan on doing this with one SELECT statement. If you do not want to use the GROUP BY clause, then your only recourse is to pull all of the data into your application and then have your application sort out which rows are duplicates.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.