SQL for Database Construction and Application Processing - Database Processing: Fundamentals, Design, and Implementation - page 297

Figure 7-15
Microsoft SQL Server 2012
View ridge Gallery Database
Diagram

Running the SQL statements in Figure 7-14 (or the specific variant in Chapter 10B for
Oracle Database 11 g Release 2, or Chapter 10C for MySQL 5.6) with your DBMS will generate
all of the tables, relationships, and constraints for the View Ridge Gallery Database. Figure 7-15
shows the completed table structure in SQL Server 2012 as a database diagram. It is far easier to
create these tables and relationships using SQL code than by using GUI displays, which are dis-
cussed in Chapter 10A (Microsoft SQL Server 2012), Chapter 10B (Oracle Database 11 g Release
2), and Chapter 10C (MySQL 5.6).

Microsoft access 2013 aNSI-89 SQL, unfor-
tunately, does not support a number of stan-
dard SQL features we have examined in this
discussion. However, you can run a basic SQL
CrEatE taBLE statement in aNSI-89 SQL, and then use the Microsoft access GUI
display to finish building the tables and relationships. Specifically:

Does Not Work with
Microsoft Access
ANSI-89 SQL

1. although Microsoft access supports a Number data type, it does not support
the ( m, n ) extension to specify the number of digits and the number of digits to
the right of the decimal place.

Solution: You can set these values in the table Design view after the column is
created.

2. although Microsoft access does support an autoNumber data type, it always
starts at 1 and increments by 1. Further, autoNumber cannot be used as an SQL
data type.

Solution: Set autoNumber data type manually after the table is created. any
other numbering system must be supported manually or by application code.

3. Microsoft access aNSI-89 SQL does not support the UNIQUE and CHECK column

constraints, nor the DEFaULt keyword.

Solution: Equivalent constraints and initial values can be set in the GUI table
Design view.

4. Microsoft access does completely support foreign key CONStraINt phrases.
although the basic referential integrity constraint can be created using SQL, the
ON UPDatE and ON DELEtE clauses are not supported.

Solution: ON UPDatE and ON DELEtE actions can be set manually after the
relationship is created.

5. Unlike SQL Server, Oracle Database, and MySQL, Microsoft access does not
support SQL scripts.

Solution: You can still create tables by using the SQL CrEatE command and
insert data by using the SQL INSErt command (discussed later in this chapter),
but you must do so one command at a time.