Add Several Default with airSlate SignNow

Add several default

welcome to presume technologies I am venket this is part 4 of signal server in this session will understand how to add a default constraint in part 3 of this video series we have seen how to create tables specifically we have created TBL person and TBL gender tables and we have also seen how to add a primary key and a foreign key constraint if you remember in TBL person in TBL person table we mark this ID column as a primary key and gender IDs a foreign key and along the same lines in TBL gender table we mark the ID column as its primary key now if you look at the sample data that we have caught in these two tables okay this is how we have the sample data and if you look at the columns in TBL person table you know we have got four columns here the first three columns ID name and email they have a not null constraint meaning whenever you insert a row into this TBL person table you have to mandate release apply the values for ID name and email columns but whereas if you look gender ID it's it's not mandatory I mean you don't have a not null constraint you're saying that this column does allow us it means when you insert some of the records for some of the records you can have gender IDs na but whereas ID name and email cannot be now okay so let's insert a record into this TBL person table and obviously to insert a row into this table you can either do that graphically or using an insert query let's do it using an insert query so insert into the table name so I want to insert the data into this TBL person table now you can also specify the column list let's say I want to insert the values for ID name email and gender ID now gender ID since gender ID allows now you may or may not supply a value for that let's say I don't want to supply a value for that column and then I will say values so into this table for these columns we have to supply the values so let's say for example the ID of the person is going to be seven and the name of the person may be rich and maybe their email are at our comm okay if you look at this row that we are going to insert we haven't supplied gender ID and since it's nullable column it means this column allows now so when I execute this query and when we select the rows from this table you should see that rich record has got null there okay now let us say when somebody doesn't supply a value and if you look at the TBL gender table for example here okay if you don't know the you know gender of a person you know I would recommend you know I want the data to be inserted in such a way that they will get you know by default three okay so it's you know instead of storing null I want to store a default value okay if we don't know the gender of a person rather than allowing nulls there I want to insert number three so that anybody when they look at this data okay rich gender ID is three so his gender is unknown okay so I want to have defaults so how do we do that using a default constraint but if you don't have a default constraint and if you don't supply a value for a nullable column then by default it will get another value there but on the other hand you can control that using a default constraint so in this case let's say anytime we insert a record into TBL person table and I don't supply a value for gender ID column I want the default number three to be inserted okay let's see how to do that so to add a default constraint okay here is the syntax altering an existing column to add a default constraint so obviously we are changing the definition of the table so we have to say all the table in this case TBL person and what do we want to do we want to add a constraint add constraint and give a meaningful name to that constraint and default and then whatever is the value in our case it's going to be three for gender ID column within this TBL person table so let's do that so to add the default constraint alter table and which is the table that we want to alter TBL person table so alter table TBL person now how do we want to add alter this table we want to add a constraint so add constraint and give the constraint a meaningful name since this is going to be a default constraint I would give an abbreviated form DF and then we are adding this constraint to this table so include the table name as well so TBL person and for which column we are adding that we are adding it for gender ID column so specify the gender ID column as well so just by looking at the name the constraint name you can say okay this constraint is for TBL person table and it's a default constraint for which column gender ID so give a meaningful name to your constraint and then what type of constraint is this this is a default constraint and you can specify the value in our case the value is going to be 3 for gender ID column so 3 for gender ID okay so when we execute this query look at that if you look at the TBL person table now if you expand constraints you don't have anything there but once we can create this constraint we should get that there so let's execute this query so command completed successfully now if I go ahead and refresh the constraints folder of TBL person we should see the default constraint created there now let's insert if you look at the records in TBL person table now we've got 7 records and rich has got now now let us say I want to insert somebody here like Mike and his ID is 8 and let us say my cat or com and if you look at this query we are still not supplying the gender of this person Mike's gender now but we have added a constraint so what should happen now if you do not supply a value for gender ID column then this constraint you know will insert default value which is three for the gender ID for Mike so let's see how you know let's execute and see what happens so when I execute this one row has been affected and if you select all rows look at this for Mike didn't supply a value so it took the default but on the other hand if I supply a value let us say I'm going to enter somebody you know I'm going to pass the gender ID as well because I know the gender ID of the person and let's say I'm going to say this time Sarah let's say her email ids s at our comm and I want to supply gender ID for her I know she is a female so I'm going to pass the value for that so when you pass the value are specifically saying Sarah's gender ID is 1 so so now the default will not be kicking in so this value will be stored for Sarah so when I execute that and when you check TBL person you should see her gender ID which we have supplied and finally let's do this let's say I'm going to insert maybe John's record whose ID is going to be 10 we already have John so let's say Johnny for example let's say his email ID is J at our comm now I am passing in the gender ID but I am specifically saying I don't know his gender so I'm just going to say now so you are specifically passing null value which means we'll the default be assigned or will it not be a sign now let's execute and see what happens so I executed that query Johnny is the latest record that we have inserted and if you look at the result for Johnny it didn't take the default why because if you supply a value even if it is null you know the default will not be executed instead the value that you have supplied will be you know insert it into the table but in the other case if you miss the value or if you don't supply a value that's when default will be considered okay so we have seen how to alter an existing column to add a default constraints it's also possible that when you are creating a table or if you want to add a new column to a table for an existing table and you want to have a default for that column it is still possible adding a new column with the default value to an existing table and this is the syntax of the table and the table for which you want to add this new column you add that columns basically the data type whether it allows nulls or not and then constraint give it a meaningful name and the default value for that okay and how to drop a constraint you know you can use alter table command drop constraint and the constraint name okay so if we want to drop this constraint we can use drop constraint statement but before that we have to use alter table so alter table TBL person drop constraint and the name of the constraint so in our case the name of the constraint is this one so when I plus f5 the constraint will be dropped so if we refresh this constraints folder on the left hand side that should be gone so it's possible to assign a default to a column using the default constraint on the slide you can find resources for asp.net and C sharp interview questions that's it for today thank you for listening have a great day