All language subtitles for 14. ALTER

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