A FOREIGN KEY is a key used to link two tables together.A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
Like other constraints, foreign keys enforce data integrity. It is always easier to deal with clean and correct data rather than cleaning up data on the fly. In addition, during the development and testing stages, foreign keys help catch a good number of bugs related to incorrect data processing
Example:
CREATE TABLE groups (
group_id INT IDENTITY PRIMARY KEY,
group_name VARCHAR
(100) NOT NULL
);
CREATE TABLE vendors (
vendor_id INT IDENTITY PRIMARY KEY,
vendor_name VARCHAR(100) NOT NULL,
group_id INT NOT NULL,
CONSTRAINT fk_group FOREIGN KEY (group_id)
REFERENCES groups(group_id)
)
The groups table now is called the parent table that is the table to which the foreign key constraint references. The vendors table is called the child table that is the table to which the foreign key constraint is applied
insert into groups(group_name) values
('Matrix group'),
('Sodexo group')
Now try to insert records into vendors table where group_id not present in group table
insert into vendors(vendor_name,group_id) values
('RK Corp',3)
Below error will throw.
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group". The conflict occurred in database "Rohit", table "dbo.groups", column 'group_id'.
Different types of SQL Server Constraints:
No comments:
Post a Comment