J O E C E L K O ’ S SQL FOR SMARTIES: ADVANCED SQL PROGRAMMING THIRD EDITION The Morgan Kaufmann Series in Data Management Systems Series Editor: Jim Gray, Microsoft Research • Joe Celko’s SQL for Smarties: Advanced SQL Programming, Third Edition, Joe Celko • Moving Objects Databases, Ralf Güting and Markus Schneider • Foundations of Multidimensional and Metric Data Structures, Hanan Samet • Joe Celko’s SQL Programming Style, Joe Celko • Data Mining, Second Edition: Concepts and Techniques, Ian Witten and Eibe Frank • Fuzzy Modeling and Genetic Algorithms for Data Mining and Exploration, Earl Cox • Data Modeling Essentials, Third Edition, Graeme C. Simsion and Graham C. Witt • Location-Based Services, Jochen Schiller and Agnès Voisard • Database Modeling with Microsft® Visio for Enterprise Architects, Terry Halpin, Ken Evans, Patrick Hallock, Bill Maclean • Designing Data-Intensive Web Applications, Stephano Ceri, Piero Fraternali, Aldo Bongio, Marco Brambilla, Sara Comai, and Maristella Matera • Mining the Web: Discovering Knowledge from Hypertext Data, Soumen Chakrabarti • Advanced SQL: 1999—Understanding Object-Relational and Other Advanced Features, Jim Melton • Database Tuning: Principles, Experiments, and Troubleshooting Techniques, Dennis Shasha and Philippe Bonnet • SQL:1999—Understanding Relational Language Components, Jim Melton and Alan R. Simon • Information Visualization in Data Mining and Knowledge Discovery, Edited by Usama Fayyad, Georges G. Grinstein, and Andreas Wierse • Transactional Information Systems: Theory, Algorithms, and Practice of Concurrency Control and Recovery, Gerhard Weikum and Gottfried Vossen • Spatial Databases: With Application to GIS, Philippe Rigaux, Michel Scholl, and Agnes Voisard • Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design, Terry Halpin • Component Database Systems, Edited by Klaus R. Dittrich and Andreas Geppert • Managing Reference Data in Enterprise Databases: Binding Corporate Data to the Wider World, Malcolm Chisholm • Data Mining: Concepts and Techniques, Jiawei Han and Micheline Kamber • Understanding SQL and Java Together: A Guide to SQLJ, JDBC, and Related Technologies, Jim Melton and Andrew Eisenberg • Database: Principles, Programming, and Performance, Second Edition, Patrick and Elizabeth O'Neil • The Object Data Standard: ODMG 3.0, Edited by R. G. G. Cattell and Douglas K. Barry • Data on the Web: From Relations to Semistructured Data and XML, Serge Abiteboul, Peter Buneman, and Dan Suciu • Data Mining: Practical Machine Learning Tools and Techniques with Java Implementations, Ian Witten and Eibe Frank • Joe Celko’s SQL for Smarties: Advanced SQL Programming, Second Edition, Joe Celko • Joe Celko’s Data and Databases: Concepts in Practice, Joe Celko • Developing Time-Oriented Database Applications in SQL, Richard T. Snodgrass • Web Farming for the Data Warehouse, Richard D. Hackathorn • Database Modeling & Design, Third Edition, Toby J. Teorey • Management of Heterogeneous and Autonomous Database Systems, Edited by Ahmed Elmagarmid, Marek Rusinkiewicz, and Amit Sheth • Object-Relational DBMSs: Tracking the Next Great Wave, Second Edition, Michael Stonebraker and Paul Brown,with Dorothy Moore • A Complete Guide to DB2 Universal Database, Don Chamberlin • Universal Database Management: A Guide to Object/Relational Technology, Cynthia Maro Saracco • Readings in Database Systems, Third Edition, Edited by Michael Stonebraker and Joseph M. Hellerstein • Understanding SQL’s Stored Procedures: A Complete Guide to SQL/PSM, Jim Melton • Principles of Multimedia Database Systems, V. S. Subrahmanian • Principles of Database Query Processing for Advanced Applications, Clement T. Yu and Weiyi Meng • Advanced Database Systems, Carlo Zaniolo, Stefano Ceri, Christos Faloutsos, Richard T. Snodgrass, V. S. Subrahmanian, and Roberto Zicari • Principles of Transaction Processing, Philip A. Bernstein and Eric Newcomer • Using the New DB2: IBMs Object-Relational Database System, Don Chamberlin • Distributed Algorithms, Nancy A. Lynch • Active Database Systems: Triggers and Rules For Advanced Database Processing, Edited by Jennifer Widom and Stefano Ceri • Migrating Legacy Systems: Gateways, Interfaces, & the Incremental Approach, Michael L. Brodie and Michael Stonebraker • Atomic Transactions, Nancy Lynch, Michael Merritt, William Weihl, and Alan Fekete • Query Processing for Advanced Database Systems, Edited by Johann Christoph Freytag, David Maier, and Gottfried Vossen • Transaction Processing: Concepts and Techniques, Jim Gray and Andreas Reuter • Building an Object-Oriented Database System: The Story of O , Edited by François 2 Bancilhon, Claude Delobel, and Paris Kanellakis • Database Transaction Models for Advanced Applications, Edited by Ahmed K. Elmagarmid • A Guide to Developing Client/Server SQL Applications, Setrag Khoshafian, Arvola Chan, Anna Wong, and Harry K. T. Wong • The Benchmark Handbook for Database and Transaction Processing Systems, Second Edition, Edited by Jim Gray • Camelot and Avalon: A Distributed Transaction Facility, Edited by Jeffrey L. Eppinger, Lily B. Mummert, and Alfred Z. Spector • Readings in Object-Oriented Database Systems, Edited by Stanley B. Zdonik and David Maier J O E C E L K O ’ S SQL FOR SMARTIES: ADVANCED SQL PROGRAMMING THIRD EDITION Joe Celko Publishing Director Michael Forster Publisher Diane Cerra Publishing Services Manager Andre Cuello Senior Project Manager George Morrison Editorial Assistant Asma Stephan Cover Design Side by Side Studios Cover Image Side by Side Studios Composition Multiscience Press, Inc. Copyeditor Multiscience Press, Inc. Proofreader Multiscience Press, Inc. Indexer Multiscience Press, Inc. Interior printer The Maple-Vail Book Manufacturing Group Cover printer Phoenix Color Corp. Morgan Kaufmann Publishers is an imprint of Elsevier. 500 Sansome Street, Suite 400, San Francisco, CA 94111 This book is printed on acid-free paper. © 2005 by Elsevier Inc. All rights reserved. Designations used by companies to distinguish their products are often claimed as trademarks or registered trademarks. In all instances in which Morgan Kaufmann Publishers is aware of a claim, the product names appear in initial capital or all capital letters. Readers, however, should contact the appropriate companies for more complete information regarding trademarks and registration. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means-electronic, mechanical, photocopying, scanning, or otherwise-without prior written permission of the publisher. Permissions may be sought directly from Elsevier’s Science & Technology Rights Department in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: [email protected]. You may also complete your request on-line via the Elsevier homepage (http://elsevier.com) by selecting “Customer Support” and then “Obtaining Permissions.” Library of Congress Cataloging-in-Publication Data Application submitted. ISBN 13: 978-0-12-369379-2 ISBN 10: 0-12-369379-9 For information on all Morgan Kaufmann publications, visit our Web site at www.mkp.com or www.books.elsevier.com Printed in the United States of America 05 06 07 08 5 4 3 2 1 To Ann and Jackers C O N T E N T S Introduction to the Third Edition xxv 1.1 What Changed in Ten Years xxv 1.2 What Is New in This Edition xxvii 1.3 Corrections and Additions xxviii 1 Database Design 1 1.1 Schema and Table Creation 2 1.1.1 CREATE SCHEMA Statement 3 1.1.2 Manipulating Tables 5 1.1.3 Column Constraints 10 1.1.4 UNIQUE Constraints versus UNIQUE Indexes 17 1.1.5 Nested UNIQUE Constraints 18 1.1.6 Overlapping Keys 22 1.1.7 CREATE ASSERTION Constraints 25 1.1.8 Using VIEWs for Schema Level Constraints 25 1.1.9 Using PRIMARY KEYs and ASSERTIONs for Constraints 29 1.1.10 Avoiding Attribute Splitting 31 1.1.11 Modeling Class Hierarchies in DDL 34 1.2 Generating Unique Sequential Numbers for Keys 36 1.2.1 IDENTITY Columns 37 1.2.2 ROWID and Physical Disk Addresses 39 1.2.3 Sequential Numbering in Pure SQL 39 1.2.4 GUIDs 41 1.2.5 Sequence Generator Functions 42 1.2.6 Unique Value Generators 42 1.2.7 Preallocated Values 44 1.2.8 Random Order Values 45 1.3 A Remark on Duplicate Rows 48 1.4 Other Schema Objects 50 1.4.1 Schema Tables 50 1.4.2 Temporary Tables 51 1.4.3 CREATE DOMAIN Statement 51