Constraints In Microsoft SQL Server 2000 - Foreign Key Constraints (Page 4 of 8 )
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables. When we add a Foreign key to the table, we are creating a dependency between the table for which we define the Foreign key (the referencing table) and the table your Foreign key references (the referenced table). Once we have set up a Foreign key for a table, any record inserted into the referencing table must either have a matching record in the referenced column(s) of the referenced table, or the value of the Foreign key column must be set to NOTNULL. Some examples will help to clarify this.
The syntax for the Foreign key is as follows:
FOREIGN KEY REFERENCES
<table name> (<column
name>)
[ON DELETE
{CASCADE | NO ACTION}]
[ON UPDATE {CASCADE | NO ACTION}]
Let's create another table in our Accounting database called Order. Please note: In this CREATE script we are going to use both Foreign key and Primary key constraints. The table code looks like this: (Example 4).
USE Accounting
CREATE
TABLE Orders
(
OrderID int IDENTITY NOT NULL
PRIMARY KEY
CustomerID FOREIGN KEY
REFERENCES Customers
(CustomerID),
OrderDate smalldatetime NOT
NULL,
EmployeeID int NOT
NULL
)
Note the actual column being referenced must have either a PRIMARY KEY or UNIQUE constraint defined on it. We will discuss UNIQUE constraints shortly. In order to make sure that we have successfully run the above code, run the following command:
EXEC
sp_helpconstraint
<table_name>
//So for our Orders it will be:
EXEC
sp_helpconstraint orders
When we run the command we will get information regarding name, criteria, and status for all constraints in the table. For our Orders tables it will be one Primary key and one Foreign key. The word CLUSTERED will appear right after the Primary key, this just means it has a clustered index. Unlike Primary keys, we are not limited to just one Foreign key on a table. We can have 0 to 253 Foreign keys in each table. The only limitation is that a given column can only reference one Foreign key.
Adding Foreign keys to an existing table is also simple and similar to adding a Primary key. Take a look at Example 5.
Use
Accounting
ALTER TABLE Orders
ADD CONSTRAINT Fk_EmpCreateOrders
FOREIGN KEY
(EmployeeID) REFERENCES Employee
(EmployeeID)
{mospagebreak title=Making a Self-Referencing Table}
What if the column we want to reference is not in another table, but within the same table? No problem. A table can be a referencing and a referenced table. Before we actually create this self-referencing constraint that references (not null) fields based on an identity column, it is critical to get at least one row in the table prior to addition of the Foreign key. Let’s look at example 6:
Use Accounting
ALTER TABLE Employees
ADD CONSTRAINT
Fk_EmpManager
FOREIGN KEY
(EmpMangerID) REFERENCES Employees
(EmployeeID)
We have our EMPLOYEES table, but if we were creating our EMPLOYEES table from scratch it is a self-referencing table and will look like this: (Example 7)
USE Accounting
CREATE TABLE
Employees
{
EmployeeID
int IDENTITY NOT NULL PRIMARY
KEY,
EmployeeName
varchar(30) NOT NULL,
SSN
varchar(11) NOT NULL,
Address1 varchar(25) NOT
NULL,
Address2 varchar(20)
NOT NULL,
City varchar(20)
NOTNULL,
State varchar(2)
NOT NULL,
Zip varchar(10)
NOT NULL,
Contact
varchar(25) NOT NULL,
Phone
char(15) NOT NULL,
Salary
money NOT NULL,
HireDate
smalldatetime NOT NULL,
TerminationDate smalldatetime
NULL,
EmpManagerID int
NOTNULL
REFERENCES EMPLOYEES(EmployeeID),
Department varchar(25) NOT NULL
}
If we try to delete the EMPLOYEES table at this point, we will get an error. We have established the reference in our ORDERS table to the EMPLOYEES table, the two tables are “schema bound.” We have to drop the Foreign key in the ORDERS table before SQL Server allows us to drop EMPLOYEES table.
Next: Cascading Updates And Deletes >>
More Database Code Articles
More By Gayathri Gokul
/ 116 
