Create Foreign Key Relationships

What is a FOREIGN KEY?

A FOREIGN KEY provides a way of enforcing referential integrity within SQL Server. In simple words, foreign key ensures values in one table must be present in another table.

Rules for FOREIGN KEY

NULL is allowed in Squốc lộ Foreign key. The table being referenced is called the Parent Table The table with the Foreign Key in SQL is called Child Table. The Squốc lộ Foreign Key in child table references the primary key in the parent table. This parent-child relationship enforces the rule which is known as "Referential Integrity."The Below Foreign Key in Squốc lộ example with diagram summarizes all the above sầu points for FOREIGN KEY How Foreign Key Works In this tutorial, you will learn

How lớn Create FOREIGN KEY in SQL

We can Create a Foreign Key in SQL VPS in 2 ways:
SQL Server Management Studio T-SQL

Squốc lộ Server Management Studio

Parent Table: Say, we have sầu an existing Parent table as "Course." Course_ID and Course_name are two columns with Course_Id as Primary Key. Child Table: We need lớn create the second table as a child table. "Course_ID" & "Course_Strength" as two columns. However, "Course_ID" shall be Foreign Key.

Bạn đang xem: Create foreign key relationships

Step 1) Right Cliông chồng on Tables>New> Table… Step 2) Enter two column name as "Course_ID" và "Course_Strength." Right cliông xã on "Course_Id" Column. Now cliông chồng on Relationship. Step 3) In "Foreign Key Relationship," Cliông chồng "Add" Step 4) In "Table & Column Spec" clichồng on "…" icon Step 5) Select "Primary Key Table" as "COURSE" & the new table now being created as "Foreign Key Table" from the drop down. Step 6) "Primary Key Table" - Select "Course_Id" column as "Primary Key table" column. "Foreign Key Table"- Select "Course_Id" column as "Foreign Key table" column. Click OK. Step 7) Clichồng on Add.

Xem thêm: Giá Chăn Ga Gối Đệm Hàn Quốc, Bảng Giá Chăn Ga Gối Everon Chính Hãng Hàn Quốc

Step 8) Give sầu the Table name as "Course_Strength" & cliông chồng on OK. Result: We have sầu phối Parent-child relationship between "Course" and "Course_strength."

T-SQL: Create a Parent-child table using T-SQL

Parent Table: Reconsider, we have an existing Parent table with table name as "Course." Course_ID & Course_name are two columns with Course_Id as Primary Key. Child Table: We need to lớn create the second table as the child table with the name as "Course_Strength_TSquốc lộ." "Course_ID" và "Course_Strength" as two columns for child table Course_Strength_TSQL." However, "Course_ID" shall be Foreign Key. Below is the syntax to create a table with FOREIGN KEY Syntax: CREATE TABLE childTable( column_1 datatype < NULL |NOT NULL >, column_2 datatype < NULL |NOT NULL >, ... CONSTRAINT fkey_name FOREIGN KEY (child_column1, child_column2, ... child_column_n) REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n) < ON DELETE CASCADE > < ON UPDATE NO ACTION > );Here is a description of the above parameters: childTable is the name of the table that is to be created. column_1, column_2- the columns to lớn be added lớn the table. fkey_name- the name of the foreign key constraint to be created. child_column1, child_column2…child_column_n- the name of chidTable columns khổng lồ reference the primary key in parentTable. parentTable- the name of parent table whose key is to lớn be referenced in the child table. parent_column1, parent_column2, ... parent_column3- the columns making up the primary key of parent table. ON DELETE. An optional parameter. It specifies what happens to lớn the child data after deletion of the parent data. Some of the values for this parameter include NO ACTION, SET NULL, CASCADE, or SET DEFAULT. ON UPDATE- An optional parameter. It specifies what happens to the child data after update on the parent data. Some of the values for this parameter include NO ACTION, SET NULL, CASCADE, or SET DEFAULT. NO ACTION- used together with ON DELETE và ON UPDATE. It means that nothing will happen lớn the child data after the update or deletion of the parent data. CASCADE- used together with ON DELETE & ON UPDATE. The child data will either be deleted or updated after the parent data has been deleted or updated. SET NULL- used together with ON DELETE and ON UPDATE. The child will be phối to lớn null after the parent data has been updated or deleted. SET DEFAULT- used together with ON DELETE & ON UPDATE. The child data will be phối to default values after an update or delete on the parent data. Let"s see a Foreign Key in SQL example to create a table with One Column as a FOREIGN KEY: Query: CREATE TABLE Course_Strength_TSQL(Course_ID Int,Course_Strength Varchar(20) CONSTRAINT FK FOREIGN KEY (Course_ID)REFERENCES COURSE (Course_ID))Step 1) Run the query by clicking on exeđáng yêu. Result: We have set Parent-child relationship between "Course" & "Course_strength_TSQL."

Using ALTER TABLE

Now we will learn how lớn use Foreign Key in Squốc lộ & add Foreign Key in SQL server using the ALTER TABLE statement, we will use the syntax given below: ALTER TABLE childTableADD CONSTRAINT fkey_name FOREIGN KEY (child_column1, child_column2, ... child_column_n) REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);Here is a mô tả tìm kiếm of the parameters used above: childTable is the name of the table that is lớn be created. column_1, column_2- the columns to be added khổng lồ the table. fkey_name- the name of the foreign key constraint khổng lồ be created. child_column1, child_column2…child_column_n- the name of chidTable columns to reference the primary key in parentTable. parentTable- the name of parent table whose key is lớn be referenced in the child table. parent_column1, parent_column2, ... parent_column3- the columns making up the primary key of parent table. Alter table add Foreign Key example: ALTER TABLE departmentĐịa chỉ cửa hàng CONSTRAINT fkey_student_admission FOREIGN KEY (admission) REFERENCES students (admission);We have sầu created a foreign key named fkey_student_admission on the department table. This foreign key references the admission column of the students table.

Example Query FOREIGN KEY

First, let"s see our Parent Table Data, COURSE.