HomeОбразованиеRelated VideosMore From: kudvenkat

Part 12 Can we join two tables without primary foreign key relation

256 ratings | 120293 views
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/09/part-12-can-we-join-two-tables-without.html Can we join two tables without primary foreign key relation Yes, we can join two tables without primary foreign key relation as long as the column values involved in the join can be converted to one type. ID column in Departments table is not the primary Key and DepartmentId column in Employees table is not the foreign key. But we can still join these tables using ID column from Departments table and DepartmentId column from Employees table, as both the columns involved in the join have same data type i.e int. Select Employees.Name as EmployeeName, Departments.Name as DepartmentName from Employees join Departments on Departments.ID = Employees.DepartmentId The obious next question is, if primary foreign key relation is not mandatory for 2 tables to be joined then what is the use of these keys? Primary key enforces uniqueness of values over one or more columns. Since ID is not a primary key in Departments table, 2 or more departments may end up having same ID value, which makes it impossible to distinguish between them based on the ID column value. Foreign key enforces referential integrity. Without foreign key constraint on DepartmentId column in Employees table, it is possible to insert a row into Employees table with a value for DepartmentId column that does not exist in Departments table. The following insert statement, successfully inserts a new Employee into Employees table whose DepartmentId is 100. But we don't have a department with ID = 100 in Departments table. This means this employee row is an orphan row, and the referential integrity is lost as result Insert into Employees values (8, 'Mary', 'Female', 80000, 100) If we have had a foreign key constraint on DepartmentId column in Employees table, the following insert statement would have failed with the following error. Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint. The conflict occurred in database "Sample", table "dbo.Departments", column 'ID'.
Html code for embedding videos on your blog
Text Comments (18)
Mahesh S (1 month ago)
This video lead me to subscribe your channel :)
uma Madurantakam (3 months ago)
Great explanation sir, thank you 👏
Akhila Vedakrishna (3 months ago)
nice///eassy //thanks sir
Ishan. Kulshrestha. (6 months ago)
Hi Venkat, I have a request to you. Can you make an console application in which MVC pattern, List and LIst childern are used and getdata and showdata are used recursively. Please Venkat I am facing some problem in this. Please help me.
sivaprasad reddy (7 months ago)
hai sir,How I can drop a primary key without a constraint name
sapna Kalra (1 year ago)
I need script of your examples, pls tell me how can i get that
dz lee (2 years ago)
pure gold. thank you for sharing with the world
Ian Steeler (2 years ago)
is there a way we could find what two columns can be joined from two different table ? i am sure its way to figure out if its few column like you explained but in real scenario we usually have 100 or more column in a table.
Arun Balaji (2 years ago)
Hi i have a question, For Transpose if it is city 4 u can manually edit and add in the query and make it effective. But what if there goes n number of rows that needs to be PIVOTED ?(for ex : 25 rows needs to be Transposed). does the user needs to enter the row name each time to get the output result as PIVOT ?
Lih Lih Ng (3 years ago)
Hi, I have question here. If there is no common attribute/column in 2 tables, can I join them and get the result like below ? Thank you :) Example : Table 1 ID | Department Table 2 Salary | Title Result Department | Title
Vishnu Menon (3 years ago)
Very beautifully explained.. Thank you...!!
Sebastian Cheung (3 years ago)
But is it also true for SQLite as well?
Kris Maly (3 years ago)
Revisited and enjoyed
krismaly (4 years ago)
Good Question and answer
Andrusha (4 years ago)
Hi Venkat. I went to the few interviews and on couple of them i was asked this question Let’s pretend I have a table with this structure.   Create  table order_detail ( Order_id              int not null , product_id       int  not null ,customer_id     int not null ,purchase_date                datetime not null , amount               money not null )   It has data for one year, let’s pretend that it is 2014 through January 1st to December 31st I need to delete few millions of records for customer_id   = 42 for period of time through May 1st and June 30th I was thinking about  to use delete as Delete from table order_detail Where customer_id   = 42 and purchase_date=> 05012014 and purchase_date=< 06302014   However this statement is not good for few millions of records. I was wondering if you know any other way to delete this records which will not going to slow down server and run faster than delete 
Esayas Bemnet (3 years ago)
--Deleting a big data using a while loop like this avoid from transaction log increasing in size quickly. WHILE 1 = 1       BEGIN DELETE TOP (1000) FROM order_detail WHERE customer_id   = 42 AND purchase_date=> 05012014 and purchase_date=< 06302014; IF @@rowcount < 1000 BREAK; END
Somesh Naganaboina (4 years ago)
Hi, Thanks for YouTube Videos. They are most useful. I have faced an interview question as below. Can you please explain it. Support we have a Stored procedure but the performance of the SP is not good. what are the all the changes u will do to improve performance. 1) Add indexes when ever required. 2) remove if there are too many indexes 3) with select use no lock 4) replace sub queries with joins  etc Could you please explain if there are any other Thanks.
Asma'a Naeem (4 years ago)
thanks alot for your great video , i want to  ask about compose primary key? can table has more than one primary key?

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.