title
Creating and working with tables - Part 3
description
In this video we will learn
1. Creating tables
2. Enforcing primary key and foreign key constraints
Text version of the video
http://csharp-video-tutorials.blogspot.co.uk/2012/08/creating-and-working-with-tables-part-3.html
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1
Slides
http://csharp-video-tutorials.blogspot.com/2013/08/part-3-creating-and-working-with-tables.html
All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html
All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.html
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd
All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatArabic/playlists
detail
{'title': 'Creating and working with tables - Part 3', 'heatmap': [{'end': 245.196, 'start': 228.981, 'weight': 0.761}, {'end': 822.543, 'start': 760.465, 'weight': 0.763}], 'summary': 'In this video tutorial series on sql server, the chapters cover creating tables like tblperson and tblgender, enforcing primary key constraints, executing sql queries, maintaining data integrity with foreign key relationships, and implementing foreign key constraints to ensure database integrity.', 'chapters': [{'end': 73.384, 'segs': [{'end': 73.384, 'src': 'embed', 'start': 0.009, 'weight': 0, 'content': [{'end': 1.73, 'text': 'Hello, welcome to Presume Technologies.', 'start': 0.009, 'duration': 1.721}, {'end': 2.511, 'text': 'I am Venkat.', 'start': 1.931, 'duration': 0.58}, {'end': 4.693, 'text': 'This is part 3 of SQL Server.', 'start': 2.771, 'duration': 1.922}, {'end': 12.74, 'text': 'In part 1 of this video series, we have seen how to connect to SQL Server using SQL Server Management Studio, and in part 2,', 'start': 5.594, 'duration': 7.146}, {'end': 15.782, 'text': 'we have seen how to create, alter and drop databases.', 'start': 12.74, 'duration': 3.042}, {'end': 22.428, 'text': "In this session, we'll learn how to create tables and enforce primary and foreign key constraints between those tables.", 'start': 16.323, 'duration': 6.105}, {'end': 29.585, 'text': 'The aim of this session is basically to create these two tables, tblPerson and tblGender.', 'start': 23.539, 'duration': 6.046}, {'end': 35.43, 'text': 'Now, within this tblPerson table, I want to mark this ID column as the primary key column,', 'start': 29.985, 'duration': 5.445}, {'end': 39.434, 'text': 'and in tblGender I want this column to be marked as a primary key column.', 'start': 35.43, 'duration': 4.004}, {'end': 47.341, 'text': 'Now, what is the use of primary key? A primary key basically is used to identify uniquely each record in that table.', 'start': 39.834, 'duration': 7.507}, {'end': 56.588, 'text': 'For example, In this tblPerson table, if I have two Marys, how do I uniquely identify each one of them using this primary key column?', 'start': 47.701, 'duration': 8.887}, {'end': 61.492, 'text': 'So if there is another record for Mary, probably she will have an ID of 7..', 'start': 56.888, 'duration': 4.604}, {'end': 67.458, 'text': 'So 2 is for this Mary, 7 is for another Mary that we are going to have.', 'start': 61.492, 'duration': 5.966}, {'end': 73.384, 'text': 'So primary key is basically used to uniquely identify each record within that table.', 'start': 68.379, 'duration': 5.005}], 'summary': 'Sql server part 3 focuses on creating tables and enforcing primary and foreign key constraints, with examples of tblperson and tblgender.', 'duration': 73.375, 'max_score': 0.009, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE9.jpg'}], 'start': 0.009, 'title': 'Sql server part 3: tables and key constraints', 'summary': 'Covers the creation of two tables, tblperson and tblgender, and the enforcement of primary key constraints to uniquely identify each record within the tables.', 'chapters': [{'end': 73.384, 'start': 0.009, 'title': 'Sql server part 3: tables and key constraints', 'summary': 'Covers creating two tables, tblperson and tblgender, and enforcing primary key constraints to uniquely identify each record within the tables.', 'duration': 73.375, 'highlights': ['The aim of this session is to create tblPerson and tblGender tables and enforce primary key constraints for uniquely identifying each record.', 'In part 1, the video series covered connecting to SQL Server using SQL Server Management Studio, and part 2 covered creating, altering, and dropping databases.', "Primary keys are used to uniquely identify each record in a table, ensuring unique identification of records like two 'Marys' through distinct primary key values."]}], 'duration': 73.375, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE9.jpg', 'highlights': ["Primary keys ensure unique identification of records like two 'Marys' through distinct primary key values.", 'Part 2 covered creating, altering, and dropping databases.', 'The aim is to create tblPerson and tblGender tables and enforce primary key constraints for uniquely identifying each record.']}, {'end': 376.155, 'segs': [{'end': 121.967, 'src': 'embed', 'start': 75.18, 'weight': 0, 'content': [{'end': 78.542, 'text': 'Alright, to create a table in SQL Server, we have got two options.', 'start': 75.18, 'duration': 3.362}, {'end': 84.006, 'text': 'One is graphically using SQL Server Management Studio and the other one is basically to write a query.', 'start': 78.823, 'duration': 5.183}, {'end': 89.59, 'text': 'Now, first we will see how to create a table graphically using SQL Server Management Studio.', 'start': 84.307, 'duration': 5.283}, {'end': 90.811, 'text': "So, let's flip back.", 'start': 89.931, 'duration': 0.88}, {'end': 97.976, 'text': 'So now, within the databases in the previous session, we have seen how to create a sample database.', 'start': 93.513, 'duration': 4.463}, {'end': 104.494, 'text': "So if I expand the sample database, You see there's a folder called Tables folder, and if I expand that,", 'start': 98.276, 'duration': 6.218}, {'end': 107.216, 'text': "we don't have any tables yet because we haven't created any.", 'start': 104.494, 'duration': 2.722}, {'end': 112.099, 'text': 'Now if I want to create a new table, I can right-click this folder and say New Table.', 'start': 107.576, 'duration': 4.523}, {'end': 120.265, 'text': 'And we want to create a TBL Person table with these columns, ID, Name, Email and Gender ID.', 'start': 113.08, 'duration': 7.185}, {'end': 121.967, 'text': "So let's create those.", 'start': 120.966, 'duration': 1.001}], 'summary': 'Demonstrates creating a table in sql server using management studio with step-by-step instructions and specifying columns.', 'duration': 46.787, 'max_score': 75.18, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE75180.jpg'}, {'end': 176.97, 'src': 'embed', 'start': 145.189, 'weight': 4, 'content': [{'end': 146.85, 'text': "So, I don't want to allow nulls.", 'start': 145.189, 'duration': 1.661}, {'end': 148.33, 'text': 'So, remove the checkbox.', 'start': 146.95, 'duration': 1.38}, {'end': 152.511, 'text': 'And I want this column to be the primary key column of this table.', 'start': 148.95, 'duration': 3.561}, {'end': 156.953, 'text': 'And how do I do that? Right-click on that column and select Set Primary Key.', 'start': 152.551, 'duration': 4.402}, {'end': 161.48, 'text': 'Okay? What have we done? We have created this ID column.', 'start': 157.573, 'duration': 3.907}, {'end': 163.222, 'text': 'We have specified the data type.', 'start': 161.741, 'duration': 1.481}, {'end': 165.683, 'text': "And we said it's not going to allow nulls.", 'start': 163.582, 'duration': 2.101}, {'end': 168.545, 'text': 'And we marked this column as a primary key column.', 'start': 166.043, 'duration': 2.502}, {'end': 173.468, 'text': "And what's the next column in our table? Name column.", 'start': 169.145, 'duration': 4.323}, {'end': 176.97, 'text': 'And the data type of this one is going to be nvacar.', 'start': 173.488, 'duration': 3.482}], 'summary': "Column 'id' set as primary key and disallowed nulls.", 'duration': 31.781, 'max_score': 145.189, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE145189.jpg'}, {'end': 260.543, 'src': 'heatmap', 'start': 228.981, 'weight': 2, 'content': [{'end': 230.122, 'text': "we're done now right.", 'start': 228.981, 'duration': 1.141}, {'end': 239.331, 'text': 'click here and say save table 1, and I want to call this table as tblPersonTable.', 'start': 230.122, 'duration': 9.209}, {'end': 241.292, 'text': 'so, basically what we have done now?', 'start': 239.331, 'duration': 1.961}, {'end': 245.196, 'text': 'we have used the designer and created this tblPersonTable.', 'start': 241.292, 'duration': 3.904}, {'end': 245.956, 'text': 'so you look at this.', 'start': 245.196, 'duration': 0.76}, {'end': 254.92, 'text': 'this is the tblPersonTable and if you expand that And if you expand the columns folder, you should see all the columns ID, name, email, gender ID.', 'start': 245.956, 'duration': 8.964}, {'end': 260.543, 'text': 'And if you look at this, ID column has a key symbol next to that indicating that this is the primary key.', 'start': 255.22, 'duration': 5.323}], 'summary': 'Created tblpersontable with columns id, name, email, gender id; id is primary key.', 'duration': 31.562, 'max_score': 228.981, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE228981.jpg'}, {'end': 319.702, 'src': 'embed', 'start': 292.574, 'weight': 3, 'content': [{'end': 297.195, 'text': 'Now, we want to create a table, so we will say create table and the name of the table.', 'start': 292.574, 'duration': 4.621}, {'end': 303.117, 'text': "So, create table and what's the table that I want to create? TBL gender.", 'start': 297.635, 'duration': 5.482}, {'end': 311.538, 'text': 'Now, this table is going to have how many columns? Two columns, id and gender columns.', 'start': 304.795, 'duration': 6.743}, {'end': 319.702, 'text': "And we want the id to be integer column and we don't want to allow nulls and we want this column to be the primary key column of this table.", 'start': 311.918, 'duration': 7.784}], 'summary': 'Creating a table tbl gender with 2 columns: id and gender, setting id as integer primary key', 'duration': 27.128, 'max_score': 292.574, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE292574.jpg'}], 'start': 75.18, 'title': 'Sql server table creation and design', 'summary': 'Covers creating tables in sql server using sql server management studio, including creating a tbl person table with columns id, name, email, and gender id, and also covers defining columns with data types and constraints such as primary keys.', 'chapters': [{'end': 121.967, 'start': 75.18, 'title': 'Creating tables in sql server', 'summary': 'Demonstrates how to create a table in sql server using sql server management studio, including creating a tbl person table with columns id, name, email, and gender id.', 'duration': 46.787, 'highlights': ['Creating a table in SQL Server can be done graphically using SQL Server Management Studio or by writing a query.', 'Creating a TBL Person table with columns ID, Name, Email, and Gender ID using SQL Server Management Studio.', 'Expanding the sample database in SQL Server Management Studio to create a new table.']}, {'end': 376.155, 'start': 122.227, 'title': 'Designing tables and creating primary keys', 'summary': 'Covers creating tables, defining columns with data types and constraints such as primary keys, and demonstrates the use of table designer vendor and sql queries to create tblpersontable and tbl gender table.', 'duration': 253.928, 'highlights': ['Using Table Designer Vendor to create tblPersonTable The speaker demonstrates using the Table Designer Vendor to create the tblPersonTable, specifying columns like ID, Name, Email, and Gender ID with their respective data types, constraints, and allowing nulls as needed.', 'Creating TBL gender table using SQL query The chapter explains the process of creating the TBL gender table through a SQL query, defining columns like ID and Gender with their respective data types, constraints, and specifying the primary key.', 'Importance of primary keys and null constraints The importance of primary keys for uniquely identifying rows within a table and the significance of null constraints for ensuring data integrity is emphasized throughout the demonstration.']}], 'duration': 300.975, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE75180.jpg', 'highlights': ['Creating a TBL Person table with columns ID, Name, Email, and Gender ID using SQL Server Management Studio.', 'Creating a table in SQL Server can be done graphically using SQL Server Management Studio or by writing a query.', 'Using Table Designer Vendor to create tblPersonTable, specifying columns like ID, Name, Email, and Gender ID with their respective data types, constraints, and allowing nulls as needed.', 'Creating TBL gender table using SQL query, defining columns like ID and Gender with their respective data types, constraints, and specifying the primary key.', 'Importance of primary keys for uniquely identifying rows within a table and the significance of null constraints for ensuring data integrity is emphasized throughout the demonstration.', 'Expanding the sample database in SQL Server Management Studio to create a new table.']}, {'end': 536.212, 'segs': [{'end': 406.058, 'src': 'embed', 'start': 376.515, 'weight': 2, 'content': [{'end': 380.016, 'text': "Now, interestingly, you don't see the tbl gender table.", 'start': 376.515, 'duration': 3.501}, {'end': 385.079, 'text': 'Why? Because if you look at the database context here, you have created that table.', 'start': 380.397, 'duration': 4.682}, {'end': 387.18, 'text': 'I mean, you have selected here the master table.', 'start': 385.139, 'duration': 2.041}, {'end': 393.083, 'text': 'So, when you executed this query, it got executed in the context of the master database.', 'start': 387.48, 'duration': 5.603}, {'end': 395.824, 'text': "And what's a master database? It's the system database.", 'start': 393.363, 'duration': 2.461}, {'end': 400.535, 'text': 'So if you come here, expand this, you should see tblGender here.', 'start': 396.693, 'duration': 3.842}, {'end': 401.956, 'text': "But that's not our intention.", 'start': 400.575, 'duration': 1.381}, {'end': 406.058, 'text': 'We want this table to be created in sample database.', 'start': 401.976, 'duration': 4.082}], 'summary': 'The tblgender table is not visible as it was created in the master database instead of the intended sample database.', 'duration': 29.543, 'max_score': 376.515, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE376515.jpg'}, {'end': 471.779, 'src': 'embed', 'start': 448.209, 'weight': 1, 'content': [{'end': 456.531, 'text': 'so if you want to specify that as the name of the database, put it in a square brackets like this and then click go.', 'start': 448.209, 'duration': 8.322}, {'end': 458.892, 'text': 'so we are saying use the sample database.', 'start': 456.531, 'duration': 2.361}, {'end': 459.893, 'text': 'go now.', 'start': 458.892, 'duration': 1.001}, {'end': 465.956, 'text': "for example, if I'm in the master database, if I execute just these two statements, what's going to happen?", 'start': 459.893, 'duration': 6.063}, {'end': 466.717, 'text': 'look at this.', 'start': 465.956, 'duration': 0.761}, {'end': 468.858, 'text': 'the database is automatically changed.', 'start': 466.717, 'duration': 2.141}, {'end': 471.779, 'text': "and now, if I execute this, what's going to happen?", 'start': 468.858, 'duration': 2.921}], 'summary': 'Demonstrating database name specification in sql commands.', 'duration': 23.57, 'max_score': 448.209, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE448209.jpg'}, {'end': 516.998, 'src': 'embed', 'start': 492.744, 'weight': 0, 'content': [{'end': 501.152, 'text': 'So, what have we done until now? Until now, we have created these two tables and marked these two columns as primary keys for these two tables.', 'start': 492.744, 'duration': 8.408}, {'end': 511.494, 'text': 'What else we want to do? We know that this gender ID column, you know, we want to mark this gender ID column as the foreign key in this table.', 'start': 502.847, 'duration': 8.647}, {'end': 516.998, 'text': 'So, how do we do that? Again, you can do that graphically or you can do that using a query.', 'start': 511.734, 'duration': 5.264}], 'summary': 'Created two tables with primary keys, planning to mark gender id column as foreign key.', 'duration': 24.254, 'max_score': 492.744, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE492744.jpg'}], 'start': 376.515, 'title': 'Sql queries execution', 'summary': 'Focuses on executing sql queries to create tables, emphasizing the importance of correct database context and demonstrates creating and using foreign key relationships.', 'chapters': [{'end': 536.212, 'start': 376.515, 'title': 'Creating and executing queries in sql', 'summary': 'Explains how to execute sql queries to create tables, emphasizing the importance of executing queries in the correct database context and demonstrates creating and using foreign key relationships.', 'duration': 159.697, 'highlights': ['The importance of executing queries in the correct database context is emphasized, with an example demonstrating how the table got created in the master database instead of the intended sample database. Example of a table getting created in the wrong database context.', "Demonstration of using 'use' statement to specify the database context for query execution, ensuring the table is created in the intended database. Use of 'use' statement to specify the database context for query execution.", 'Explanation and demonstration of marking columns as primary keys and creating foreign key relationships in tables, providing a practical understanding of their significance. Practical demonstration of marking columns as primary keys and creating foreign key relationships.']}], 'duration': 159.697, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE376515.jpg', 'highlights': ['Practical demonstration of marking columns as primary keys and creating foreign key relationships.', "Demonstration of using 'use' statement to specify the database context for query execution.", 'The importance of executing queries in the correct database context is emphasized, with an example demonstrating how the table got created in the master database instead of the intended sample database.']}, {'end': 675.034, 'segs': [{'end': 675.034, 'src': 'embed', 'start': 601.843, 'weight': 0, 'content': [{'end': 602.583, 'text': 'Now look at this.', 'start': 601.843, 'duration': 0.74}, {'end': 606.126, 'text': 'That value is accepted by the SQL Server.', 'start': 602.863, 'duration': 3.263}, {'end': 609.555, 'text': "Now, if somebody asks, can you tell me what is Mary's gender??", 'start': 606.471, 'duration': 3.084}, {'end': 610.636, 'text': 'Now, you are looking.', 'start': 609.815, 'duration': 0.821}, {'end': 615.863, 'text': "Mary's gender ID is 99 and you go to the genders table and you don't have a 99 here.", 'start': 610.636, 'duration': 5.227}, {'end': 618.767, 'text': "So what is Mary's gender? You don't know.", 'start': 616.303, 'duration': 2.464}, {'end': 623.893, 'text': 'So basically here the data that you have in the person table is not correct.', 'start': 620.048, 'duration': 3.845}, {'end': 630.058, 'text': 'Somebody maybe by mistake they have entered this 99 and SQL Server accepted that value.', 'start': 624.857, 'duration': 5.201}, {'end': 634.479, 'text': 'So if you look at this data, the database integrity is lost.', 'start': 630.438, 'duration': 4.041}, {'end': 635.919, 'text': 'Your data is not intact.', 'start': 634.539, 'duration': 1.38}, {'end': 638.04, 'text': 'There is something wrong with this data.', 'start': 636.579, 'duration': 1.461}, {'end': 646.141, 'text': 'But, on the other hand, if we have established the relationship between these two tables, the foreign key relationship,', 'start': 639.62, 'duration': 6.521}, {'end': 648.722, 'text': "it wouldn't have allowed you to enter 99 here.", 'start': 646.141, 'duration': 2.581}, {'end': 653.131, 'text': 'But on the other hand, it would have allowed null.', 'start': 650.93, 'duration': 2.201}, {'end': 659.072, 'text': "So if you enter null, what is Mary's gender? It's null, which means we don't know.", 'start': 653.971, 'duration': 5.101}, {'end': 661.373, 'text': "They haven't provided their gender information.", 'start': 659.192, 'duration': 2.181}, {'end': 667.275, 'text': "But if somebody is able to enter maybe 99 or 101 like this, this data doesn't make any sense.", 'start': 661.913, 'duration': 5.362}, {'end': 671.856, 'text': 'Why? Because gender ID 101 does not exist in the genders table.', 'start': 667.335, 'duration': 4.521}, {'end': 675.034, 'text': "and we don't know what is Mary's gender.", 'start': 672.913, 'duration': 2.121}], 'summary': 'Data integrity lost due to incorrect values in database, impacting gender identification.', 'duration': 73.191, 'max_score': 601.843, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE601843.jpg'}], 'start': 536.212, 'title': 'Data integrity and foreign key relationship', 'summary': 'Discusses the significance of maintaining data integrity through foreign key relationships, emphasizing the ramifications of incorrect data entry and the advantages of enforcing referential integrity.', 'chapters': [{'end': 675.034, 'start': 536.212, 'title': 'Data integrity and foreign key relationship', 'summary': 'Discusses the importance of maintaining data integrity through foreign key relationships, highlighting the consequences of incorrect data entry and the benefits of enforcing referential integrity.', 'duration': 138.822, 'highlights': ['Enforcing foreign key relationship prevents incorrect data entry, maintaining data integrity and avoiding the acceptance of invalid values like gender ID 99 or 101.', 'Establishing a foreign key relationship would prevent the acceptance of invalid gender IDs, ensuring data accuracy and integrity.', 'Entering incorrect data like gender ID 99 leads to database integrity issues and inaccurate information, compromising the reliability of the data in the person table.', 'Foreign key relationship allows the acceptance of null values, indicating the absence of gender information, maintaining data accuracy and integrity.', 'SQL Server accepted the incorrect gender ID 99, highlighting the risk of data inconsistency and loss of integrity.']}], 'duration': 138.822, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE536212.jpg', 'highlights': ['Enforcing foreign key relationship prevents incorrect data entry, maintaining data integrity and avoiding the acceptance of invalid values like gender ID 99 or 101.', 'Establishing a foreign key relationship would prevent the acceptance of invalid gender IDs, ensuring data accuracy and integrity.', 'Entering incorrect data like gender ID 99 leads to database integrity issues and inaccurate information, compromising the reliability of the data in the person table.', 'Foreign key relationship allows the acceptance of null values, indicating the absence of gender information, maintaining data accuracy and integrity.', 'SQL Server accepted the incorrect gender ID 99, highlighting the risk of data inconsistency and loss of integrity.']}, {'end': 804.307, 'segs': [{'end': 705.002, 'src': 'embed', 'start': 675.514, 'weight': 0, 'content': [{'end': 677.214, 'text': 'So the database integrity is lost.', 'start': 675.514, 'duration': 1.7}, {'end': 683.477, 'text': 'So how do we enforce the database integrity using this foreign key relationship, foreign key constraints?', 'start': 677.455, 'duration': 6.022}, {'end': 685.997, 'text': 'So let us see how to add a foreign key constraint.', 'start': 683.817, 'duration': 2.18}, {'end': 690.599, 'text': "So now let's enter this as null for the time being.", 'start': 687.678, 'duration': 2.921}, {'end': 694.74, 'text': "Let's close this table.", 'start': 693.46, 'duration': 1.28}, {'end': 697.701, 'text': "Let's close this table as well.", 'start': 696.221, 'duration': 1.48}, {'end': 705.002, 'text': 'Okay, now what do we want to do? In tblPerson table, we want to mark this column as a foreign key constraint.', 'start': 698.281, 'duration': 6.721}], 'summary': 'Enforcing foreign key constraints for database integrity in tblperson table.', 'duration': 29.488, 'max_score': 675.514, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE675514.jpg'}, {'end': 781.348, 'src': 'embed', 'start': 727.716, 'weight': 1, 'content': [{'end': 732.385, 'text': 'So primary key table is TBL gender and foreign key table is TBL person.', 'start': 727.716, 'duration': 4.669}, {'end': 741.011, 'text': 'And in tblPerson, genderId column looks up its values in tblGender table in this id column.', 'start': 733.126, 'duration': 7.885}, {'end': 745.494, 'text': "For example, if I want Martin's gender, Martin's genderId is 1.", 'start': 741.992, 'duration': 3.502}, {'end': 750.058, 'text': 'So you take this value, go into tblGender, look in this column, 1 is male.', 'start': 745.494, 'duration': 4.564}, {'end': 753.94, 'text': 'So this is your primary key table, tblGender.', 'start': 751.479, 'duration': 2.461}, {'end': 756.222, 'text': 'And your foreign key table is tblPerson.', 'start': 754.2, 'duration': 2.022}, {'end': 760.124, 'text': "Now let's flip back to SQL Server Management Studio.", 'start': 757.583, 'duration': 2.541}, {'end': 767.244, 'text': 'So in the tblPerson table, I want this column Gender ID to be marked as a Foreign Key.', 'start': 760.465, 'duration': 6.779}, {'end': 771.485, 'text': 'How do I do that? Right click on the table and select Design.', 'start': 767.624, 'duration': 3.861}, {'end': 777.607, 'text': 'And within the Designer window of this table, this is the Gender ID column that we want to mark as a Foreign Key.', 'start': 772.405, 'duration': 5.202}, {'end': 781.348, 'text': 'So right click on that and select Relationships.', 'start': 778.047, 'duration': 3.301}], 'summary': "Tblperson's gender id is linked to tblgender's id column as a foreign key.", 'duration': 53.632, 'max_score': 727.716, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE727716.jpg'}], 'start': 675.514, 'title': 'Enforcing database integrity with foreign key constraints', 'summary': 'Explains how to enforce database integrity using foreign key constraints, defining the relationship between the primary key table (tbl gender) and foreign key table (tbl person) in sql server management studio.', 'chapters': [{'end': 804.307, 'start': 675.514, 'title': 'Enforcing database integrity with foreign key constraints', 'summary': 'Explains how to enforce database integrity using foreign key constraints, defining the relationship between the primary key table (tbl gender) and foreign key table (tbl person) in sql server management studio.', 'duration': 128.793, 'highlights': ['The primary key table is TBL gender and the foreign key table is TBL person, with the genderId column in TBL person looking up its values in the id column of TBL gender, creating a foreign key relationship.', 'In SQL Server Management Studio, the process of marking the Gender ID column in the tblPerson table as a Foreign Key involves right-clicking on the table, selecting Design, choosing Relationships, and specifying the primary key and foreign key tables.', 'Enforcing database integrity using foreign key constraints ensures data consistency and accuracy, preventing the loss of database integrity, and facilitating the secure linkage between related tables.']}], 'duration': 128.793, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE675514.jpg', 'highlights': ['Enforcing database integrity using foreign key constraints ensures data consistency and accuracy, preventing the loss of database integrity, and facilitating the secure linkage between related tables.', 'The primary key table is TBL gender and the foreign key table is TBL person, with the genderId column in TBL person looking up its values in the id column of TBL gender, creating a foreign key relationship.', 'In SQL Server Management Studio, the process of marking the Gender ID column in the tblPerson table as a Foreign Key involves right-clicking on the table, selecting Design, choosing Relationships, and specifying the primary key and foreign key tables.']}, {'end': 1059.761, 'segs': [{'end': 864.289, 'src': 'embed', 'start': 837.927, 'weight': 3, 'content': [{'end': 845.449, 'text': 'If I want to create a foreign key constraint using a query rather than the designer, how do we do that? There is a general formula for that.', 'start': 837.927, 'duration': 7.522}, {'end': 847.149, 'text': 'So alter table.', 'start': 845.869, 'duration': 1.28}, {'end': 849.779, 'text': 'and foreign key table.', 'start': 848.058, 'duration': 1.721}, {'end': 853.001, 'text': 'look at this alter table.', 'start': 849.779, 'duration': 3.222}, {'end': 854.682, 'text': 'which table we want to alter?', 'start': 853.001, 'duration': 1.681}, {'end': 856.063, 'text': 'tblPerson table.', 'start': 854.682, 'duration': 1.381}, {'end': 857.564, 'text': "so let's try that.", 'start': 856.063, 'duration': 1.501}, {'end': 862.788, 'text': 'so alter table tblPerson.', 'start': 857.564, 'duration': 5.224}, {'end': 864.289, 'text': "that's the table we want to alter.", 'start': 862.788, 'duration': 1.501}], 'summary': 'Creating foreign key constraint using query for tblperson table.', 'duration': 26.362, 'max_score': 837.927, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE837927.jpg'}, {'end': 1001.055, 'src': 'embed', 'start': 910.331, 'weight': 0, 'content': [{'end': 915.472, 'text': 'you know which table, which other table is it going to reference and which column in that other table?', 'start': 910.331, 'duration': 5.141}, {'end': 929.795, 'text': 'So if you look back, so foreign key, so foreign key and we need to specify, you know, which column in TBL person table is going to be the foreign key.', 'start': 916.072, 'duration': 13.723}, {'end': 932.796, 'text': 'In this TBL person table, gender ID.', 'start': 930.115, 'duration': 2.681}, {'end': 938.787, 'text': 'so we specify gender id is the column that we want to mark as a foreign key.', 'start': 932.796, 'duration': 5.991}, {'end': 943.23, 'text': 'but then if this is a foreign key, then it has to be a primary key of some other table.', 'start': 938.787, 'duration': 4.443}, {'end': 947.592, 'text': 'so which is that other table in which we have to look up the primary key column?', 'start': 943.23, 'duration': 4.362}, {'end': 951.975, 'text': "so in our case it's going to be the tbl gender table.", 'start': 947.592, 'duration': 4.383}, {'end': 958.518, 'text': 'so this column is going to reference tbl gender table and id column.', 'start': 951.975, 'duration': 6.543}, {'end': 968.632, 'text': 'so references which table tbl gender and which column in that table id column.', 'start': 958.518, 'duration': 10.114}, {'end': 972.276, 'text': 'so if you look at that primary key column, alright.', 'start': 968.632, 'duration': 3.644}, {'end': 974.678, 'text': "so if I execute this query, what's gonna happen?", 'start': 972.276, 'duration': 2.402}, {'end': 980.743, 'text': "it's gonna create a foreign key constraint with this name.", 'start': 974.678, 'duration': 6.065}, {'end': 982.325, 'text': 'so execute that.', 'start': 980.743, 'duration': 1.582}, {'end': 984.346, 'text': 'so command completed successfully.', 'start': 982.325, 'duration': 2.021}, {'end': 995.753, 'text': 'now if I come to the tbl person table, refresh that and if I expand the tblPerson and if I go into the keys table,', 'start': 984.346, 'duration': 11.407}, {'end': 1000.135, 'text': 'look at that there is this tblPerson, genderId.', 'start': 995.753, 'duration': 4.382}, {'end': 1001.055, 'text': 'fk created.', 'start': 1000.135, 'duration': 0.92}], 'summary': "Creating a foreign key from tbl person table's gender id column to tbl gender table's id column, successfully executed.", 'duration': 90.724, 'max_score': 910.331, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE910331.jpg'}, {'end': 1040.488, 'src': 'embed', 'start': 1016.316, 'weight': 1, 'content': [{'end': 1023.719, 'text': 'you should see that the primary key table is tbl gender and the column is id and the foreign key table is tbl person gender id.', 'start': 1016.316, 'duration': 7.403}, {'end': 1027.741, 'text': 'So you can do that either graphically or using the designer.', 'start': 1023.779, 'duration': 3.962}, {'end': 1034.973, 'text': 'Now, do I have to remember this syntax of adding a foreign key constraint on top of my head?', 'start': 1028.839, 'duration': 6.134}, {'end': 1038.079, 'text': "I would say not really, but you'll have to understand the concept.", 'start': 1035.374, 'duration': 2.705}, {'end': 1039.262, 'text': "That's what is important.", 'start': 1038.118, 'duration': 1.144}, {'end': 1040.488, 'text': 'In your interview.', 'start': 1039.747, 'duration': 0.741}], 'summary': 'Understanding foreign key constraints is important in database design and development.', 'duration': 24.172, 'max_score': 1016.316, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE1016316.jpg'}], 'start': 804.307, 'title': 'Foreign key constraints in sql', 'summary': 'Discusses creating and altering foreign key constraints in sql, emphasizing the process of specifying referencing and referenced tables and columns, and underlining the significance of understanding the concept in database design and interviews.', 'chapters': [{'end': 910.331, 'start': 804.307, 'title': 'Creating foreign key constraint', 'summary': 'Explains how to create a foreign key constraint using a query rather than the designer, detailing the process of altering the table and adding a foreign key constraint on a specific column within the table.', 'duration': 106.024, 'highlights': ['Explaining the process of altering the table and adding a foreign key constraint on a specific column within the table. False', 'Demonstrating the general formula for creating a foreign key constraint using a query rather than the designer. False', 'Detailing the steps to add a constraint and give it a name, specifically creating a foreign key constraint on the tblPerson table for the gender ID column. False']}, {'end': 1016.316, 'start': 910.331, 'title': 'Creating foreign key in sql', 'summary': 'Discusses creating a foreign key in sql, specifying the referencing and referenced tables, as well as the columns involved, and executing the query to create the foreign key constraint successfully.', 'duration': 105.985, 'highlights': ['The speaker explains the process of creating a foreign key in SQL, including specifying the referencing and referenced tables, along with the columns involved.', 'The chapter also details the successful execution of the query to create the foreign key constraint, which is confirmed through a demonstration of the key creation in the database interface.', "The speaker emphasizes the importance of identifying the primary key column in the referenced table, highlighting the need to reference the 'id' column in the 'tbl gender' table."]}, {'end': 1059.761, 'start': 1016.316, 'title': 'Understanding foreign key constraints', 'summary': 'Explains the importance of understanding the concept of foreign key constraints in database design and mentions that syntax can be easily found by searching online. it also emphasizes that in an interview, explaining the concept and usage of foreign key constraints is more important than recalling the syntax.', 'duration': 43.445, 'highlights': ['Explaining the concept and usage of foreign key constraints is more important than recalling the syntax in an interview.', 'The syntax for adding a foreign key constraint can be easily found by searching online within two minutes.', 'Understanding the concept of foreign key constraints is important in database design and interview scenarios.']}], 'duration': 255.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE804307.jpg', 'highlights': ["The speaker emphasizes the importance of identifying the primary key column in the referenced table, highlighting the need to reference the 'id' column in the 'tbl gender' table.", 'Understanding the concept of foreign key constraints is important in database design and interview scenarios.', 'The chapter also details the successful execution of the query to create the foreign key constraint, which is confirmed through a demonstration of the key creation in the database interface.', 'Explaining the process of altering the table and adding a foreign key constraint on a specific column within the table.', 'The speaker explains the process of creating a foreign key in SQL, including specifying the referencing and referenced tables, along with the columns involved.']}, {'end': 1204.342, 'segs': [{'end': 1105.346, 'src': 'embed', 'start': 1060.001, 'weight': 2, 'content': [{'end': 1064.244, 'text': 'So, basically what we have done until now is we have created these two tables,', 'start': 1060.001, 'duration': 4.243}, {'end': 1070.872, 'text': 'marked the ID columns as primary keys for both the tables and we mark genderId in tblPerson as a foreign key column.', 'start': 1064.244, 'duration': 6.628}, {'end': 1080.027, 'text': 'Now since we have marked this column as a foreign key column, now this column only allow values if they exist in tblGender.', 'start': 1071.293, 'duration': 8.734}, {'end': 1083.177, 'text': "Otherwise, the value doesn't make sense.", 'start': 1080.628, 'duration': 2.549}, {'end': 1087.259, 'text': "For example, for Rob, if I enter 99, what is Rob's gender? It doesn't make sense.", 'start': 1083.197, 'duration': 4.062}, {'end': 1091.341, 'text': 'So it checks that value when you try to insert or update that.', 'start': 1088.279, 'duration': 3.062}, {'end': 1098.303, 'text': "And if that value does not exist in the primary key table, it's going to flag that as an error and that it doesn't allow you to enter.", 'start': 1091.641, 'duration': 6.662}, {'end': 1099.544, 'text': "Let's see that in action.", 'start': 1098.623, 'duration': 0.921}, {'end': 1102.425, 'text': "For example, let's go to this TBL gender table.", 'start': 1099.584, 'duration': 2.841}, {'end': 1105.346, 'text': "And let's say edit top 200 rows.", 'start': 1102.445, 'duration': 2.901}], 'summary': 'Creation of two tables with primary and foreign keys, ensuring data integrity and preventing invalid entries.', 'duration': 45.345, 'max_score': 1060.001, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE1060001.jpg'}, {'end': 1178.767, 'src': 'embed', 'start': 1129.63, 'weight': 0, 'content': [{'end': 1133.612, 'text': 'there is a foreign key constraint violation.', 'start': 1129.63, 'duration': 3.982}, {'end': 1138.155, 'text': 'okay, so because this 99 does not make sense.', 'start': 1133.612, 'duration': 4.543}, {'end': 1141.332, 'text': "okay, so Let's enter null, because why?", 'start': 1138.155, 'duration': 3.177}, {'end': 1143.914, 'text': 'In general, table 99 does not exist.', 'start': 1141.352, 'duration': 2.562}, {'end': 1148.758, 'text': 'So now your data is being verified because of this foreign key constraint.', 'start': 1144.355, 'duration': 4.403}, {'end': 1157.164, 'text': 'If somebody tries to enter invalid data accidentally or intentionally, SQL Server is not going to save that data and instead it throws an error.', 'start': 1148.778, 'duration': 8.386}, {'end': 1160.867, 'text': 'So now we are able to maintain database integrity.', 'start': 1157.625, 'duration': 3.242}, {'end': 1165.395, 'text': 'the data now is going to be a valid, so foreign key constraint.', 'start': 1161.411, 'duration': 3.984}, {'end': 1166.596, 'text': "so what's a foreign key constraint?", 'start': 1165.395, 'duration': 1.201}, {'end': 1171.14, 'text': 'basically, we are using foreign key constraint to enforce database integrity.', 'start': 1166.596, 'duration': 4.544}, {'end': 1178.767, 'text': "in layman's terms, a foreign key in one table points to a primary key in another table,", 'start': 1171.14, 'duration': 7.627}], 'summary': 'Foreign key constraint violation prevented invalid data, maintaining database integrity.', 'duration': 49.137, 'max_score': 1129.63, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE1129630.jpg'}], 'start': 1060.001, 'title': 'Implementing foreign key constraint', 'summary': 'Demonstrates the implementation of foreign key constraints to enforce database integrity, ensuring that only valid values are entered by preventing invalid data from being inserted or updated, ultimately maintaining database integrity.', 'chapters': [{'end': 1204.342, 'start': 1060.001, 'title': 'Implementing foreign key constraint', 'summary': 'Demonstrates the implementation of foreign key constraints to enforce database integrity, ensuring that only valid values are entered by preventing invalid data from being inserted or updated, ultimately maintaining database integrity.', 'duration': 144.341, 'highlights': ['The foreign key constraint prevents invalid data from being entered or updated, ensuring database integrity.', 'The genderId in tblPerson is marked as a foreign key column, allowing only values that exist in tblGender, preventing the entry of invalid data.', 'The system throws an error if an attempt is made to enter a value that does not exist in the primary key table, maintaining data validity.', 'The system verifies and prevents the entry of invalid data, ensuring that SQL Server does not save such data and instead throws an error.']}], 'duration': 144.341, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/JLeaM8pK8dE/pics/JLeaM8pK8dE1060001.jpg', 'highlights': ['The foreign key constraint prevents invalid data from being entered or updated, ensuring database integrity.', 'The system verifies and prevents the entry of invalid data, ensuring that SQL Server does not save such data and instead throws an error.', 'The genderId in tblPerson is marked as a foreign key column, allowing only values that exist in tblGender, preventing the entry of invalid data.', 'The system throws an error if an attempt is made to enter a value that does not exist in the primary key table, maintaining data validity.']}], 'highlights': ['Enforcing foreign key relationship prevents incorrect data entry, maintaining data integrity and avoiding the acceptance of invalid values like gender ID 99 or 101.', 'Enforcing database integrity using foreign key constraints ensures data consistency and accuracy, preventing the loss of database integrity, and facilitating the secure linkage between related tables.', 'The foreign key constraint prevents invalid data from being entered or updated, ensuring database integrity.', 'The system verifies and prevents the entry of invalid data, ensuring that SQL Server does not save such data and instead throws an error.', 'The genderId in tblPerson is marked as a foreign key column, allowing only values that exist in tblGender, preventing the entry of invalid data.']}