Microsoft SQL Server Tutorials

SQL - How to create a Foreign Key

How to Create a Foreign Key in SQL Server

To impose a foreign key constraint we require the following things.

We require two tables for binding with each other and those two tables must have a common column for linking the tables.

Step 1 : To create Department Table (PARENT TABLE)

Create table Department(Deptno int primary key,DNAME varchar(50),LOCATION varchar(max))

Step 2 : Insert Records Into Department Table

insert into Department values(10,'Sales','Chennai') 

insert into Department values(20,'Production','Mumbai') 

insert into Department values(30,'Finance','Delhi') 

insert into Department values(40,'Research','Hyderabad')

Step 3 : To create Employee Table(CHILD TABLE)

Create table Employee(EID int,ENAME varchar(50),SALARY money,Deptno int foreign key references Department(Deptno))

Step 4 : Insert Records Into Department Table

insert into Employee values(101,'Sai',35000,10) insert into Employee values(102,'Pavan',45000,20) insert into Employee values(103,'Kamal',74000,30) insert into Employee values(104,'Ravi',58000,40)

 

The below records are not allowed into employee table

insert into Employee values(105,'Kamal',74000,50) 

insert into Employee values(106,'Ravi',58000,60)

 

When we impose the foreign key constraint and establish relation between the table,the followiong three rules will come into picture.

Rule1 :  Cannot insert a value into the foreign key column provided that value is not existing under the refernce key column of the parent table.

Rule2 : Cannot update the reference key value of a parent table provided that value has corresponding child record in the child table with out addressing what to do with the child record.

Rule3 : Cannot delete a record from the parent table provided that records reference key value has child record in the child table with out addressing what to do with the child record.

If we want to delete or update a record in the parent table when they have corresponding child records in the child table we are provide with a set of rules to perform delete and update operations knows as cascade rules.

On Delete cascade :  It is used to delete a key value in the parent table which is referenced by foreign key in other table all rows that contains those foreign keys in child table are also deleted.

On Update cascade : It is used to Update a key value in the parent table which is referenced by foreign key in other table all rows that contains those foreign keys in child table are also updated.

If we apply this rule while creating the child table like below

Create table Emp(EID int,ENAME varchar(50),SALARY money,Deptno int foreign key references Department(Deptno)on delete cascade on update cascade)

Example : 

Update Department set Deptno=222 where Deptno=20

Delete from Department where Deptno=222

logo

Microsoft SQL Server

Beginner 5 Hours

How to Create a Foreign Key in SQL Server

To impose a foreign key constraint we require the following things.

We require two tables for binding with each other and those two tables must have a common column for linking the tables.

Step 1 : To create Department Table (PARENT TABLE)

Create table Department(Deptno int primary key,DNAME varchar(50),LOCATION varchar(max))

Step 2 : Insert Records Into Department Table

insert into Department values(10,'Sales','Chennai') 

insert into Department values(20,'Production','Mumbai') 

insert into Department values(30,'Finance','Delhi') 

insert into Department values(40,'Research','Hyderabad')

Step 3 : To create Employee Table(CHILD TABLE)

Create table Employee(EID int,ENAME varchar(50),SALARY money,Deptno int foreign key references Department(Deptno))

Step 4 : Insert Records Into Department Table

insert into Employee values(101,'Sai',35000,10) insert into Employee values(102,'Pavan',45000,20) insert into Employee values(103,'Kamal',74000,30) insert into Employee values(104,'Ravi',58000,40)

 

The below records are not allowed into employee table

insert into Employee values(105,'Kamal',74000,50) 

insert into Employee values(106,'Ravi',58000,60)

 

When we impose the foreign key constraint and establish relation between the table,the followiong three rules will come into picture.

Rule1 :  Cannot insert a value into the foreign key column provided that value is not existing under the refernce key column of the parent table.

Rule2 : Cannot update the reference key value of a parent table provided that value has corresponding child record in the child table with out addressing what to do with the child record.

Rule3 : Cannot delete a record from the parent table provided that records reference key value has child record in the child table with out addressing what to do with the child record.

If we want to delete or update a record in the parent table when they have corresponding child records in the child table we are provide with a set of rules to perform delete and update operations knows as cascade rules.

On Delete cascade :  It is used to delete a key value in the parent table which is referenced by foreign key in other table all rows that contains those foreign keys in child table are also deleted.

