SQL Server/T-SQL Tutorial/Constraints/REFERENCES — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 13:25, 26 мая 2010

Making a Table be Self-Referencing

   <source lang="sql">

5> 6> CREATE TABLE Employees 7> ( 8> EmployeeID int IDENTITY NOT NULL, 9> FirstName varchar(25) NOT NULL, 10> MiddleInitial char(1) NULL, 11> LastName varchar(25) NOT NULL, 12> Title varchar(25) NOT NULL, 13> SSN varchar(11) NOT NULL, 14> Salary money NOT NULL, 15> PriorSalary money NOT NULL, 16> LastRaise AS Salary - PriorSalary, 17> HireDate smalldatetime NOT NULL, 18> TerminationDate smalldatetime NULL, 19> ManagerEmpID int NOT NULL, 20> Department varchar(25) NOT NULL 21> ) 22> GO 1> 2> EXEC sp_help Employees 3> GO Name Owner

                                                         Type                            Created_datetime

-----------------------------------------------------------------------
------------------------------- -----------------------

Employees dbo

                                                         user table                      2008-08-17 13:06:24.657

Column_name Type

                                                         Computed                            Length      Prec  Scale Nullable                            TrimTrailingBlanks                  FixedLenNu

llInSource Collation


-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------

EmployeeID int

                                                         no                                            4 10    0     no                                  (n/a)                               (n/a)
                         NULL

FirstName varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

MiddleInitial char

                                                         no                                            1             yes                                 no                                  yes
                         SQL_Latin1_General_CP1_CI_AS

LastName varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

Title varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

SSN varchar

                                                         no                                           11             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

Salary money

                                                         no                                            8 19    4     no                                  (n/a)                               (n/a)
                         NULL

PriorSalary money

                                                         no                                            8 19    4     no                                  (n/a)                               (n/a)
                         NULL

LastRaise money

                                                         yes                                           8 19    4     yes                                 (n/a)                               (n/a)
                         NULL

HireDate smalldatetime

                                                         no                                            4             no                                  (n/a)                               (n/a)
                         NULL

TerminationDate smalldatetime

                                                         no                                            4             yes                                 (n/a)                               (n/a)
                         NULL

ManagerEmpID int

                                                         no                                            4 10    0     no                                  (n/a)                               (n/a)
                         NULL

Department varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment

          Not For Replication

---------------------------------------- ------------------------------
-------------------

EmployeeID 1

        1                   0

RowGuidCol


No rowguidcol column defined. Data_located_on_filegroup


PRIMARY The object "Employees" does not have any indexes, or you do not have permissions. No constraints are defined on object "Employees", or you do not have permissions. No foreign keys reference table "Employees", or you do not have permissions on referencing tables. Table is referenced by views


1> ALTER TABLE Employees 2> ADD CONSTRAINT PK_EmployeeID 3> PRIMARY KEY (EmployeeID) 4> GO 1> 2> EXEC sp_help Employees 3> GO Name Owner

                                                         Type                            Created_datetime

-----------------------------------------------------------------------
------------------------------- -----------------------

Employees dbo

                                                         user table                      2008-08-17 13:06:24.657

Column_name Type

                                                         Computed                            Length      Prec  Scale Nullable                            TrimTrailingBlanks                  FixedLenNu

llInSource Collation


-----------------------------------------------------------------------
----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------
--------------------------------------------------------------------------------------------------------------------------------

EmployeeID int

                                                         no                                            4 10    0     no                                  (n/a)                               (n/a)
                         NULL

FirstName varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

MiddleInitial char

                                                         no                                            1             yes                                 no                                  yes
                         SQL_Latin1_General_CP1_CI_AS

LastName varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

Title varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

SSN varchar

                                                         no                                           11             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

Salary money

                                                         no                                            8 19    4     no                                  (n/a)                               (n/a)
                         NULL

PriorSalary money

                                                         no                                            8 19    4     no                                  (n/a)                               (n/a)
                         NULL

LastRaise money

                                                         yes                                           8 19    4     yes                                 (n/a)                               (n/a)
                         NULL

HireDate smalldatetime

                                                         no                                            4             no                                  (n/a)                               (n/a)
                         NULL

TerminationDate smalldatetime

                                                         no                                            4             yes                                 (n/a)                               (n/a)
                         NULL

ManagerEmpID int

                                                         no                                            4 10    0     no                                  (n/a)                               (n/a)
                         NULL

Department varchar

                                                         no                                           25             no                                  no                                  no
                         SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment

          Not For Replication

---------------------------------------- ------------------------------
-------------------

EmployeeID 1

        1                   0

RowGuidCol


No rowguidcol column defined. Data_located_on_filegroup


