Полезная информация

next up previous contents index
Next: Performing Joins Up: Joining Tables Previous: Joined Tables

Creating Joined Tables

Figure [*] shows the SQL statements needed to create those tables.

  
Figure: Creation of company tables
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...MERIC(8,2)
\par test->~)\textbackslash{}g
\par CREATE\end{list}\par
\end{figure}

The customer, employee, and part tables have a column to hold their unique identification numbers. The salesorder8.2 table has columns to hold the customer, employee, and part numbers associated with the sales order. For sake of simplicity, we will assume that each salesorder contains only one part number.

We have used underscores(_) to allow multiple words in column names, i.e. customer_id. This is common. You could enter the column as CustomerId, but POSTGRESQL converts all identifiers, like column and table names, to lowercase, so the actual column name becomes customerid, which is not very clear. You can't put spaces in table or column names either unless you put double quotes(") around the name like "customer id". Double quotes also preserve any capitalization you supply. If you decide to use this feature, you have to put double quotes around the table or column name every time you reference it. This can be cumbersome.

Keep in mind that all table and column names not protected by double quotes should be made up of only letters, numbers, and the underscore character. Each name must start with a letter and not a number. Don't use punctuation, except underscore, in your names either. For example, address, office, and zipcode9 are valid names, while 2pair and my# are not.

The example also shows the existence of a column named customer_id in two tables. This is done because the two columns contain the same type of number, a customer id number. Naming them the same clearly shows which columns join the tables together. If you wanted to use unique names, you could name the column salesorder_customer_id or sales_cust_id. This makes the column names unique, but still documents the columns to be joined.

Figure [*] shows the insertion of a row into the customer, employee, and part tables. It also shows the insertion of a row into the salesorder table, using the same customer, employee, and part numbers to link the salesorder row to the other rows we inserted.8.3

  
Figure: Insertion into company tables
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...~~~~~~~~~~~~~~~~~18.39
\par test->~)\textbackslash{}g\end{list}\par
\end{figure}


next up previous contents index
Next: Performing Joins Up: Joining Tables Previous: Joined Tables
Bruce Momjian
1999-11-21