Universal Database (netDB2) Introductory Guide

Alter And Drop Statements

In order to modify the schema of an existing relation the statement of "Alter" is used. These modifications are done by a statement that begins with a keyword ALTER TABLE and the name of the relation. 

Syntax:
	Alter table {Tablename} Add {attribute name and datatype}

example:
	for a schema Create Table customer
	(ssn integer NOT NULL,
	name varchar(20),
	acct integer,
	age integer,
	primary key(ssn));
	
	alter customer add phone varchar(19);

In this case the customer has 6 attributes, in the actual relation, tuples would now all have components phone, but since it has been added later, the value of each of these components would be NULL. We can set a default value of added tuples by:

PRIMARY KEY:
Assume that there isnt any primary key for the table customer and we want to make the ssn as the primary key, adding the primary key constraint could do this: 

alter {tablename} add constraint {constraint name} primary key ({attribute name});

alter table customer add constraint primconst primary key (ssn);

Now there can be 2 tuples of the same ssn.

DROP A TABLE:
In order to drop a table and all its data, the following statement can be used. 

DROP TABLE table-name;

Example:
Drop table customer;

A constraint can be dropped from the table scheme for e.g., if one wants to remove the primary key from the table customer then: 

Alter table <tableName> Drop Primary key;

Alter table customer drop primary key;

As a result of the SQL statement, the following statement is possible. 

Insert into customer values('jane',128),('Mark',287);

Moreover, if any other table had been used the "drop" attribute as the foriegn key, then the referential key constraint on the second table would be automatically removed. 

Example:
Create table cust (name varchar(20), custid int references customer(ssn));

Would'nt have allowed entries in cust with cust-id's, which were not present in customer(ssn). But after the customer key had been dropped from the customer table, it would be possible to have entries in the cust table, with custid which are not ssn attributes for customer. 

insert into cust valyes('amrita',140),('adrita',130);

{140,130 are not ssn attributes in customer)

Once the primary key is dropped from the table, all the referential integrity constraints are also dropped and they have to be explicitly added on.