TAILIEUCHUNG - SQL PROGRAMMING STYLE- P16

SQL PROGRAMMING STYLE- P16:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | 142 CHAPTER 7 HOW TO USE VIEWS ON DELETE CASCADE start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL end_date TIMESTAMP CHECK start_date end_date job_type INTEGER DEFAULT 99 NOT NULL CHECK job_type 99 CREATE TABLE Job_not99_Assignments store_nbr INTEGER NOT NULL REFERENCES Stores store_nbr ON UPDATE CASCADE ON DELETE CASCADE ssn CHAR 9 NOT NULL PRIMARY KEY REFERENCES Personnel ssn ON UPDATE CASCADE ON DELETE CASCADE start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL end_date TIMESTAMP CHECK start_date end_date job_type INTEGER DEFAULT 0 NOT NULL CHECK job_type BETWEEN 0 AND 98 -no 99 code Then build a UNION-ed VIEW CREATE VIEW JobAssignments store_nbr ssn start_date end_date job_type AS SELECT store_nbr ssn start_date end_date job_type FROM Job_not99_Assignments UNION ALL SELECT store_nbr ssn start_date end_date job_type FROM Job_99_Assignments The key and job_type constraints in each table working together will guarantee only one manager per store. The next step is to add INSTEAD OF triggers to the VIEW so that the users can insert update and delete from it easily. As an exercise for the reader How would you ensure that no store has more than two assistant managers Updatable VIEWs 143 Updatable VIEWs The SQL-92 standard is actually conservative about which VIEWs are updatable. They have to be based on the following 1. A SELECT statement on one and only one table but the VIEW can be defined on several layers of VIEWs on top of VIEWs. 2. The VIEW must include all of the columns of a UNIQUE or PRIMARY KEY constraint in the base table. This guarantees that all of the rows in the VIEW map back to one and only one row in the base table from which it is derived. 3. All base table columns not shown in the VIEW must have default values or be NULL-able. The reason for that is obvious You have to delete or insert a complete row into the base table so the system must be able to construct such a row. However other VIEWs are updatable and some vendors support .

• Cơ sở dữ liệu
• cơ sở dữ liệu MySQL
• database SQL
• thiết kế CSDL quan hệ
• Kiến trúc hệ quản trị CSDL
• đặc tính cơ sở dữ liệu
• bảo mật cơ sở dữ liệu
• Cơ sở dữ liệu
• Hệ quản trị cơ sở dữ liệu MySQL
• Quản trị cơ sở dữ liệu
• Làm việc với MySQL
• Giới thiệu về MySQL
• Bài giảng Phần mềm nguồn mở
• Phần mềm nguồn mở
• Hệ quản trị cơ sở dữ liệu
• Lịch sử phát triển MySQL
• Kiến trúc MySQL
• MySQL Command Line
• MySQL Query Browser
• giáo trình cơ sở dữ liệu
• lý thuyết cơ sở dữ liệu
• bái giảng cơ sở dữ liệu
• tài liệu cơ sở dữ liệu
• Kết nối với MySQL
• Truy xuất cơ sở dữ liệu
• Quản lý dữ liệu
• Kết nối cơ sở dữ liệu
• Cập nhật dữ liệu
• Kích hoạt dữ liệu
• cơ sở dữ liệu sql server
• MySQL cơ bản