SQL Server/T-SQL/Constraints/Foreign Key
Содержание
Adding a Foreign Key to an Existing Table
<source lang="sql">
3> 4> CREATE TABLE employee( 5> id INTEGER NOT NULL PRIMARY KEY, 6> first_name VARCHAR(10), 7> last_name VARCHAR(10), 8> salary DECIMAL(10,2), 9> start_Date DATETIME, 10> region VARCHAR(10), 11> city VARCHAR(20), 12> managerid INTEGER 13> ); 14> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> create table EmergencyContact( 3> EID INTEGER NOT NULL PRIMARY KEY, 4> Name VARCHAR(20)) 5> GO 1> 2> ALTER TABLE EmergencyContact 3> ADD CONSTRAINT FK_EmergencyContact_Employee 4> FOREIGN KEY (EID) 5> REFERENCES Employee (ID) 6> GO 1> 2> 3> drop table EmergencyContact; 4> drop table employee; 5> GO
</source>
Define Primary key and foreign key
<source lang="sql">
1> CREATE TABLE Matches ( 2> Society_Group_Id int NOT NULL , 3> Match_Id int IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED, 4> Points_Against smallint NOT NULL 5> ) 6> GO 1> 2> CREATE TABLE Match_Scores ( 3> Match_Id int NOT NULL PRIMARY KEY CLUSTERED, 4> Player_Id int NOT NULL , 5> Score_Points tinyint NULL 6> ) 7> GO 1> 2> ALTER TABLE Match_Scores 3> ADD CONSTRAINT [FK_Match_Scores_Matches] FOREIGN KEY (Match_Id) 4> REFERENCES [Matches] ([Match_Id]) 5> GO 1> 2> drop table Matches 3> GO 1> 2> drop table Match_scores 3> GO 1>
</source>
How to use foreign key constraints
<source lang="sql">
The syntax of a column-level foreign key constraint [FOREIGN KEY] REFERENCES ref_table_name (ref_column_name)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
The syntax of a table-level foreign key constraint FOREIGN KEY (column_name_1 [, column_name_2]...)
REFERENCES ref_table_name (ref_column_name_1
[, ref_column_name_2]...)
[ON DELETE {CASCADE|NO ACTION}]
[ON UPDATE {CASCADE|NO ACTION}]
A foreign key constraint defined at the column level A statement that creates the primary key table 28> CREATE TABLE Bankers 29> (BankerID INT NOT NULL PRIMARY KEY, 30> BankerName VARCHAR(50) NOT NULL) 31> GO 1> 2> --A statement that creates the foreign key table 3> 4> CREATE TABLE Billings 5> (BillingID INT NOT NULL PRIMARY KEY, 6> BankerID INT NOT NULL REFERENCES Bankers (BankerID), 7> BillingTotal MONEY NULL) 8> GO 1> 2> --An INSERT statement that fails because a related row doesn"t exist 3> 4> INSERT Billings VALUES (1, 99, 100) 5> GO Msg 547, Level 16, State 1, Server J\SQLEXPRESS, Line 4 The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Billings__Banker__5F9EF494". The conflict occurred in database "master", table "dbo.Bankers", column "BankerID". The statement has been terminated. 1> 2> 3> drop table Billings; 4> drop table Bankers; 5> GO
</source>
Table-level constraints
<source lang="sql">
1> CREATE TABLE employee (emp_no INTEGER NOT NULL CONSTRAINT prim_empl PRIMARY KEY, 2> emp_fname CHAR(20) NOT NULL, 3> emp_lname CHAR(20) NOT NULL, 4> dept_no CHAR(4) NULL) 5> 6> GO 1> CREATE TABLE works_on (emp_no INTEGER NOT NULL, 2> project_no CHAR(4) NOT NULL, 3> job CHAR (15) NULL, 4> enter_date DATETIME NULL, 5> CONSTRAINT prim_works PRIMARY KEY (emp_no, project_no), 6> CONSTRAINT foreign_works FOREIGN KEY (emp_no) REFERENCES employee (emp_no)) 7> GO 1> -- Specified with two declarative integrity constraints 2> -- Both constraints are table-level constraints, 3> 4> drop table works_on 5> GO 1> 2> drop table employee 3> GO 1>
</source>
