Scribd
Upload
17 views8 pages

Constarints

The document contains a series of MySQL commands for creating and managing tables in a database named 'extra'. It includes the creation of tables for customers, employees, students, courses, departments, and voters, along with various insertions and error handling for duplicate entries and constraints. The document also demonstrates the use of foreign keys and the structure of the tables created.

Uploaded by

likithasaipeddinti
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
17 views8 pages

Constarints

The document contains a series of MySQL commands for creating and managing tables in a database named 'extra'. It includes the creation of tables for customers, employees, students, courses, departments, and voters, along with various insertions and error handling for duplicate entries and constraints. The document also demonstrates the use of foreign keys and the structure of the tables created.

Uploaded by

likithasaipeddinti
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

mysql> use extra;

Database changed
mysql> show tables;
+-----------------+
| Tables_in_extra |
+-----------------+
| book |
+-----------------+
1 row in set (0.00 sec)

mysql> create table customer(c_id int not null,c_name varchar(30) not null);
Query OK, 0 rows affected (0.12 sec)

mysql> desc customer;


+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| c_id | int | NO | | NULL | |
| c_name | varchar(30) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

mysql> insert into customer values();


ERROR 1364 (HY000): Field 'c_id' doesn't have a default value
mysql> create table employee(emp_id int unsigned not null,emp_name
varchar(30),email varchar(30) default '@[Link]');
Query OK, 0 rows affected (0.04 sec)

mysql> insert into employee(emp_id,emp_name) values(1,'likitha');


Query OK, 1 row affected (0.02 sec)

mysql> select * from employee;


+--------+----------+------------+
| emp_id | emp_name | email |
+--------+----------+------------+
| 1 | likitha | @[Link] |
+--------+----------+------------+
1 row in set (0.00 sec)

mysql> create table student(st_id int unsigned not null,std_name varchar(55) not
null default 'not mentioned');
Query OK, 0 rows affected (0.04 sec)

mysql> insert into student(std_id) values(1);


ERROR 1054 (42S22): Unknown column 'std_id' in 'field list'
mysql> insert into student(st_id) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;


+-------+---------------+
| st_id | std_name |
+-------+---------------+
| 1 | not mentioned |
+-------+---------------+
1 row in set (0.00 sec)

mysql> insert into student(st_id,std_name) values (2,null);


ERROR 1048 (23000): Column 'std_name' cannot be null
mysql> drop table student;
Query OK, 0 rows affected (0.05 sec)

mysql> create table student(st_id int unsigned unique key,std_name varchar(55) not
null default 'not mentioned');
Query OK, 0 rows affected (0.06 sec)

mysql> desc student;


+----------+--------------+------+-----+---------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------------+-------+
| st_id | int unsigned | YES | UNI | NULL | |
| std_name | varchar(55) | NO | | not mentioned | |
+----------+--------------+------+-----+---------------+-------+
2 rows in set (0.00 sec)

mysql> insert into student(st_id) values(1);


Query OK, 1 row affected (0.01 sec)

mysql> insert into student(st_id) values(1);


ERROR 1062 (23000): Duplicate entry '1' for key 'student.st_id'
mysql> select * from student;
+-------+---------------+
| st_id | std_name |
+-------+---------------+
| 1 | not mentioned |
+-------+---------------+
1 row in set (0.00 sec)

mysql> insert into student(st_id,std_name) values (2,null);


ERROR 1048 (23000): Column 'std_name' cannot be null
mysql> insert into student(st_id,std_name) values (null,'likitha');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student(st_id,std_name) values (null,'likitha');


Query OK, 1 row affected (0.01 sec)

mysql> select * student;


ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'student'
at line 1
mysql> select * from student;
+-------+---------------+
| st_id | std_name |
+-------+---------------+
| 1 | not mentioned |
| NULL | likitha |
| NULL | likitha |
+-------+---------------+
3 rows in set (0.00 sec)

mysql> --primary key


-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '--
primary key' at line 1
mysql> drop table student;
Query OK, 0 rows affected (0.03 sec)
Microsoft Windows [Version 10.0.26100.4652]
(c) Microsoft Corporation. All rights reserved.

mysql> use extra;


Database changed
mysql> create table course(course_id int unsigned primary key , course_name
varchar(50) unique key not null,course_fee bigint);
Query OK, 0 rows affected (0.17 sec)

mysql> desc course;


+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| course_id | int unsigned | NO | PRI | NULL | |
| course_name | varchar(50) | NO | UNI | NULL | |
| course_fee | bigint | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.04 sec)