On Update cascade : It is used to Update a key value in the parent table which is referenced by foreign key in other table all rows that contains those foreign keys in child table are also updated.

If we apply this rule while creating the child table like below

Create table Emp(EID int,ENAME varchar(50),SALARY money,Deptno int foreign key references Department(Deptno)on delete cascade on update cascade)

Example : 

Update Department set Deptno=222 where Deptno=20

Delete from Department where Deptno=222

Related Tutorials

Frequently Asked Questions for Microsoft SQL Server

To install Microsoft MySQL Server on Windows, you need to download the installer from the official MySQL website. Follow the on-screen instructions to complete the installation process and set up the server.

To upgrade MySQL Server, download the latest version from the official site, run the installer, and follow the upgrade instructions. Backup your data before starting the process.

You can integrate MySQL Server with web applications written in languages like PHP, Node.js, and Python by using the appropriate MySQL connector libraries.<

Stored procedures are precompiled SQL statements that can be executed repeatedly. They improve performance by reducing the amount of SQL code sent to the server.

The MySQL root user is the default administrator account with full privileges. To secure it, ensure strong password protection, disable remote access, and grant minimal permissions.

Microsoft MySQL Server is an open-source relational database management system (RDBMS) that provides high-performance data storage and retrieval. It is often used for web applications and other data-driven platforms.

The default username for Microsoft MySQL Server is "root". During the installation process, you'll set a password. If you forget it, you can reset the MySQL root password using the command line.

Common MySQL error codes include 1045 (Access Denied), 1062 (Duplicate Entry), and 2002 (Can't Connect to MySQL Server).

Some advantages include its high availability, scalability, open-source nature, and the support of a large community of developers. Microsoft MySQL Server is also compatible with various platforms and provides advanced data security features.

The MySQL query cache stores the result of SELECT queries to speed up response times for identical queries. You can enable it in the MySQL configuration file.

InnoDB supports ACID-compliant transactions, foreign keys, and row-level locking, making it more suitable for high-concurrency environments. MyISAM is faster for read-heavy operations but lacks transaction support.

You can manage users in MySQL using the CREATE USER, GRANT, and REVOKE commands to set permissions for specific users.

To configure Microsoft MySQL Server, you need to access the MySQL Configuration File (my.ini) and make necessary adjustments like setting up users, enabling ports, and optimizing server performance.

You can use the SHOW STATUS and SHOW VARIABLES commands to monitor MySQL performance. Alternatively, use tools like MySQL Workbench and Percona Monitoring and Management (PMM).

To optimize MySQL Server performance, you can tweak configuration settings like innodb_buffer_pool_size, query_cache_size, and tmp_table_size. Indexing and query optimization also play a crucial role.

The primary difference is that MySQL is open-source and cross-platform, while SQL Server is a proprietary product by Microsoft that primarily runs on Windows. MySQL is more popular in the open-source community, while SQL Server is used in enterprise environments.

To repair a corrupt table, you can use the REPAIR TABLE command in MySQL:

REPAIR TABLE table_name;

If you're facing MySQL connectivity issues, ensure that the MySQL service is running, the port is open, and your firewall settings are correctly configured.

You can import data into MySQL using the LOAD DATA INFILE command or by using tools like phpMyAdmin or MySQL Workbench.

To enable foreign keys, ensure your tables use the InnoDB storage engine. Then, define the FOREIGN KEY constraint in your table creation statement.

To check the version, simply open the command prompt or terminal and type:

mysql --version

This will return the current version of Microsoft MySQL Server installed on your system.

MySQL data types define the type of data a column can store. Some common types include VARCHAR, INT, DATE, TEXT, and BLOB.

To create a new database, use the following SQL command:

CREATE DATABASE database_name;

You can back up a MySQL Server database using the mysqldump command. For example:

mysqldump -u username -p database_name > backup_file.sql

Yes, Microsoft MySQL Server can run on various platforms, including Linux, Windows, and macOS, making it a versatile choice for developers.

line

Mail us :

Address :

India
  • Facebook_logo
  • Instagram_logo
  • Linkedin_logo
  • X-twitter_logo
  • Youtube_logo
  • Pinterest_logo

Sub Categories

Copyrights © 2024 letsupdateskills All rights reserved