SQL Foreign Key
※ Download: Primary key and foreign key in sql with examples
Its also called as composite key. Get access to world class Learning Platform to LEARN the tools and tricks to BUILD industry grade applications under our expert guidance. Below we will show some simple examples of how to set up foreign keys and how they work in MySQL.
Example Consider the structure of the following two tables. Specifying RESTRICT is the same as omitting the ON DELETE or ON UPDATE clause.
MySQL: “Foreign Keys” Used in an Example - There can be multiple foreign keys in a relation.
MySQL is the most widely used open source relational database management system in the world. MySQL is used by many web applications out there. While you are reading this article, you are actually indirectly using MySQL database. If you are completely new to relational database management systems, then I would recommend reading the below article to get an idea about it. Read: The primary responsibility of a database is to store information about different objects or entities, and their relationship with each other. For example, an educational institution might store details about students, courses provided. There can be a relationship between courses and students. This relationship can then be called as enrollment. If you are completely new to databases, then consider a database table as an excel sheet, with different columns and multiple rows. Columns will contain different attributes that will describe a row in that table. Attributes columns in the table will help us distinguish one entity from another one row from the other - A row in the students table will have all the details about a particular student, and a row in the courses table will describe course details. When we say students table, the table should have columns like Firstname, Last Name, Phone Number, Email Address etc. We could consider the name attribute column to help distinguish one student from the other ie: Uniquely identify one particular student. However that does not sound like a good solution. Because two different students rows could have identical names. To be able to identify one student uniquely, we need to have an attribute column , that is guaranteed to be unique for each and every student entry row in the database table. This attribute column is called as primary key in a database. We can also consider phone number column in the students table to identify one particular student uniquely, or else we can consider email address as well. The primary key should never be NULL. For example, if a particular student does not provide email address or phone number, we cannot use either of them as our primary key. This artificial key column created by us can then be used as a primary key. Mostly this artificial column will contain numbers that will auto increment itself for each entry row added to the table. For example, the first row will have an entry of 1 in the artificial column that we created, and the second row will have 2, and the third will have 3 and so on. Hence we can be hundred percent sure that it will be unique and non-empty. There are 4 columns in our example. The values in that column are automatically created for each individual students that are part of the table. As the entries are auto-incremented for each entry, it will be hundred percent unique for each student. Let's achieve the same example in a mysql database. Am going to now connect to a test MySQL server, and run several commands, to achieve the same result we saw above. Now let's create a students table inside that database. Let's add another column to the same example let's add a column to the same table that stores the phone number of the student. As we already discussed, primary key should be able to uniquely identify one particular record row in the table. Its perfectly possible that two students can have the same first name, two students can have the same last name, two students can also have the same phone number well if both of them are from the same house. But its less likely that two students will have the same phone number and the same firstname. So the firstname and the phone number, combined together will uniquely identify a particular student for sure. So in this case we can construct a primary key without using a surrogate key the artificial auto incremented column that we added previously. Its also called as composite key. Running the below query should throw a primary key error. You can actually add a primary key on an existing table using ALTER TABLE statement in MySQL as shown below. You will have to replace this with corresponding tablename and columns in your case. The students table is shown below. This table will have the list of courses that the university is offering to students. This table will contain student enrollment details. Enrollment table will be making links to students table, as well as courses table. This is because an enrollment is nothing but the combination of students and courses table. A student opts for a particular course in the university, and this creates an entry in the enrollment table. Let's now add few entries inside our enrollment table as shown below. Adding an entry inside enrollment table, with values that are not present in primary key of students and courses table will create an error as shown below. Its not present in the students table see the students table shown previously.. It only has 4 entries for 4 students. Hence only 4 valid students are available for us to use in enrollment table. Our enrollment table with all valid entries we created looks like the below. Foreign keys can be used to protect the integrity of your table. It will also keep the links between tables in perfect and valid state.
SalesPerson table, and the salesperson's ID is used for sales orders in the Sales. The relationships in a RDBMS ensure that there is no redundant data. In SQL Server, there are two keys - primary key and foreign key which seems identical, but actually both are different in features and behaviours. The process of removing redundant data by creating relations between tables is known as Normalization. Foreign key in a table enforcewhich can be used to implement business rules e. As the entries are auto-incremented for each entry, it will be hundred percent unique for each student. The constraint here is that all customers must be associated with an agent that is already in the 'agents' table. This kind of check maintains integrity of data in a relationship. If you are preparing for any technical job interview where you expect some SQL questions, check out these questions, they are worth preparing. Also, it is OK for them contain NULL values.
