SQL Server/T-SQL Tutorial/Constraints/REFERENCES
Содержание
Making a Table be Self-Referencing
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
ON UPDATE CASCADE ON DELETE NO ACTION
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>
Primary key and foreign key
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
REFERENCES customer(cust_id) (from another table)
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>
Self-Referencing Tables
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