SQL session 3

More about constraints in SQL

-- SELECT * FROM demo;

-- Adding the unique constraint to specific column in the table using Alter clause

-- ALTER TABLE demo
-- ADD UNIQUE (name);

-- ALTER TABLE demo
-- DROP INDEX name;

-- ALTER TABLE demo
-- ADD phone_number SMALLINT(12);

-- ALTER TABLE demo 
-- ADD email VARCHAR(400);

-- ALTER TABLE demo
-- ADD CONSTRAINT Unique_phone_and_emails UNIQUE(phone_number, email);

-- INSERT INTO demo(name)
-- VALUES ((abudi),(abudi));

INSERT INTO demo(name, phone_number,email)
VALUES(('David','075791751','davidderrrick@gmail.com'),('Enock', '0758585390', 'enochmale@gmail.com'));

 alter TABLE demo
-- modify phone_number int(12);

-- This won't work because the columns phone_number and email are absolutely similar. 
-- However, if any of the two columns is modified to differ from the other, it will add the records to demo
/*
INSERT into demo(name, phone_number, email) values (('Anyuru', '0791301243', 'egwillface@gmail.com'),('Anyuru', '0791301243', 'egwillface@gmail.com'));
        
*/



-- ALTER TABLE demo
-- Add age SMALLINT(3) NOT NULL;

-- INSERT into demo(name, phone_number, email, age, gender) 
-- values ('Anyuru', '0791301243', 'add@gmail.com', 23, 'Male'),
-- ('Anyuru', '0700820686', 'ace@gmail.com', 3, 'Male');
 
-- Add a default constraint on an existing column e.g set default age to be 1
-- ALTER TABLE demo
-- ALTER age SET DEFAUlt 1;

-- Test if default age constraint has worked
-- INSERT into demo 
-- (name)
-- VALUES
-- ('Cosma');

/*
INSERT INTO demo(name, hint, phone_number, email, age)
VALUES ('Carol', 'Programmer', '0893898379', 'namlet@gmail.com', 21),
        ('Dorcas', 'Researcher', '0777054990', 'ddnaiga@gmail.com', 33);
*/

-- Add a check constraint that limits gender column to female or Male values only.
-- ALTER TABLE demo
-- ADD gender VARCHAR(6) NOT NULL
-- CHECK (gender IN('Female', 'Male'));

-- Make sure that gender column has Female value as default, if not explicitly specifieddemo
-- ALTER TABLE demo
-- ALTER gender SET DEFAULT 'Female';
-- alter TABLE demo
-- modify phone_number int(12);
/*ALTER TABLE demo 
ADD phone_number int(12);*/
/*ALTER TABLE demo 
ADD email VARCHAR(0);*/
/*ALTER TABLE demo 
ADD age int(2);*/

/*CREATE INDEX quickly_search_names
ON demo (name);
CREATE  UNIQUE INDEX quickly_search_names
ON demo (name);
CREATE  UNIQUE INDEX quickly_search_names
ON demo (name,hint);
*/