Original subtitles

0 In the last video 1

We saw how to delete the rows 2

In a table 3

Let us now look at how to change the structure of a table 4

If I run select star command now on the table 5

We can see that the table has 6

Five columns 7

And each column has a column name 8

and a column type 9

Adding or deleting a column 10

or changing this column name or this type 11

On adding constraint is called changing the structure of table 12

To change the structure of the table we will be using alter table command 13

Let us look at the presentation 14

So the syntax of this alter command goes like this 15

We will write alter table and then write the table name 16

And then we'll specify the action 17

actions can be broadly classified into three parts 18

one will be actions on columns 19

Which will include adding deleting modifying or renaming a column 20

or 21

It can be on constraints 22

Which will include adding or deleting a constraint or you can add or delete an index 23

Let us first look at how to add or delete a column 24

To add a column we will write alter table 25

And the table name 26

Then type 27

Add 28

And then write the column name and then specify the data type of that column name 29

Let's go to the PG admin interface 30

Let us type alter table 31

Customer table 32

this is the table name 33

then Add then what will be the column name it will be test column 34

And it contains a 35

VARCHAR type of 36

Data 37

Let us run this command 38

Query was successful 39

Let us run the select star command again 40

You can see 41

at the end of the table we have a new column called test 42

And it has that particular data type that we specified which is 43

Variable characters 44

With 255 maximum limit 45

This is how we added a column in our table 46

No let us delete this column 47

Using the delete command 48

Let us look at the syntax first 49

To delete a column 50

Drop command is used. So we will 51

type Alter table tablename then write drop 52

and then the column name 53

we can also write drop column and then specify the column name 54

Let us try these two variants 55

Drop 56

Test 57

Let's run this 58

it was successful 59

let us run the select start command again. you can see the last column is now deleted 60

You can also write drop column and then specify the column name 61

I'll add the test 62

column again and now I will delete it again using the Drop column command 63

Just run the select star command again to check 64

Yes the test column is 65

Deleted 66

Next we will look at how to 67

Modify a column 68

What can we change in a column, we can change the data type that column will be containing 69

If earlier 70

Age is an integer type of column 71

We can change that data type to variable characters 72

The syntax for that will be 73

Alter table 74

Table name and then alter column 75

Column name and then you specify the new data type for that column 76

Let us go and change the data type of 77

age column from integer to 78

Variable characters 79

Alter table customer table then alter column 80

Column name 81

Type then the new type which will be VARCHAR 82

Run this command 83

It was successful 84

Run the select star command to look at your data 85

You can see age which was earlier integer 86

Is now variable character type 87

now you want to rename any particular column 88

Say for example we have email id column here 89

If you want to rename to customer email 90

How to do that let us look at the syntax 91

So to rename a column this is the syntax 92

Alter table 93

Mention the table name then rename column 94

first you will write the column name the old column name 95

Then the keyword to 96

and then you specify the new column name 97

Let us go and change the name of 98

Email id column 99

Alter table tablename then 100

Rename column 101

Old column name 102

then to new column name 103

Run this command 104

It was successful 105

let us run the select star command 106

You can see the 107

Column name which was earlier email ID is now customer email 108

Now let us look at 109

How to 110

Add or delete a constraint 111

so till now we did not have any constraint on any of the columns 112

If I want to add a constraint 113

Such as 114

My first name or the customer ID cannot be null 115

Show that whenever I am getting the 116

Data entered from any source 117

My database checks and confirms that it is conforming to the specified constraints 118

To do that we will need to add that particular constraint to our table 119

To add that constraint 120

This is the Syntax alter table 121

Table name alter column column name set to not null 122

so this particular column cannot be null 123

Whenever you will be inserting data into any particular column 124

This column must have some value if it is not having any value it will give an error 125

so let us go and run this query 126

Alter table customer table 127

Alter column 128

Column name 129

This is 130

Cust ID and this should be not null so set not null 131

So the not null constraint will be applied to 132

