1.Create a table that demonstrates the required constraint (i.e., not null).Crea

Question

1.Create a table that demonstrates the required constraint (i.e., not null).Create several INSERT statements that test this constraint (i.e., at least one record passes and one fails).

2. Create a table that includes both a field level check and a table level check. Create several INSERT statements that test these constraints (i.e., at least one record passes and one fails). Note that there are two separate constraints here…so make sure you have enough INSERTS to pass and fail all the constraints.

3. Create a table that defines a primary key constraint. Create several INSERT statements that test this constraint (i.e., at least one record passes and one fails).

4. Create a table that defines a unique constraint. Create several INSERT statements that test this constraint (i.e., at least one record passes and one fails).

5.Create 3 tables: Students, Classes, andClassRegistration. Create a primary key in both the Students and Classes tables (as well as other meaningful columns). In the ClassRegistration table, use the primary keys from each of the other tables to indicate which students take which classes. Define these as foreign keys to the other tables. Specify the foreign constraints in such a way that, if a student is deleted from the Students table, all corresponding records in the ClassRegistration table are deleted and, deleting a class from the Classes table is not allowed if that class is referenced in the ClassRegistration table. Create at least 3 records in each table. Test that your constraints are working properly by trying to delete a student and then also trying to delete a class.

Explanation / Answer

==============================================================================

CREATE TABLE `Student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email_id` varchar(255) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`gender` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_id` (`email_id`,`phone_number`)
);

insert into Student(email_id,phone_number,gender) values('adssdfs@gmail.com','234234234234',null);
insert into Student(id,email_id,phone_number) values(3,'adssdfs@gmail.com','234234234234');

==============================================================================

CREATE TABLE `Student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email_id` varchar(255) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`gender` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_id` (`email_id`,`phone_number`)
);

insert into Student(email_id,phone_number,gender) values('adssdfs@gmail.com','234234234234',null);
insert into Student(id,email_id,phone_number) values(3,'adssdfs@gmail.com','234234234234');
insert into Student(id,email_id,phone_number) values('adssdfs@gmail.com','234234234234');

==============================================================================

CREATE TABLE `Student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email_id` varchar(255) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`gender` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_id` (`email_id`,`phone_number`)
);

insert into Student(email_id,phone_number) values('adssdfs@gmail.com','234234234234');
insert into Student(id,email_id,phone_number) values(1,'adssdfs@gmail.com','234234234234');

==============================================================================

CREATE TABLE `Student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email_id` varchar(255) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`gender` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_id` (`email_id`,`phone_number`)
);

insert into Student(email_id,phone_number,gender) values('adssdfs@gmail.com','234234234234');
insert into Student(id,email_id,phone_number) values(3,'adssdfs@gmail.com','234234234234');

==============================================================================

CREATE TABLE `Student` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email_id` varchar(255) DEFAULT NULL,
`phone_number` varchar(255) DEFAULT NULL,
`gender` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email_id` (`email_id`,`phone_number`)
);

CREATE TABLE `Classes` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `ClassRegistration` (
`student_id` bigint(20) NOT NULL,
`class_id` bigint(20) NOT NULL,
CONSTRAINT class_ibfk_1
FOREIGN KEY (student_id)
REFERENCES Student (id)
ON DELETE CASCADE,
CONSTRAINT class_ibfk_2
FOREIGN KEY (class_id)
REFERENCES Classes (id)
);

insert into Student(email_id,phone_number) values('adssasdsadddfs@gmail.com','234234234234');
insert into Classes(name) values('Maths');
insert into Classes(name) values('Biology');
insert into ClassRegistration values(1,2),(1,2);
insert into ClassRegistration values(2,1),(2,2);

mysql> select * from ClassRegistration;
+------------+----------+
| student_id | class_id |
+------------+----------+
|          1 |        2 |
|          1 |        2 |
|          2 |        1 |
|          2 |        2 |
+------------+----------+
4 rows in set (0.00 sec)

mysql> delete from Student where id = 1;
Query OK, 1 row affected (0.05 sec)

mysql> select * from ClassRegistration;
+------------+----------+
| student_id | class_id |
+------------+----------+
|          2 |        1 |
|          2 |        2 |
+------------+----------+
2 rows in set (0.00 sec)

mysql> delete from Classes where id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`ClassRegistration`, CONSTRAINT `class_ibfk_2` FOREIGN KEY (`class_id`) REFERENCES `Classes` (`id`))

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.