Tables are used to store all the data in your database. Each row in a table holds an individual record that contains one or more related data items. Each column contains one particular type of data. You can use the Tables Utility to create and delete tables, to look at the structure of a table, and to retrieve or modify the data contained in a table.
Except for rows containing large objects (long varchar, byte, byte varying, and long byte data types), all rows in a given table are the same width, measured in bytes, with no row exceeding the lesser of the maximum configured row size and 32,000. The number of rows is limited only by disk space.
Each column is assigned a data type (see page 6) to indicate the type of data to be stored and the length of the data (or width of the column). For example, a column with a data type of varchar (25) can hold 25 text (ASCII) characters. A maximum of 1024 columns is allowed in a table. Each column has a name that uniquely identifies the column within the table.
A view is actually a special definition, or virtual table, constructed from one or more tables. A view is a way of looking at or updating data stored in tables and does not contain any data or exist in physical storage.
Views enable you to:
- Limit a user’s access to specific rows and columns of a table.
- Manipulate data from multiple tables as if all the data were contained in a single table.
- Gain access to aggregates (sets of data) as if they were individual columns of data.
Views simplify retrieval and modify the user’s view of data to only certain rows and columns in a table. For example, the table on which you base a view can contain the columns name, title, and hourly_rate. However, the view based on that table might only show columns for name and title and could be restricted to certain rows. Views also allow you to run queries and reports on specific subsets of data and to specify certain rows that fit particular criteria.
You can use the Tables Utility to look at the structure of a view, and to retrieve, modify, or perform computations on the data in the underlying tables. For example, while in the Tables Utility, you could access a particular view based on the emp and tasks tables to compute the average hourly rate of programmers (emp table) working on a particular project (tasks table).
Views are created with a query language such as SQL. You cannot create or destroy views from within the Ingres Menu or the Tables Utility. For detailed information on views, see the description of the create view command in your query language reference guide.
CREATE VIEW phone_view AS
SELECT empFName, empLName, empPhone FROM Employees;
GRANT SELECT ON phone_view TO public;
CREATE VIEW job_view AS
SELECT employeeID, empFName, empLName, jobTitle, managerID FROM Employees;
GRANT SELECT, UPDATE ON job_view TO managers;
CREATE VIEW pay_view AS
SELECT employeeID, empFName, empLName, payRate FROM Employees;
GRANT SELECT, UPDATE ON pay_view TO payroll;
An index is a table that indicates where data is stored in another table. It contains the locations of specified columns in the base table that are queried frequently. The index can speed up the retrieval of information.
You can examine an index’s structure but cannot perform any other operations on it in the Tables Utility. You create an index on one or more columns of a table, using a query language such as SQL. Whenever a user enters a query based on the indexed column in the base table, the index helps locate the information quickly. Use of indexes is recommended to improve performance of the queries in your applications.
Indexes are created with the create index query language statement. You cannot create, destroy, or directly query or run reports on indexes from within the Ingres Menu or the Tables Utility.
Syntax: As you would expect by now, the SQL to create an index is:
CREATE INDEX <indexname> ON <tablename> (<column>, <column>…);
To enforce unique values, add the UNIQUE keyword:
CREATE UNIQUE INDEX <indexname> ON <tablename> (<column>, <column>…);