Cust ID now 133

let us run this 134

This is successful. Now let us try to insert values where customer ID is missing 135

so we will insert into 136

Customer table 137

we'll need to specify the column names 138

It'll start with first name last name 139

Age and email ID 140

Customer email 141

And then it will have values 142

ABCD 143

Aa 144

bb 145

25 146

And an email id 147

So when I run this command 148

I get this error 149

Null value in column customer ID 150

Which violates the not null constraint. this is the constraint we applied earlier 151

Now if I want to remove this constraint 152

How to remove this particular constrain. Let us go and check the syntax first 153

Syntax to remove the not null constraint is same as above but 154

Instead of the set keyword, we will be using the Drop keyword 155

so let us go and write this command with the Drop keyword 156

Alter table tablename 157

Alter column 158

Cust ID 159

Drop not null 160

So this will drop the constraint of not null. Let us run this 161

Ran successfully. Now let us try to insert the earlier one 162

This also ran successfully. Let us run the Select start command to check if 163

the Data is added 164

You can see cust ID is having null value 165

because 166

It did not have the constraint of not null 167

Next 168

now if you remember we also discussed that there is a constraint named check 169

If you want to put a check on the value that any particular column is containing 170

so let us now add a constraint 171

Where the customer ID value 172

Cannot be less than zero so it cannot be negative 173

So we'll add a constraint that the value should be greater than equal to 1 174

so let us go to the PG admin interface 175

And add this constraint 176

Alter table tablename 177

Add constraint constraint 178

Mention the column name 179

Cust ID 180

And check 181

In bracket cust ID 182

Should be greater than 183

Equal to zero greater than zero 184

let's run this 185

It ran successfully 186

Now let us try to insert 187

a value where cust ID will be negative. So insert into table name 188

Values 189

-1 190

a 191

cc, dd 192

87 193

and an email id 194

cd@ xyz.com 195

So if you will run this we should get an erro 196

and we do 197

If you read the error it says new row for relation customer table violates check constraint 198

that's the check constraint that we added 199

and it is violating that particular constraint 200

Apart from this 201

You can specify 202

Whether a particular 203

Column is a primary key for you are not 204

And you can also specify a column to be a foreign key 205

We discussed primary and foreign key earlier also 206

And in the later part of the course you will find another separate video 207

on primary and foreign keys 208

so let us add this constraint of primary key and foreign key 209

on our table. first let's look at the 210

syntax 211

So to make a column 212

A primary key 213

This is a Syntax alter table table name 214

Add primary key 215

and then the column name 216

So 217

if we go and make cust ID our primary key 218

We'll write 219

Alter table 220

Table name add primary key 221

then the column name 222

This will make 223

cust ID as the primary key 224

So the column name should be in brackets bracket cust ID 225

and we run this command 226

So you can see that we are getting an error message which is saying 227

Column cust ID contains null values 228

So we are unable to make cust id as a primary key because it is containing a null value where as 229

Primary key cannot have null values 230

Therefore what we'll do is we will delete the row which has the null value and then we'll 231

Make it the primary key 232

Delete from customer table so if you remember this we did in the previous videos 233

How to delete rows from a Table 234

We'll not put any condition we'll clean the table 235

Delete from 236

Customer table delete from customer table we will run 237

Now, our table has no values 238

Now that my table is clean, I'll insert one value 239

Into our table 240

Using insert into command let us run this insert into command 241

So if I run select star I can see that I have that 242

One value 243

So you can see that we have that value. Now let us make cust ID as the primary key 244

Alter table customer table add primary key 245

brackets cust ID 246

run this command 247

It ran successfully. it is now a primary key 248

So to 249

Make any particular column as a foreign key 250

we have given a syntax here. this presentation will be part of the resources for this lecture 251

I'll suggest that you open this presentation look at the syntax and try to do it on your own 252

So in this video we have learnt how to change the structure of the table 253

now it is time to master all the commands of data querying 254

See you in the next video