PRIMARY index_name index_description

                                                                                                                                           index_keys




-----------------------------------------------------------------------
------------------------------------------------------------










PK_EmployeeID clustered, unique, primary key located on PRIMARY

                                                                                                                                           EmployeeID




constraint_type constraint_name

                                                                           delete_action update_action status_enabled status_for_replication constraint_keys




-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------










PRIMARY KEY (clustered) PK_EmployeeID

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  EmployeeID




No foreign keys reference table "Employees", or you do not have permissions on referencing tables. Table is referenced by views


1> 2> drop table Employees; 3> GO</source>


ON UPDATE CASCADE ON DELETE NO ACTION

   <source lang="sql">

2> CREATE TABLE customer 3> ( 4> cust_id int NOT NULL IDENTITY PRIMARY KEY, 5> cust_name varchar(50) NOT NULL 6> ) 7> GO 1> CREATE TABLE orders 2> ( 3> order_id int NOT NULL IDENTITY PRIMARY KEY, 4> cust_id int NOT NULL REFERENCES customer(cust_id) 5> ON UPDATE CASCADE ON DELETE NO ACTION 6> ) 7> GO 1> 2> EXEC sp_helpconstraint customer 3> GO Object Name





customer


constraint_type constraint_name

                                                                           delete_action update_action status_enabled status_for_replication constraint_keys




-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------










PRIMARY KEY (clustered) PK__customer__43A1D464

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  cust_id




Table is referenced by foreign key




master.dbo.orders: FK__orders__cust_id__4E1F62D7

1> 2> EXEC sp_helpconstraint orders 3> GO Object Name





orders


constraint_type constraint_name

                                                                           delete_action update_action status_enabled status_for_replication constraint_keys




-----------------------------------------------------
------------- ------------- -------------- ---------------------- ----------------------------------------------------------










FOREIGN KEY FK__orders__cust_id__4E1F62D7

                                                                           No Action     Cascade       Enabled        Is_For_Replication     cust_id




                                                                                                                                             REFERENCES master.dbo.customer (cust_id)



PRIMARY KEY (clustered) PK__orders__4D2B3E9E

                                                                           (n/a)         (n/a)         (n/a)          (n/a)                  order_id




No foreign keys reference table "orders", or you do not have permissions on referencing tables. 1> 2> drop table orders; 3> GO 1> 2></source>


Primary key and foreign key

   <source lang="sql">

4> CREATE TABLE master_customer 5> ( 6> cust_id int NOT NULL IDENTITY PRIMARY KEY, 7> cust_name varchar(50) NOT NULL 8> ) 9> GO 1> 2> 3> CREATE TABLE customer_location 4> ( 5> cust_id int NOT NULL, 6> cust_loc smallint NOT NULL, 7> CONSTRAINT PK_CUSTOMER_LOCATION PRIMARY KEY (cust_id,cust_loc), 8> CONSTRAINT FK_CUSTOMER_LOCATION FOREIGN KEY (cust_id) 9> REFERENCES master_customer (cust_id) 10> ) 11> GO 1> 2> 3> CREATE TABLE orders 4> ( 5> order_id int NOT NULL IDENTITY PRIMARY KEY, 6> 7> cust_id int NOT NULL, 8> cust_loc smallint NOT NULL, 9> CONSTRAINT FK_ORDER_MASTER_CUST FOREIGN KEY (cust_id) 10> REFERENCES master_customer (cust_id), 11> CONSTRAINT FK_ORDER_CUST_LOC FOREIGN KEY (cust_id, cust_loc) 12> REFERENCES customer_location (cust_id, cust_loc) 13> ) 14> GO 1> 2> drop table orders; 3> GO 1> 2> drop table customer_location; 3> GO 1> 2> drop table master_customer; 3> GO</source>


REFERENCES customer(cust_id) (from another table)

   <source lang="sql">

3> CREATE TABLE customer 4> ( 5> cust_id int NOT NULL IDENTITY PRIMARY KEY, 6> cust_name varchar(50) NOT NULL 7> ) 8> GO 1> 2> CREATE TABLE orders 3> ( 4> order_id int NOT NULL IDENTITY PRIMARY KEY, 5> cust_id int NOT NULL REFERENCES customer(cust_id) 6> ) 7> GO 1></source>


Self-Referencing Tables

   <source lang="sql">

4> CREATE TABLE employee 5> ( 6> emp_id int NOT NULL PRIMARY KEY, 7> emp_name varchar(30) NOT NULL, 8> mgr_id int NOT NULL REFERENCES employee(emp_id) 9> ) 10> GO 1> 2> INSERT employee VALUES (1, "Chris Smith", 1) 3> GO (1 rows affected) 1> 2> 3> drop table employee; 4> GO</source>