Mysql Query ....constraints

  • abhinav_8182
  • Beginner
  • Beginner
  • User avatar
  • Posts: 41

Post 3+ Months Ago

hi


1.TABLE Publishers has the following description:

select * from Publishers;
+-------+------------+------------+-------+---------+---------+
| PubId | PubName | City | State | Country | PinCode |
+-------+------------+------------+-------+---------+---------+
| 50 | Ramona | Dallas | TX | USA | 110034 |
| 40 | Five Lakes | Chicago | IL | USA | 110076 |
| 30 | Hardely | Berkeley | CA | USA | 110020 |
| 10 | New Moon | Boston | MA | USA | 110001 |
| 20 | Binnet | Washington | DC | USA | 110011 |
| 60 | GGG&G | Munchen | | USA | 110067 |
| 70 | Scooteny | New York | NY | USA | 110045 |
| 80 | Lucerne | Paris | | USA | 110081 |
| 90 | aa | sss | TR | USA | 110090 |
+-------+------------+------------+-------+---------+---------+
9 rows in set (0.16 sec)


2.I want to put a constraint on PinCode & City coloumn.
According to the Constraint1 if the user enter the value of PinCode as 110090,then he will
receive ERROR msg.
According to the Constraint2 if the user enter the value of City other than NewYork,Dallas & Boston,then he will receive ERROR msg.



alter table Publishers
-> ADD CONSTRAINT chkPin CHECK( PinCode!=110090);
Query OK, 9 rows affected (0.08 sec)
Records: 9 Duplicates: 0 Warnings: 0

alter table Publishers
-> ADD CONSTRAINT chkCity CHECK(City IN("New York","Dallas","Boston"));
Query OK, 9 rows affected (0.05 sec)
Records: 9 Duplicates: 0 Warnings: 0



3.User entered Paris for the City coloumn & 110090 as the value of Pincode.According to the
constraints, user should receive an error msg.
Instead of getting any error msg these values get inserted into the table Publishers.



insert into Publishers values(100,"Lucerne","Paris","","USA",110090);
Query OK, 1 row affected (0.00 sec)

mysql> select * from Publishers;
+-------+------------+------------+-------+---------+---------+
| PubId | PubName | City | State | Country | PinCode |
+-------+------------+------------+-------+---------+---------+
| 50 | Ramona | Dallas | TX | USA | 110034 |
| 40 | Five Lakes | Chicago | IL | USA | 110076 |
| 30 | Hardely | Berkeley | CA | USA | 110020 |
| 10 | New Moon | Boston | MA | USA | 110001 |
| 20 | Binnet | Washington | DC | USA | 110011 |
| 60 | GGG&G | Munchen | | USA | 110067 |
| 70 | Scooteny | New York | NY | USA | 110045 |
| 80 | Lucerne | Paris | | USA | 110081 |
| 90 | aa | sss | TR | USA | 110090 |
| 100 | Lucerne | Paris | | USA | 110090 |
+-------+------------+------------+-------+---------+---------+
10 rows in set (0.00 sec)
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

Post Information

  • Total Posts in this topic: 1 post
  • Users browsing this forum: No registered users and 92 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.