SQL Bible [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

SQL Bible [Electronic resources] - نسخه متنی

Alex Kriegel

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید






Schemas

A
schema is a logical database object holder. SQL99 defines
a schema as a named group of related objects. Creating schemas can be useful
when objects have circular references, that is, when we
need to create two tables each with a foreign key referencing the other table.
Different implementations treat schemas in slightly different ways.


CREATE SCHEMA statement


The
CREATE
SCHEMA statement has different meanings
in SQL99, Oracle, DB2, and MS SQL Server.

SQL99


SQL99 states that the
CREATE
SCHEMA statement creates a group of
objects that somehow logically relate to each other; the group has a name that
is called schema name. Also, you can grant privileges on
these objects within the
CREATE
SCHEMA statement. The syntax is

CREATE SCHEMA
{<schema_name> | AUTHORIZATION <authorization_id> |
<schema_name> AUTHORIZATION <authorizat_id> }
<create_object_statement>,...
<grant_privilege_statement>,...

The schema creator usually owns the
objects within the schema unless otherwise specified by using a different
authorization_id.

Objects that can be created as a part
of a schema include tables, views, domains, assertions, character sets,
collations, and translations; you can grant any valid privileges within the
grant_privilege_statement
clause.





Note

Creation of domains, assertions, character sets, collations,
and translations are not valid operations in all our three major databases even
though they are all part of SQL99 standards.






Cross-References

The
GRANT
PRIVILEGE statement is covered in
Chapter
12
(SQL and RDBMS Security).


Oracle
9i


We already mentioned that in Oracle
terminology the word schema is almost identical to
user. You still can use the
CREATE
SCHEMA statement in Oracle, but the
only use for that operation would be to create multiple objects in a single
transaction.





Note

The
CREATE
SCHEMA statement does
not
actually create a schema in Oracle. The schema is automatically
created when you create a user (see
Chapter
12
).


The syntax for
CREATE
SCHEMA is

CREATE SCHEMA AUTHORIZATION
<schema=your_user_name> <create_object_statement>,...
<grant_privilege_statement>,... ;

The schema name must be the same as
your Oracle user ID, otherwise the statement will fail. The valid objects to
create include tables and views; you can grant any valid object privileges on
them to anybody. The following example creates two tables in hypothetical
schema
ACMETEST (i.e., we assume that the
statement is run by user
ACMETEST that does not exist in the
ACME test database) and gives permissions on them to user
ACME:

CREATE
SCHEMA AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT CONSTRAINT
pk_addrprimary PRIMARY KEY, addr_custid_fn INT, addr_salesmanid_fn INT
CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n) ON DELETE
CASCADE, addr_address_s VARCHAR2(60), addr_type_s VARCHAR2(8) CONSTRAINT
chk_addr_type CHECK (addr_type_s IN ('BILLING', 'SHIPPING')), addr_city_s
VARCHAR2(18) CONSTRAINT nn_addr_city NOT NULL, addr_state_s CHAR(2), addr_zip_s
VARCHAR2(10) NOT NULL, addr_country_s CHAR(3) ) CREATE TABLE salesman (
salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY, salesman_code_s
VARCHAR2 (2) CONSTRAINT uk_salescode UNIQUE, salesman_name_s VARCHAR2 (50) NOT
NULL, salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus CHECK
(salesman_status_s in ('N', 'Y')) ) GRANT ALL ON salesman TO ACME GRANT ALL ON
address TO ACME ;





Note

The first
CREATE
TABLE statement will fail if you
didn't create the two tables as a part of one transaction
because it refers to the nonexistent
SALESMAN table (constraint
FK_ADDR_SALESMAN).






Note

If any statement within
CREATE
SCHEMA fails, all other statements
are also ignored.


DB2 UDB 8.1


The DB2
CREATE
SCHEMA statement seems to be closer to
SQL99 standards. The syntax is

CREATE
SCHEMA {<schema_name> | AUTHORIZATION <authorization_id> |
<schema_name> AUTHORIZATION <authorization_id> }
<create_object_statement>,...
<grant_privilege_statement>,...

The valid objects to create within the
create_object_statement clause are
tables, views, and indexes. The owner of the schema is either
authorization_id or (if not specified)
the user who issued the
CREATE
SCHEMA statement:

CREATE
SCHEMA ACMETEST AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT NOT
NULL CONSTRAINT pk_addrprimary PRIMARY KEY, addr_custid_fn INT,
addr_salesmanid_fn INT CONSTRAINT fk_addr_salesman REFERENCES salesman
(salesman_id_n) " ON DELETE CASCADE, addr_address_s VARCHAR(60), addr_type_s
VARCHAR(8) CONSTRAINT chk_addr_type CHECK (addr_type_s IN ('BILLING',
'SHIPPING')), addr_city_s VARCHAR(18) CONSTRAINT nn_addr_city NOT NULL,
addr_state_s CHAR(2), addr_zip_s VARCHAR(10) NOT NULL, addr_country_s CHAR(3) )
CREATE TABLE salesman ( salesman_id_n INT NOT NULL CONSTRAINT pk_salesmanprim
PRIMARY KEY, salesman_code_s VARCHAR (2) NOT NULL CONSTRAINT uk_salescode
UNIQUE, salesman_name_s VARCHAR (50) NOT NULL, salesman_status_s CHAR (1)
CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N', 'Y')) ) GRANT ALL
ON salesman TO ACME GRANT ALL ON address TO ACME

MS SQL Server 2000


MS SQL Server provides this syntax to
create a schema:

CREATE SCHEMA AUTHORIZATION
<owner> <create_object_statement>,...
<grant_privilege_statement>,...

The owner must have a valid security
account in the database. The statement below assumes the existence of account
ACMETEST:

CREATE
SCHEMA AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT CONSTRAINT
pk_addrprimary PRIMARY KEY, addr_custid_fn INT, addr_salesmanid_fn INT
CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n) ON DELETE
CASCADE, addr_address_s VARCHAR(60), addr_type_s VARCHAR(8) CONSTRAINT
chk_addr_type CHECK (addr_type_s IN ('BILLING', 'SHIPPING')), addr_city_s
VARCHAR(18) CONSTRAINT nn_addr_city NOT NULL, addr_state_s CHAR(2), addr_zip_s
VARCHAR(10) NOT NULL, addr_country_s CHAR(3) ) CREATE TABLE salesman (
salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY, salesman_code_s
VARCHAR (2) not null CONSTRAINT uk_salescode UNIQUE, salesman_name_s VARCHAR
(50) NOT NULL, salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus CHECK
(salesman_status_s in ('N', 'Y')) ) GRANT ALL ON salesman TO ACME GRANT ALL ON
ADDRESS TO ACME

/ 207