mysql> insert into course values(


-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> insert into course values(101,'python',10000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values(101,'HTML',10000);


ERROR 1062 (23000): Duplicate entry '101' for key '[Link]'
mysql> insert into course values(102,'python',10000);
ERROR 1062 (23000): Duplicate entry 'python' for key 'course.course_name'
mysql> insert into course values(103,'HTML',10000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from course;


+-----------+-------------+------------+
| course_id | course_name | course_fee |
+-----------+-------------+------------+
| 101 | python | 10000 |
| 103 | HTML | 10000 |
+-----------+-------------+------------+
2 rows in set (0.00 sec)

mysql> create table course1(course_id int unsigned primary key , course_name


varchar(50) primary key,course_fee bigint);
ERROR 1068 (42000): Multiple primary key defined
mysql> select * from course;
+-----------+-------------+------------+
| course_id | course_name | course_fee |
+-----------+-------------+------------+
| 101 | python | 10000 |
| 103 | HTML | 10000 |
+-----------+-------------+------------+
2 rows in set (0.00 sec)

mysql> insert into course values(102,'MYSQL',40000);


Query OK, 1 row affected (0.01 sec)
mysql> insert into course values(104,'css',120000);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values(105,'java',1500000);


Query OK, 1 row affected (0.00 sec)

mysql> select * from course;


+-----------+-------------+------------+
| course_id | course_name | course_fee |
+-----------+-------------+------------+
| 101 | python | 10000 |
| 102 | MYSQL | 40000 |
| 103 | HTML | 10000 |
| 104 | css | 120000 |
| 105 | java | 1500000 |
+-----------+-------------+------------+
5 rows in set (0.00 sec)

mysql> insert into course values(106,'ui/ux graphics',11000);


Query OK, 1 row affected (0.01 sec)

mysql> select * from course;


+-----------+----------------+------------+
| course_id | course_name | course_fee |
+-----------+----------------+------------+
| 101 | python | 10000 |
| 102 | MYSQL | 40000 |
| 103 | HTML | 10000 |
| 104 | css | 120000 |
| 105 | java | 1500000 |
| 106 | ui/ux graphics | 11000 |
+-----------+----------------+------------+
6 rows in set (0.00 sec)

mysql> create table deptartment(dept_id int unsigned primary key


auto_increment,dept_name varchar(50) unique key not null);
Query OK, 0 rows affected (0.04 sec)

mysql> desc department


-> ;
ERROR 1146 (42S02): Table '[Link]' doesn't exist
mysql> desc deptartment;
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| dept_id | int unsigned | NO | PRI | NULL | auto_increment |
| dept_name | varchar(50) | NO | UNI | NULL | |
+-----------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into deptartment(dept_name) values('Technical');


Query OK, 1 row affected (0.01 sec)

mysql> insert into deptartment(dept_name) values('non-technical');


Query OK, 1 row affected (0.01 sec)

mysql> select * from deptartment;


+---------+---------------+
| dept_id | dept_name |
+---------+---------------+
| 2 | non-technical |
| 1 | Technical |
+---------+---------------+
2 rows in set (0.00 sec)

mysql> insert into deptartment(dept_id,dept_name) values('5,'field work');


'> ;
'> ^C
mysql> insert into deptartment(dept_id,dept_name) values('5','field work');
Query OK, 1 row affected (0.01 sec)

mysql> select * from deptartment;


+---------+---------------+
| dept_id | dept_name |
+---------+---------------+
| 5 | field work |
| 2 | non-technical |
| 1 | Technical |
+---------+---------------+
3 rows in set (0.00 sec)

mysql> insert into deptartment(dept_name) values('Technicalwork');


Query OK, 1 row affected (0.01 sec)

mysql> select * from deptartment;


+---------+---------------+
| dept_id | dept_name |
+---------+---------------+
| 5 | field work |
| 2 | non-technical |
| 1 | Technical |
| 6 | Technicalwork |
+---------+---------------+
4 rows in set (0.00 sec)

mysql> insert into deptartment(dept_name) values('Technicalwork');


ERROR 1062 (23000): Duplicate entry 'Technicalwork' for key 'deptartment.dept_name'
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> create table voter(voter_id int unsigned primary key,voter_age tinyint
check(voter_age>=18));
Query OK, 0 rows affected (0.05 sec)

mysql> desc voter;


+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| voter_id | int unsigned | NO | PRI | NULL | |
| voter_age | tinyint | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> show create table voter;
+-------
+----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
---------------------------------------------------------------------------------+
| Tablecreate table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not null)
|
+-------
+----------------------------------------------------------------------------------
--------------------------------------------------create table emp(emp_id int
unsigned int primary key,emp_name varchar(50) not null,emp_salary mediumint
unsigned not null,date_of_joining date not null)oter` (
`voter_id` int unsigned NOT NULL,
`votecreate table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not null)
CONSTcreate table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not
null)-----------
-> _ai_ci |
-> -
+----------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-> create table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not null)
-> n set (0.01 sec)
->
-> insert into voter values(1,20);
-> K, 1 row affected (0.01 sec)
->
-> create table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not null)
-> 819 (HY000): Check constraint 'voter_chk_1' is violated.
-> select * from voter;
-> ----+-----------+
-> _id | voter_age |
-> ----+-----------+
-> 1 | 20 |
-> ----+-----------+
-> n set (0.00 sec)
->
-> desc deptartment;
-> -----+--------------+------+-----+---------+----------------+
-> | Type | Null | Key | Default | Extra |
-> -----+--------------+------+-----+---------+----------------+
-> id | int unsigned | NO | PRI | NULL | auto_increment |
-> name | varchar(50) | NO | UNI | NULL | |
-> -----+--------------+------+-----+---------+----------------+
-> in set (0.00 sec)
->
-> create table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not null)
-> ^C
mysql>
mysql> create table emp(emp_id int unsigned int primary key,emp_name varchar(50)
not null,emp_salary mediumint unsigned not null,date_of_joining date not
null,dept_id int unsigned,foregin key(dept_id) references deptartment(dept_id));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'int
primary key,emp_name varchar(50) not null,emp_salary mediumint unsigned not ' at
line 1
mysql> create table emp(emp_id int unsigned primary key,emp_name varchar(50) not
null,emp_salary mediumint unsigned not null,date_of_joining date not null,dept_id
int unsigned,foregin key(dept_id) references deptartment(dept_id));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'key(dept_id) references deptartment(dept_id))' at line 1
mysql> create table emp(emp_id int unsigned primary key,emp_name varchar(50) not
null,emp_salary mediumint unsigned not null,date_of_joining date not null,dept_id
int unsigned,foreign key(dept_id) references deptartment(dept_id));
Query OK, 0 rows affected (0.06 sec)

