1 00:00.740 --> 00:00.950 Yeah. 2 00:00.950 --> 00:01.520 Hi, everyone. 3 00:01.520 --> 00:09.170 So we are seeing that, uh, on the last lecture, we have seen that we are going through the delete, 4 00:09.170 --> 00:12.590 truncate and drop statement in SQL. 5 00:13.700 --> 00:15.620 So let's move forward. 6 00:20.780 --> 00:26.810 So now in the delete and uh truncate we need to see two more differences. 7 00:26.810 --> 00:27.110 Right. 8 00:27.110 --> 00:29.540 Practically last time I told. 9 00:30.230 --> 00:33.050 So this one is the first one. 10 00:34.340 --> 00:41.870 Delete can be used on parent table if foreign key relationships are present on primary key, whereas 11 00:41.870 --> 00:48.380 truncate command cannot be used on parent table if foreign key relationships are present on primary 12 00:48.380 --> 00:48.590 key. 13 00:48.590 --> 00:50.720 So what is the meaning of that one? 14 00:52.900 --> 00:56.620 So I am creating this two table create table dipped. 15 00:58.690 --> 01:00.010 In some name is there. 16 01:00.010 --> 01:06.010 I am defining the primary key on this particular table and then insert. 17 01:06.430 --> 01:09.040 I am inserting the data into the same. 18 01:12.070 --> 01:16.330 And now I am creating one more table which is. 19 01:20.800 --> 01:21.790 Employee table. 20 01:21.790 --> 01:27.700 So let me create the table and I am giving the references of department table. 21 01:29.050 --> 01:31.360 And the column name is dept ID. 22 01:31.990 --> 01:35.170 So this is how we can give the reference of foreign key. 23 01:36.670 --> 01:42.000 Now let me insert the data also into the same. 24 01:48.270 --> 01:48.570 Okay. 25 01:48.570 --> 01:50.610 So now we have two tables right. 26 01:50.850 --> 01:53.730 Now we have two tables select star from. 27 02:06.720 --> 02:09.810 Select star from this table and select star from. 28 02:13.830 --> 02:21.540 So in the third table we have primary key and foreign key relationships are mentioned right in the department 29 02:21.540 --> 02:21.810 table. 30 02:21.810 --> 02:23.340 Department ID is primary key. 31 02:23.340 --> 02:28.050 And in the employee table Department ID is foreign key. 32 02:28.770 --> 02:32.900 Now let me try to delete the data from the department table. 33 02:35.090 --> 02:40.610 So where I am deleting delete from department table where dept id equal to ten. 34 02:42.230 --> 02:43.850 So let me try. 35 02:46.940 --> 02:50.000 So it is giving me some error over here. 36 02:50.960 --> 02:53.390 Let me copy paste this error. 37 02:54.770 --> 02:58.220 The delete statement conflicted with the reference constraint. 38 02:58.700 --> 03:00.020 This is a constraint name. 39 03:00.020 --> 03:02.750 The conflict occurred in the database. 40 03:02.750 --> 03:04.490 This database and table. 41 03:05.810 --> 03:07.340 This particular table. 42 03:07.340 --> 03:08.870 Column Department ID. 43 03:08.900 --> 03:10.700 The statement is terminated. 44 03:10.820 --> 03:15.980 So because of the reference of one table into the another table is available. 45 03:16.580 --> 03:21.670 We cannot delete data from the department table. 46 03:21.670 --> 03:22.360 Right. 47 03:22.870 --> 03:26.950 But now let me try to truncate this table. 48 03:30.550 --> 03:34.810 So what error it will give me at the time of truncate. 49 03:35.350 --> 03:39.190 So at the time of truncate also it is giving me some error right. 50 03:40.120 --> 03:46.600 Like the cannot truncate table because it is being referenced by a foreign key constraint, we cannot 51 03:46.600 --> 03:47.500 truncate it. 52 03:47.920 --> 03:50.530 But now let me show you one more thing. 53 03:51.520 --> 03:56.080 If I insert one more record into this particular table. 54 03:59.590 --> 04:07.860 Like 40 and department you can say mechanical department. 55 04:10.080 --> 04:12.960 I have added one more record right in into the table. 56 04:13.020 --> 04:17.730 So at this time, if I want to delete this particular record. 57 04:20.880 --> 04:27.210 Which is newly inserted, I am will be allowed to delete this particular record. 58 04:27.210 --> 04:35.490 I will be allowed to delete this particular record because the 14 number has no entry into this employee 59 04:35.490 --> 04:36.900 table, right? 60 04:37.260 --> 04:40.440 There is no record into this employee table with 40. 61 04:42.390 --> 04:44.580 So 40 is there in the department table. 62 04:44.580 --> 04:47.040 But in the employee table 40 is not there. 63 04:47.310 --> 04:50.370 So there is no reference as of now. 64 04:51.120 --> 04:55.070 So it should allow me to delete record number 40. 65 04:57.470 --> 04:59.210 Because of no reference. 66 04:59.210 --> 05:00.530 But still. 67 05:00.860 --> 05:08.270 But still I will be not allowed to truncate because of other three records have a reference, right? 68 05:08.270 --> 05:11.030 Because of other three record ever as a reference. 69 05:11.030 --> 05:14.630 So truncate will truncate all the data. 70 05:15.260 --> 05:18.830 Whereas here I am deleting only particular records only. 71 05:20.270 --> 05:23.180 So that is what is written over here. 72 05:24.020 --> 05:26.390 Delete can be used on the parent table. 73 05:26.390 --> 05:34.310 Can be used on a parent table if foreign key relationships are present on primary key table, but truncate 74 05:34.310 --> 05:39.620 cannot be used on a parent table if foreign key relationships are present on a primary key column. 75 05:40.100 --> 05:43.850 So this point I have explained to you over here. 76 06:02.720 --> 06:05.270 Right now. 77 06:11.410 --> 06:15.550 Now let me try to move to the second point. 78 06:16.870 --> 06:19.600 The second point is also very interesting one. 79 06:21.760 --> 06:23.380 And this is the second point. 80 06:23.890 --> 06:30.100 Delete does not reset identity whereas truncate reset identity. 81 06:30.490 --> 06:32.740 So what is the meaning of that one? 82 06:34.150 --> 06:39.490 So let me create two more table for the same purpose. 83 06:41.260 --> 06:44.380 Let me try to drop this one. 84 06:46.420 --> 06:48.370 And let me try to drop this one. 85 06:49.960 --> 06:52.840 So now again I am creating two more table. 86 06:52.960 --> 06:58.290 So let me create this first table in which I have defined identity as. 87 07:01.050 --> 07:03.990 One comma, one one minute. 88 07:15.480 --> 07:18.330 In which I have defined identity as one comma. 89 07:18.330 --> 07:20.760 one, and that is also primary key. 90 07:20.790 --> 07:26.400 I have defined and let me insert the data into the same. 91 07:27.630 --> 07:33.420 So I have inserted this data and this time I am not creating the foreign key. 92 07:33.420 --> 07:35.760 So let me create one more table. 93 07:41.250 --> 07:43.610 And insert the data into the same. 94 07:46.490 --> 07:49.550 Now let me show you the data of both the table. 95 07:51.350 --> 07:56.960 So this time only primary key is created on both of the table, but not the foreign key. 96 07:57.560 --> 08:02.660 So now let me show you one example. 97 08:11.030 --> 08:16.730 So now if I try to delete record from the EMP table where. 98 08:23.030 --> 08:24.710 ID equal to four. 99 08:25.280 --> 08:29.930 So it will allow me to delete data into the id equal to four. 100 08:29.930 --> 08:30.440 Right. 101 08:30.940 --> 08:36.250 So let me try to delete it has deleted the data. 102 08:36.250 --> 08:39.610 Now carefully observe this table. 103 08:40.240 --> 08:42.490 It is data one, two and three. 104 08:42.490 --> 08:43.060 Right. 105 08:44.290 --> 08:45.640 One two and three. 106 08:46.150 --> 08:56.950 Now again if I try to insert the data into this particular table in which I am giving name like. 107 09:00.460 --> 09:02.800 Raju and employee equal to 30. 108 09:03.700 --> 09:05.470 Now let me insert the data. 109 09:06.550 --> 09:13.510 I am not giving the value for the identity property because it is auto generated, right? 110 09:13.510 --> 09:15.340 Identity is auto. 111 09:17.470 --> 09:18.220 Generated. 112 09:18.220 --> 09:21.660 So I need to give value for this two column only. 113 09:25.260 --> 09:29.340 Now let me try to insert the data. 114 09:37.920 --> 09:38.610 Yeah. 115 09:39.030 --> 09:39.660 Yes is right. 116 09:39.660 --> 09:41.070 It will be five only. 117 09:44.850 --> 09:49.560 So it is five, right? 118 09:49.560 --> 09:50.550 It is five. 119 09:50.700 --> 09:54.360 But now let me try to delete five. 120 09:58.290 --> 10:00.090 And let me do one thing. 121 10:04.440 --> 10:06.660 Let me try to do this thing. 122 10:11.030 --> 10:19.610 This particular table and three db db cc check indent MP 2824. 123 10:19.610 --> 10:20.690 Receipt two three. 124 10:21.140 --> 10:22.370 Now what will happen? 125 10:22.370 --> 10:30.140 I have done the receipt one and now I am trying to insert that again the same value to the MP table. 126 10:32.090 --> 10:38.300 I am inserting one record and now I am checking so it will be not six. 127 10:38.300 --> 10:40.520 It will be again starting from four. 128 10:45.980 --> 10:51.620 Yeah, because of this DB this one will be happen. 129 10:52.550 --> 10:56.720 This function will be used for Reset the identity value. 130 10:57.530 --> 10:59.120 Reset the identity value. 131 10:59.150 --> 10:59.780 Dbcc. 132 10:59.780 --> 11:00.080 Check. 133 11:00.080 --> 11:00.620 Indent. 134 11:01.340 --> 11:02.900 MP 2824. 135 11:02.900 --> 11:03.920 Reset two three. 136 11:05.960 --> 11:09.440 So it will start from plus one. 137 11:09.440 --> 11:12.290 If you reset it to three then it will start from four. 138 11:12.320 --> 11:14.600 It will reset to four. 139 11:14.600 --> 11:16.310 It will start from five. 140 11:16.400 --> 11:19.990 If you reset to seven, it will start from eight like that. 141 11:20.020 --> 11:21.130 Next value. 142 11:26.980 --> 11:29.680 Yeah, it is also a function only. 143 11:29.680 --> 11:31.750 It is also a function or property. 144 11:31.780 --> 11:33.580 Property of SQL server. 145 11:36.700 --> 11:42.580 Yeah this is a everything you have to reset is also same thing. 146 11:42.580 --> 11:43.870 This is the parameter. 147 12:01.960 --> 12:02.980 Now. 148 12:06.670 --> 12:08.760 But now let me try to. 149 12:09.510 --> 12:12.780 So this is what this is the example of this one. 150 12:13.890 --> 12:15.690 Delete it. 151 12:16.650 --> 12:18.600 It does not reset the identity. 152 12:18.600 --> 12:20.490 You have to reset it manually. 153 12:21.150 --> 12:23.220 Delete does not reset the identity. 154 12:23.220 --> 12:30.510 You have to reset it manually using. 155 12:36.840 --> 12:37.650 Property. 156 12:43.530 --> 12:43.980 Yeah. 157 12:47.520 --> 12:50.100 No no no no no not because. 158 12:50.130 --> 12:50.760 Because it is. 159 12:50.760 --> 12:51.780 Identity column. 160 12:56.570 --> 12:57.200 Yes, yes. 161 13:03.320 --> 13:06.050 I know no, otherwise it is not required to do. 162 13:07.640 --> 13:08.090 Yes. 163 13:08.090 --> 13:08.270 Yes. 164 13:08.270 --> 13:08.840 Right. 165 13:13.280 --> 13:15.410 Now if I do the same. 166 13:15.920 --> 13:17.420 Truncate table. 167 13:18.110 --> 13:20.540 AMP MP 2824. 168 13:23.630 --> 13:27.950 And if I insert now if I insert the data. 169 13:29.960 --> 13:35.840 And now if I check this one it will start from one not 6 or 7. 170 13:37.700 --> 13:40.040 Because truncate will recede the identity. 171 13:40.040 --> 13:40.460 Right. 172 13:40.460 --> 13:41.900 What is written over here. 173 13:43.370 --> 13:46.700 It does reset the identity from beginning. 174 13:53.360 --> 13:55.940 It does reset the identity from beginning. 175 14:02.480 --> 14:05.480 Because of delete, do not does not reset the identity. 176 14:05.480 --> 14:12.070 We have to manually reset it through Dbcc check indent property of SQL server, in which you have to 177 14:12.070 --> 14:13.450 mention table name. 178 14:14.110 --> 14:15.280 This is the table name. 179 14:15.280 --> 14:18.940 This is a receipt and from which you want to receive. 180 14:20.620 --> 14:23.980 So minus one that particular value. 181 14:31.270 --> 14:32.760 You know can you manually. 182 14:32.760 --> 14:34.710 You have to write multiple statements. 183 14:37.380 --> 14:38.520 Like this. 184 14:39.270 --> 14:39.690 Over here. 185 14:39.720 --> 14:41.190 Table name will be different. 186 14:50.280 --> 14:52.050 No, no, that is not not possible. 187 14:52.050 --> 14:53.460 That is not possible. 188 14:54.180 --> 14:56.370 Uh, how can be two different identities? 189 14:58.140 --> 14:59.730 I don't think, uh. 190 15:08.880 --> 15:09.420 No, no. 191 15:09.420 --> 15:12.000 How can it be two different identity? 192 15:13.050 --> 15:14.280 Which column? 193 15:23.060 --> 15:23.300 Mhm. 194 15:25.040 --> 15:26.870 Then you have to. 195 15:29.270 --> 15:30.980 Let's try that one. 196 15:40.160 --> 15:40.520 Okay. 197 15:48.020 --> 15:56.570 So now let's see another, uh, question, another important properties of SQL server. 198 15:56.570 --> 16:01.040 So we have done with this constraint right. 199 16:01.040 --> 16:04.460 So this is further expansion of the constraint. 200 16:06.320 --> 16:08.540 This is the further expansion of the constraint. 201 16:08.540 --> 16:09.920 Let's see that one. 202 16:13.760 --> 16:16.610 So while creating a table. 203 16:18.680 --> 16:21.470 Or while creating a primary key foreign key relationships. 204 16:21.470 --> 16:26.780 There are three different properties are also available at the time at that time. 205 16:26.780 --> 16:31.100 So on delete cascade on delete set null and on delete set default. 206 16:32.410 --> 16:39.010 So now let me let me drop this table if it is available. 207 16:41.920 --> 16:44.500 So what is that on delete Cascade. 208 16:45.670 --> 16:51.130 On delete set null that we will understand now. 209 16:52.300 --> 16:56.710 So over here country is our parent table and state is our child table. 210 16:58.690 --> 17:01.390 Country is parent table and state is a child table. 211 17:01.390 --> 17:02.890 Create table countries. 212 17:02.890 --> 17:06.250 So I am creating this table with primary key column. 213 17:09.520 --> 17:15.490 And create table states referencing countries. 214 17:15.490 --> 17:20.730 Table country ID is a foreign key in this states table. 215 17:21.840 --> 17:27.510 Now let me try to insert the data into the both the table. 216 17:29.070 --> 17:31.920 Now I am selecting the data from both the table. 217 17:32.610 --> 17:33.960 This is my data. 218 17:35.490 --> 17:37.050 Now let me. 219 17:39.420 --> 17:43.350 Let me try to show, uh, try to delete the data from the parent table. 220 17:43.350 --> 17:45.070 We have already seen this one. 221 17:45.250 --> 17:48.160 We cannot delete because it has a reference. 222 17:49.420 --> 17:52.210 We cannot delete because it has a reference. 223 17:52.750 --> 17:55.510 Now let me. 224 17:58.870 --> 18:01.150 Try to drop this country's table. 225 18:01.150 --> 18:07.450 So it will not allow me to drop this country's table because I could not drop the object countries. 226 18:07.450 --> 18:08.110 Because. 227 18:09.950 --> 18:12.680 it is referenced by foreign key constraint. 228 18:14.960 --> 18:16.970 So no problem. 229 18:16.970 --> 18:18.560 Let me move ahead. 230 18:19.730 --> 18:24.200 Let me drop the state table and let me again drop the countries table. 231 18:24.440 --> 18:29.030 Now let me create this country table again. 232 18:29.060 --> 18:31.520 Now we will see on delete Cascade. 233 18:32.150 --> 18:34.550 So what is on delete Cascade. 234 18:38.420 --> 18:42.260 So now over here I am creating this test table with foreign key. 235 18:42.260 --> 18:47.420 But at the end I am adding this statement on delete Cascade. 236 18:49.790 --> 18:53.810 This one statement is additional over here in this at this time. 237 18:54.560 --> 18:56.390 So let me. 238 19:00.400 --> 19:03.580 Now select the data from the both the table. 239 19:04.360 --> 19:07.150 Now insert the data into the both the table. 240 19:08.590 --> 19:09.550 Now. 241 19:12.940 --> 19:14.560 Now let me try. 242 19:14.950 --> 19:19.240 So now country is my parent table and state is my child table. 243 19:19.750 --> 19:20.860 So. 244 19:22.030 --> 19:29.890 at this time, this data let me show you at that time this data is available with me country ID one 245 19:29.890 --> 19:34.390 two and country ID 112. 246 19:34.900 --> 19:42.160 So for country ID one two records are there in the child table and for country ID two one record is 247 19:42.160 --> 19:44.020 there in the child table. 248 19:48.690 --> 19:50.790 Now I am trying to delete. 249 19:50.970 --> 19:53.640 Previously we are getting some error, right? 250 19:53.640 --> 19:59.610 Because we cannot delete the data from the parent table if it is a child reference. 251 19:59.760 --> 20:02.040 If it is a child reference. 252 20:02.280 --> 20:04.950 So over here child reference are there right. 253 20:05.220 --> 20:08.640 For one also child reference are there for two. 254 20:08.640 --> 20:10.380 Also child references are there. 255 20:11.790 --> 20:13.680 Now let me try to delete. 256 20:18.960 --> 20:25.110 So it is deleted one record right now error is not coming this time. 257 20:26.820 --> 20:29.070 Now error is not coming this time. 258 20:29.070 --> 20:29.760 Now. 259 20:30.240 --> 20:30.960 Why? 260 20:31.470 --> 20:31.980 Now. 261 20:31.980 --> 20:34.260 See the magic now. 262 20:34.290 --> 20:35.460 Magic will happen. 263 20:37.050 --> 20:39.150 It will delete it from both the table. 264 20:43.380 --> 20:46.680 Country ID one is deleted from both the table. 265 20:47.130 --> 20:47.820 Why? 266 20:48.420 --> 20:50.730 Because of on delete cascade. 267 20:53.010 --> 21:00.470 If you delete the data from the parent table, the child record is will be automatically deleted. 268 21:02.750 --> 21:07.820 Over here we are deleting just from one table, but it is deleted from two tables. 269 21:10.460 --> 21:18.050 It is deleted from two tables because if we delete data, this will delete data from the child table 270 21:18.050 --> 21:18.710 as well. 271 21:19.040 --> 21:25.400 This will delete data from the child table as well because on delete, cascade is used while creating 272 21:25.400 --> 21:26.420 the child table. 273 21:34.190 --> 21:39.020 So maybe sometimes in the interview they will ask that what is the on delete cascade. 274 21:40.070 --> 21:42.440 Then you have to explain like this. 275 21:42.890 --> 21:47.120 It will delete data from the child table as well. 276 21:47.120 --> 21:50.450 If you delete data from the parent table. 277 21:50.450 --> 21:53.030 Now again let me drop this table. 278 21:56.540 --> 21:58.940 Again let me drop both of the table. 279 21:58.940 --> 22:00.800 Now on delete set null. 280 22:02.720 --> 22:06.200 So again my creating table one. 281 22:09.940 --> 22:11.350 In I am creating. 282 22:11.380 --> 22:12.910 Table two. 283 22:18.760 --> 22:23.920 So now in place of on delete cascade I have write own delete set null. 284 22:26.020 --> 22:27.190 On delete set null. 285 22:27.190 --> 22:33.100 Now I am inserting the data from both the table into the both the table. 286 22:33.100 --> 22:36.030 Now I am selecting the data from both of the table. 287 22:38.730 --> 22:39.630 By. 288 22:47.400 --> 22:48.150 Okay. 289 23:08.880 --> 23:11.130 Now let me drop again. 290 23:14.130 --> 23:15.540 Let me create. 291 23:19.170 --> 23:23.720 Create table and insert data into both the table. 292 23:25.160 --> 23:29.180 Now let me select the data from both the table. 293 23:29.180 --> 23:33.890 So one reference is over here in the country id. 294 23:34.730 --> 23:39.380 Now I am deleting the data from the countries table. 295 23:39.380 --> 23:41.270 So this time what will happen. 296 23:41.690 --> 23:42.080 Again. 297 23:42.080 --> 23:43.250 It will delete the data. 298 23:43.250 --> 23:47.570 But now this time it will not delete the child record. 299 23:47.570 --> 23:51.140 It will update the country ID to null. 300 23:52.250 --> 23:55.400 It will update the country ID reference column to null. 301 23:55.970 --> 24:00.440 Previously it has deleted both of the record because of own delete set. 302 24:00.440 --> 24:02.180 So on delete cascade. 303 24:02.420 --> 24:04.790 But now on delete sentinel we have used. 304 24:04.790 --> 24:06.380 So what happened. 305 24:07.760 --> 24:10.360 It has deleted the records from the child. 306 24:10.360 --> 24:10.720 Sorry. 307 24:10.720 --> 24:11.410 It has. 308 24:15.070 --> 24:17.140 Then the null column to the country. 309 24:17.140 --> 24:18.760 ID column into the child table. 310 24:18.790 --> 24:20.860 Because of own delete set null. 311 24:22.780 --> 24:29.230 This is again one more property of SQL server and now own delete set default. 312 24:32.050 --> 24:33.430 So again over here. 313 24:33.430 --> 24:35.770 Nothing, uh, special this time. 314 24:35.770 --> 24:37.600 It will make a default value. 315 24:50.440 --> 24:50.620 Yeah. 316 24:50.620 --> 24:54.430 Wherever the wherever primary key is, there it is a parent table. 317 24:54.430 --> 24:58.890 And whenever foreign key is defined, it is a child table. 318 25:07.200 --> 25:08.310 A yes is right. 319 25:13.620 --> 25:15.870 Now only set default. 320 25:16.710 --> 25:21.300 So over here what will happen? 321 25:25.530 --> 25:30.180 Now let me try to select the data from both of the table I have not inserted. 322 25:30.180 --> 25:30.630 Right. 323 25:32.940 --> 25:33.720 Okay. 324 25:33.720 --> 25:36.840 Let me now select the data from both of the table. 325 25:37.170 --> 25:39.600 So it is 12112. 326 25:40.110 --> 25:44.190 Now if I delete now what will happen. 327 25:46.860 --> 25:50.490 It will make country ID column to default two. 328 25:50.520 --> 25:52.890 If I delete that the data from the. 329 25:55.080 --> 25:56.100 Parent table. 330 25:56.400 --> 25:59.010 So let me show you. 331 26:01.230 --> 26:02.340 Delete from countries. 332 26:02.370 --> 26:05.190 Now let's see the data of both of the table. 333 26:06.180 --> 26:08.250 Previously it was one one over here. 334 26:08.250 --> 26:09.210 Now it is two two. 335 26:09.240 --> 26:09.840 Why? 336 26:10.430 --> 26:15.530 Because we have defined country ID column two, default two. 337 26:18.500 --> 26:22.010 While creating a foreign key on this particular column. 338 26:29.900 --> 26:32.480 So these are the three different properties. 339 26:34.940 --> 26:37.100 Are there in SQL server. 340 26:39.590 --> 26:43.310 Now the not null constraint can only be defined at the column level. 341 26:43.310 --> 26:45.830 It cannot be used at the table level. 342 26:45.860 --> 26:50.420 So this is one more thing we can understand. 343 26:51.020 --> 26:53.960 Let me try to create. 344 26:58.040 --> 27:01.480 Let me drop this table if it is already exist. 345 27:03.940 --> 27:07.090 So now constraint can be defined on the two levels. 346 27:07.090 --> 27:10.450 One is column level and another is table level. 347 27:10.780 --> 27:15.550 So this is a column level constraint whenever. 348 27:15.790 --> 27:17.260 Why it is column level. 349 27:17.470 --> 27:24.070 Because at the time of creating the column in defining the data type, we are giving the constraint 350 27:24.100 --> 27:27.970 name with column name itself. 351 27:27.970 --> 27:30.700 So it is known as column level constraint. 352 27:31.810 --> 27:33.640 Now let's see. 353 27:46.980 --> 27:47.850 Now let's see. 354 27:47.850 --> 27:50.280 That is known as a column level constraint. 355 27:50.430 --> 27:54.570 Now if we are creating like this. 356 27:57.120 --> 27:59.430 So over here what it has done. 357 28:02.610 --> 28:05.610 Over here we have done product id integer. 358 28:07.110 --> 28:14.310 Then product name where this is a column level, not null is this is we have given as a column level. 359 28:14.310 --> 28:14.820 Right. 360 28:15.780 --> 28:17.010 But now. 361 28:21.390 --> 28:23.070 Now let me. 362 28:23.880 --> 28:26.700 If I declare like this. 363 28:28.560 --> 28:31.200 So let me remove this from here. 364 28:31.950 --> 28:36.140 And let me try to declare two constraints. 365 28:36.140 --> 28:37.220 Let me. 366 28:57.950 --> 28:59.090 Now let's see. 367 28:59.090 --> 29:02.960 Let me remove this one and let me try to create this table. 368 29:05.900 --> 29:06.380 Okay. 369 29:06.380 --> 29:08.030 It is already existed. 370 29:09.830 --> 29:12.200 Let me give you some another name. 371 29:17.030 --> 29:23.440 Now previously we have defined primary key at the over here only right. 372 29:23.440 --> 29:26.980 For product ID we have defined primary key over here. 373 29:27.430 --> 29:29.530 So that is known as a column label. 374 29:29.530 --> 29:31.240 But now what we have done. 375 29:33.850 --> 29:41.740 After creating all the columns, after creating all the columns we are defining the constraint at the 376 29:41.740 --> 29:42.550 last. 377 29:44.080 --> 29:46.630 So this is known as a. 378 29:53.560 --> 29:55.120 Table level constraints. 379 29:56.380 --> 29:57.640 Table level constraints. 380 29:57.670 --> 29:58.150 Why? 381 29:58.180 --> 30:04.990 Because after declaring all the columns we are defining, we are defining this particular constraint. 382 30:05.380 --> 30:08.440 But now let me remove this one. 383 30:08.860 --> 30:11.820 And let me try to do same for the not null. 384 30:12.450 --> 30:13.770 So what is given? 385 30:16.380 --> 30:21.930 Suppose if I define over here unique. 386 30:21.930 --> 30:23.640 So error is gone right? 387 30:23.670 --> 30:24.690 Error is gone. 388 30:25.590 --> 30:29.130 But now let me remove the and define not null. 389 30:30.210 --> 30:33.840 Not null constraint can be defined. 390 30:40.980 --> 30:45.120 At the column level only. 391 30:45.120 --> 30:51.270 This is one more interview question, not at the table level. 392 30:51.270 --> 30:52.620 It is not possible. 393 30:58.140 --> 30:58.740 Okay. 394 30:59.040 --> 31:01.770 Can we define not null constraint at the table level? 395 31:01.770 --> 31:03.210 No we cannot define. 396 31:08.130 --> 31:08.610 Right. 397 31:14.340 --> 31:18.390 So now let me know if you have any question. 398 31:29.810 --> 31:30.110 Yeah. 399 31:30.110 --> 31:34.310 So this is when we are defining the column level constraint. 400 31:34.460 --> 31:43.310 We are writing with column name itself like we have declared over where column name, then integer data 401 31:43.310 --> 31:46.190 type and then primary key. 402 31:46.280 --> 31:48.560 So this is again known as. 403 31:50.600 --> 31:57.050 Column level constraint because we are defining constraint at the time of declaring column name. 404 31:57.440 --> 31:59.690 But what is known as a table level constraint. 405 31:59.720 --> 32:01.280 Table level constraint. 406 32:01.370 --> 32:11.600 After declaring all the columns if it is hundred column no issue column A, B, c after declaring all 407 32:11.600 --> 32:18.940 the column, when we are defining the particular constraint, it is known as a table level constraint. 408 32:19.960 --> 32:22.090 It is known as a table level constraint. 409 32:22.120 --> 32:31.480 Now not null constraint can be defined at the column level only, not at the table level. 410 32:33.220 --> 32:41.920 We cannot define at the table level because it is a property of SQL server. 411 32:52.690 --> 32:55.120 Over here we cannot write like not null. 412 32:55.120 --> 32:56.410 It is giving me some error. 413 32:56.410 --> 32:57.490 But yes. 414 32:57.880 --> 32:58.480 Unique. 415 32:58.480 --> 32:59.500 You can write. 416 33:02.100 --> 33:04.980 Error will go on then. 417 33:06.330 --> 33:06.930 Yes. 418 33:24.570 --> 33:25.350 Yeah. 419 33:32.910 --> 33:38.430 No, no, that is, uh, that is as per your your choice as per your choice. 420 33:38.820 --> 33:45.270 If you have even not creating constraint at the table level as well as at the column level. 421 33:45.270 --> 33:49.520 After that, also you will be able to create constraint primary key. 422 33:50.600 --> 33:52.880 For that you need to do alter command. 423 33:54.500 --> 33:56.330 For that you need to do alter command. 424 33:56.330 --> 34:02.570 And this is your totally your choice whether you want to define it column level or whether you want 425 34:02.570 --> 34:04.220 to define it table level. 426 34:04.700 --> 34:05.540 That is your choice. 427 34:05.540 --> 34:08.660 I am showing you what are the different ways. 428 34:10.340 --> 34:14.660 For that purpose, I am explaining you now, not null constraint. 429 34:14.960 --> 34:18.740 One way is this one, but there is no other way. 430 34:21.170 --> 34:27.020 You can alter command for sure, but you cannot declare while table level. 431 34:27.620 --> 34:29.210 But now let me show you. 432 34:29.210 --> 34:32.690 Primary key constraint can be defined on two columns also. 433 34:32.690 --> 34:33.500 Maybe. 434 34:33.500 --> 34:33.520 be. 435 34:37.960 --> 34:41.620 Primary key constraint can be defined on two columns. 436 34:42.190 --> 34:44.890 Now let me try. 437 34:49.000 --> 34:54.160 So let me remove this one and let me try now. 438 35:04.990 --> 35:06.070 Let's see. 439 35:18.130 --> 35:21.400 There is already an object named PK in the database. 440 35:21.430 --> 35:22.510 Okay. 441 35:22.660 --> 35:23.350 Okay. 442 35:26.610 --> 35:27.030 No. 443 35:27.030 --> 35:27.480 No one. 444 35:37.830 --> 35:38.490 From peak. 445 35:38.490 --> 35:40.080 I have made it to peak one. 446 35:40.080 --> 35:42.150 Now I am able to create this table. 447 35:42.150 --> 35:44.970 Right now it is not giving me error. 448 35:45.120 --> 35:48.270 Now let me try to insert the data into the same. 449 35:50.250 --> 35:52.680 Now one comma product a b c. 450 35:54.780 --> 35:58.080 Now again I am inserting one. 451 35:59.880 --> 36:01.080 Will it allow? 452 36:08.100 --> 36:08.550 It. 453 36:08.580 --> 36:09.390 It will allow. 454 36:09.390 --> 36:10.470 It will allow. 455 36:10.470 --> 36:11.190 It will allow. 456 36:11.220 --> 36:11.790 Why? 457 36:13.170 --> 36:14.220 No no no no no. 458 36:14.220 --> 36:20.400 It is a combination on two different columns we have created primary key ID and name. 459 36:20.400 --> 36:22.800 Now let me show you why. 460 36:22.800 --> 36:24.270 When it is giving me error. 461 36:25.080 --> 36:26.550 Now it is giving me error. 462 36:29.220 --> 36:32.070 The duplicate key value is one comma ABC. 463 36:32.520 --> 36:38.120 Now primary key is a combination of these two columns, not only one column. 464 36:40.640 --> 36:44.120 Not only this one column, it is a combination of two columns. 465 36:51.800 --> 36:52.370 Yeah. 466 36:52.370 --> 36:55.100 And you can also say one thing. 467 36:58.670 --> 37:00.650 It is a composite primary key. 468 37:03.260 --> 37:06.530 You can also say that, uh, candidate key. 469 37:06.560 --> 37:07.160 Also. 470 37:09.770 --> 37:15.140 You know, at the time of normalization, we have shown seen order ID and product code is a combination 471 37:15.140 --> 37:18.020 of uniquely identify each record. 472 37:18.020 --> 37:19.550 So it is a candidate key. 473 37:19.580 --> 37:25.120 So now over here also this two row is uniquely identify each record. 474 37:25.750 --> 37:28.690 So it is also candidate key composite primary key. 475 37:37.240 --> 37:38.530 So. 476 37:43.150 --> 37:46.870 Actually I want to start a new identity property. 477 37:46.870 --> 37:50.710 We have already seen SP rename if we haven't already seen. 478 37:50.710 --> 37:53.680 So first phase is over. 479 37:53.680 --> 37:55.210 First phase is completed. 480 37:55.210 --> 37:56.590 This is the first phase. 481 37:59.590 --> 38:01.240 First phase is completed. 482 38:01.240 --> 38:03.460 We will start from functions tomorrow. 483 38:03.880 --> 38:08.560 But now from tomorrow I will allow last 15 minutes to speak. 484 38:08.560 --> 38:16.770 Everyone that you can, uh, write your doubts in the notepad file in in the last 15 minutes. 485 38:16.770 --> 38:19.350 You can ask your whatever questions. 486 38:24.780 --> 38:25.320 Super. 487 38:28.230 --> 38:30.450 Super key or composite? 488 38:30.450 --> 38:31.110 Primary key. 489 38:41.490 --> 38:43.260 No, no one more key is a candidate. 490 38:43.260 --> 38:44.190 Candidate key? 491 38:50.160 --> 38:51.180 Uh, yeah. 492 38:51.180 --> 38:53.280 That is a, I think, surrogate. 493 39:01.760 --> 39:02.210 So. 494 39:02.210 --> 39:06.260 So, uh, this is also same right over here. 495 39:06.260 --> 39:07.820 1 or 2 attributes. 496 39:07.820 --> 39:09.800 Two attributes means two columns. 497 39:10.070 --> 39:16.820 So these two columns will be uniquely identify each record how this one is one record. 498 39:16.820 --> 39:17.570 Unique record. 499 39:17.570 --> 39:18.170 Right. 500 39:18.350 --> 39:20.570 This one another is unique record. 501 39:25.640 --> 39:27.950 So this will be that. 502 39:27.950 --> 39:30.950 Maybe you are talking about surrogate key, not super key. 503 39:34.520 --> 39:38.000 Because there is nothing like super key in SQL server. 504 39:38.420 --> 39:40.970 Let me google it. 505 39:44.720 --> 39:53.890 I'm also not sure what is that single key, or a group of multiple keys that uniquely identify a tuples. 506 39:54.490 --> 39:55.300 What is this? 507 39:55.300 --> 39:58.420 Tuples in a table? 508 39:59.500 --> 40:04.720 Multiple attributes that might not be able to identify tuples in a table. 509 40:11.830 --> 40:13.240 Okay a couple issues. 510 40:21.310 --> 40:22.240 So, uh. 511 40:22.240 --> 40:22.630 No. 512 40:22.630 --> 40:23.650 No, over here it is. 513 40:23.650 --> 40:24.670 Same also, right? 514 40:24.730 --> 40:28.480 123455. 515 40:28.480 --> 40:29.440 And this one. 516 40:29.440 --> 40:29.950 Two. 517 40:29.980 --> 40:30.970 Everything is unique. 518 40:30.970 --> 40:33.370 Only over here. 519 40:44.130 --> 40:44.610 Okay. 520 40:45.270 --> 40:45.570 Okay. 521 40:45.570 --> 40:45.840 Okay. 522 40:50.280 --> 40:50.580 Yeah. 523 40:50.580 --> 40:51.450 Okay. 524 40:57.870 --> 41:01.470 So now let me know if anyone has any doubt.