sql for smarties 读书笔记<1>

第一章 数据库设计(Database Design)

 

1.1 模式和创建表

 

Create Table

CREATE TABLE <table name> (<table element list>)
<table element list> ::=
<table element> | <table element>, <table element list>

 
<table element> ::=
<column definition> | <table constraint definition>

 

Column definition

<column definition> ::=
<column name> <data type>
[<default clause>]
[<column constraint>...]


<column constraint> ::= NOT NULL
| <check constraint definition>
| <unique specification>
| <references specification>

 

The first important thing to notice here is that each column must have a data type, which it keeps unless you ALTER the table.

The data types fall into three major categories:numeric, character, and temporal data types.

 DEFAULT Clause

<default clause> ::=
[CONSTRAINT <constraint name>] DEFAULT <default option>
<default option> ::= <literal> | <system value> | NULL

 

<system value> ::= CURRENT_DATE | CURRENT_TIME |
CURRENT_TIMESTAMP | SYSTEM_USER | SESSION_USER | CURRENT_USER

If you do not provide a DEFAULT clause and the column is NULL-able, the system will provide a NULL as the default. If all that fails, you will get an error message about missing data.

The most common tricks are to use a zero in numeric columns; a string to encode a missing value ('{{unknown}}') or a true default (“same address”) in character columns; and the system timestamp to mark transactions.

1.1.3 Column constraints

      Column constraints are rules attached to a table. All the rows in the table are validated against them.

<constraint name definition> ::= CONSTRAINT <constraint name>
<constraint attributes> ::=
<constraint check time> [[NOT] DEFERRABLE]
| [NOT] DEFERRABLE [<constraint check time>]
<constraint check time> ::= INITIALLY DEFERRED | INITIALLY
IMMEDIATE

A deferrable constraint can be “turned off” during a transaction. The initial state tells you whether to enforce it at the start of the transaction or wait until the end of the transaction, before the COMMIT.

1. If INITIALLY DEFERRED is specified, then the constraint has to be DEFERRABLE.
2. If INITIALLY IMMEDIATE is specified or implicit and neither DEFERRABLE nor NOT DEFERRABLE is specified, then NOT DEFERRABLE is implicit.

NOT NULL Constraint

The NULL is a special marker in SQL that belongs to all data types. SQL is the only language that has such a creature;

 

NULL means that we have a missing, unknown, miscellaneous, or inapplicable value in the data. It can mean many other things, but just
consider those four for now.

 

When you use NULLs in math calculations, they propagate in the results so that the answer is another NULL. When you use them inlogical expressions or comparisons, they return a logical value of UNKNOWN and give SQL its strange three-valued logic.

 

In short, NULLs cause a lot of irregular features in SQL, which we will discuss later.

CHECK() Constraint

The CHECK() constraint tests the rows of the table against a logical expression, which SQL calls a search condition, and rejects rows whose search condition returns FALSE. However, the constraint accepts rows when the search condition returns TRUE or UNKNOWN. This is not the same rule as the WHERE clause, which rejects rows that test UNKNOWN.

 

CHECK (rating BETWEEN 1 AND 10)

CHECK (sex IN (0, 1, 2, 9))

 

Although it is optional, it is a really good idea to use a constraint name.

If you provide your own, you can drop the constraint more easily and understand the error messages when the constraint is violated.

The real power of the CHECK() clause comes from writing complex expressions that verify relationships with other rows, with other tables, or with constants.

In Standard SQL, the CHECK() constraint can reference any schema object.

 

First, let’s enforce the rule that no country can export more than ten titles.
[limitations of check() http://msdn.microsoft.com/zh-cn/library/ms188258.aspx]

因为CHECK约束的限制，我将原文中的sql语句改为如下（似乎不符合原书的意思了，原书的意思是在create table中加约束）:

IF EXISTS
(SELECT * FROM sys.objects o 
 WHERE o.[object_id]=OBJECT_ID(N'dbo.Exports')
 AND TYPE IN (N'U')
	)
DROP TABLE dbo.Exports

Go

IF EXISTS (SELECT * FROM sys.objects o WHERE o.[object_id]=OBJECT_ID(N'dbo.MovieCount') 

AND TYPE IN (N'FN') ) DROP TABLE dbo.MovieCount

Go

Create Table Exports
(
movie_title CHAR(25) NOT NULL,
country_code CHAR(2) NOT NULL, --USE 2-letter ISO nation codes
sales_amt DECIMAL(12,2) NOT NULL,
PRIMARY KEY (movie_title,country_code),
)
Go

CREATE FUNCTION MovieCount()
RETURNS int
AS
BEGIN
	DECLARE @retval INT
	SELECT @retval= COUNT(movie_title)
	FROM Exports e
	GROUP BY e.country_code
	RETURN @retval
END
GO


ALTER TABLE  dbo.Exports
Add CONSTRAINT National_Quota
CHECK (dbo.MovieCount()<=10)
Go

后面有两个问题的sql语句暂时不知道如何修改。

UNIQUE and PRIMARY KEY Constraints 

REFERENCES Clause

The referenced column must be in a UNIQUE constraint.

This is one of those situations where the PRIMARY KEY is important, but you can always play it safe and explicitly name a column.

Notice that the columns in a multicolumn FOREIGN KEY must match to a multicolumn PRIMARY KEY or UNIQUE constraint.

[CONSTRAINT <constraint name>]
FOREIGN KEY (<column list>)
  REFERENCES <referenced table name>[(<reference column list>)]

Referential Actions

The REFERENCES clause can have two subclauses that take actions when a database event changes the referenced table.

The two database events are updates and deletes, and the subclauses look like this: …

1.1.4 UNIQUE Constraints versus UNIQUE Indexes

virtually all products have some form of “access enhancement” for the DBA to use, and most often, it is an index.

The column referenced by a FOREIGN KEY has to be either a PRIMARY KEY or a column with a UNIQUE constraint;

A unique index on the same set of columns cannot be referenced, since the index is on one table and not a relationship between two tables.

an index is ordered, so the unique index might be an aid for sorting.

All the constraints can be defined as equivalent to some CHECK
constraint. For example:

PRIMARY KEY = CHECK (UNIQUE (SELECT <key columns> FROM <table>)
     AND (<key columns>) IS NOT NULL)

UNIQUE = CHECK (UNIQUE (SELECT <key columns> FROM <table>))

NOT NULL = CHECK (<column> IS NOT NULL)