mysql> desc emp;


+-----------------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------------+------+-----+---------+-------+
| emp_id | int unsigned | NO | PRI | NULL | |
| emp_name | varchar(50) | NO | | NULL | |
| emp_salary | mediumint unsigned | NO | | NULL | |
| date_of_joining | date | NO | | NULL | |
| dept_id | int unsigned | YES | MUL | NULL | |
+-----------------+--------------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> select * from deptartment;


+---------+---------------+
| dept_id | dept_name |
+---------+---------------+
| 5 | field work |
| 2 | non-technical |
| 1 | Technical |
| 6 | Technicalwork |
+---------+---------------+
4 rows in set (0.00 sec)

mysql> insert into employee(emp_id,emp_name,emp_salary,date_of_joining,dept_id)


values(101,'likitha',300000,'2022-11-22',1);
ERROR 1054 (42S22): Unknown column 'emp_salary' in 'field list'
mysql> insert into employee(emp_id,emp_name,emp_salary,date_of_joining,dept_id)
values(101,'likitha',3000,'2022-11-22',1);)
ERROR 1054 (42S22): Unknown column 'emp_salary' in 'field list'
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near ')' at
line 1
mysql> insert into employee(emp_id,emp_name,emp_salary,date_of_joining,dept_id)
values(101,'likitha',3000,'2022-11-22',1);
ERROR 1054 (42S22): Unknown column 'emp_salary' in 'field list'
mysql> insert into emp(emp_id,emp_name,emp_salary,date_of_joining,dept_id)
values(101,'likitha',3000,'2022-11-22',1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into emp(emp_id,emp_name,emp_salary,date_of_joining,dept_id)


values(101,'likitha',3000,'2022-11-22',7);
ERROR 1062 (23000): Duplicate entry '101' for key '[Link]'
mysql> insert into emp(emp_id,emp_name,emp_salary,date_of_joining,dept_id)
values(102,'likitha',3000,'2022-11-22',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`extra`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES
`deptartment` (`dept_id`))
mysql>

You might also like