Styjun

What does "tea juice" mean in this context?

Can't connect to Internet in bash using Mac OS

Why would Lupin kill Pettigrew?

Preserving culinary oils

Could I be denied entry into Ireland due to medical and police situations during a previous UK visit?

Looking after a wayward brother in mother's will

Why does the UK have more political parties than the US?

If a massive object like Jupiter flew past the Earth how close would it need to come to pull people off of the surface?

If a problem only occurs randomly once in every N times on average, how many tests do I have to perform to be certain that it's now fixed?

Self-Preservation: How to DM NPCs that Love Living?

What is the difference between nullifying your vote and not going to vote at all?

Mother abusing my finances

What are the benefits of cryosleep?

60s (or earlier) short story where each colony has one person who doesn't connect well with others who is there for being able to absorb knowledge

How should I push back against my job assigning "homework"?

Points within polygons in different projections

Understanding STM32 datasheet regarding decoupling capacitors

Is a hash a zero-knowledge proof?

Do creatures all have the same statistics upon being reanimated via the Animate Dead spell?

What does the behaviour of water on the skin of an aircraft in flight tell us?

Strange math syntax in old basic listing

Differences between “pas vrai ?”, “c’est ça ?”, “hein ?”, and “n’est-ce pas ?”

etoolbox: AtBeginEnvironment is not At Begin Environment

The qvolume of an integer

Prevent Insertion if Check Fails

CHECK constraint in MySQL is not workingHow can I prevent SQL injection in PHP?Complex mySQL Query (again)GROUP BY vs INSERT IGNOREVb.net error for query and databaseHow to do Insert in many-to-many scenario while working with entity framework for mysql?mysql relation slowReferencing table with generated primary keysJOIN Query In MYSQL and PHPGetting org.hibernate.exception.SQLGrammarException while inserting objects through hibernateCreate Entity Class for multiple table joins

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

I want to prevent insertion in my MySQL database when the Check condition is not true. For example, in the following MySQL query I have added a check constraint that Gender can only be "M" or "F" but still, I am able to insert "T".

CREATE TABLE STUDENT
(S_FIRST VARCHAR(10) NOT NULL, 
S_LAST VARCHAR(10) NOT NULL, 
GENDER VARCHAR(1) NOT NULL CHECK (GENDER="M" OR GENDER="F"), 
S_EMAIL VARCHAR(50) NOT NULL CHECK (S_EMAIL="%@%.COM"), 
S_PHONE VARCHAR(10) NOT NULL CHECK (S_PHONE<9999999999), 
DATE_OF_BIRTH DATE NOT NULL, 
S_ID INT(3) PRIMARY KEY AUTO_INCREMENT);


INSERT INTO STUDENT VALUE("ABNISH","NIRAJ","T","ABNISHNIRAJ10@GMAIL.COM","7062123799",'1996-10-19',NULL); 

-- the Second query should not work since the Gender is "T"

mysql

share|improve this question

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  which version of mysql are you using?
  
  – Derviş Kayımbaşıoğlu
  Mar 24 at 10:08
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Possible duplicate of CHECK constraint in MySQL is not working
  
  – Ravi
  Mar 24 at 10:15
  

  
  
  
  

add a comment | 

0

I want to prevent insertion in my MySQL database when the Check condition is not true. For example, in the following MySQL query I have added a check constraint that Gender can only be "M" or "F" but still, I am able to insert "T".

CREATE TABLE STUDENT
(S_FIRST VARCHAR(10) NOT NULL, 
S_LAST VARCHAR(10) NOT NULL, 
GENDER VARCHAR(1) NOT NULL CHECK (GENDER="M" OR GENDER="F"), 
S_EMAIL VARCHAR(50) NOT NULL CHECK (S_EMAIL="%@%.COM"), 
S_PHONE VARCHAR(10) NOT NULL CHECK (S_PHONE<9999999999), 
DATE_OF_BIRTH DATE NOT NULL, 
S_ID INT(3) PRIMARY KEY AUTO_INCREMENT);


INSERT INTO STUDENT VALUE("ABNISH","NIRAJ","T","ABNISHNIRAJ10@GMAIL.COM","7062123799",'1996-10-19',NULL); 

-- the Second query should not work since the Gender is "T"

mysql

share|improve this question

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  which version of mysql are you using?
  
  – Derviş Kayımbaşıoğlu
  Mar 24 at 10:08
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Possible duplicate of CHECK constraint in MySQL is not working
  
  – Ravi
  Mar 24 at 10:15
  

  
  
  
  

add a comment | 

I want to prevent insertion in my MySQL database when the Check condition is not true. For example, in the following MySQL query I have added a check constraint that Gender can only be "M" or "F" but still, I am able to insert "T".

CREATE TABLE STUDENT
(S_FIRST VARCHAR(10) NOT NULL, 
S_LAST VARCHAR(10) NOT NULL, 
GENDER VARCHAR(1) NOT NULL CHECK (GENDER="M" OR GENDER="F"), 
S_EMAIL VARCHAR(50) NOT NULL CHECK (S_EMAIL="%@%.COM"), 
S_PHONE VARCHAR(10) NOT NULL CHECK (S_PHONE<9999999999), 
DATE_OF_BIRTH DATE NOT NULL, 
S_ID INT(3) PRIMARY KEY AUTO_INCREMENT);


INSERT INTO STUDENT VALUE("ABNISH","NIRAJ","T","ABNISHNIRAJ10@GMAIL.COM","7062123799",'1996-10-19',NULL); 

-- the Second query should not work since the Gender is "T"

mysql

share|improve this question

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

CREATE TABLE STUDENT
(S_FIRST VARCHAR(10) NOT NULL, 
S_LAST VARCHAR(10) NOT NULL, 
GENDER VARCHAR(1) NOT NULL CHECK (GENDER="M" OR GENDER="F"), 
S_EMAIL VARCHAR(50) NOT NULL CHECK (S_EMAIL="%@%.COM"), 
S_PHONE VARCHAR(10) NOT NULL CHECK (S_PHONE<9999999999), 
DATE_OF_BIRTH DATE NOT NULL, 
S_ID INT(3) PRIMARY KEY AUTO_INCREMENT);


INSERT INTO STUDENT VALUE("ABNISH","NIRAJ","T","ABNISHNIRAJ10@GMAIL.COM","7062123799",'1996-10-19',NULL); 

-- the Second query should not work since the Gender is "T"

share|improve this question

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

share|improve this question

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

asked Mar 24 at 9:52

Pankaj KumarPankaj Kumar

6311

1 Answer
1

active

oldest

votes

0

Check constraints are introduced in MySql Version 8.

Alternative approach is to use triggers

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
 IF NEW.GENDER NOT IN ('F', 'M') THEN
 SIGNAL SQLSTATE '12345'
 SET MESSAGE_TEXT := 'check constraint on GENDER failed';
 END IF;
END$$ 
DELIMITER ;

share|improve this answer

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55322534%2fprevent-insertion-if-check-fails%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Check constraints are introduced in MySql Version 8.

Alternative approach is to use triggers

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
 IF NEW.GENDER NOT IN ('F', 'M') THEN
 SIGNAL SQLSTATE '12345'
 SET MESSAGE_TEXT := 'check constraint on GENDER failed';
 END IF;
END$$ 
DELIMITER ;

share|improve this answer

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042

add a comment | 

0

Check constraints are introduced in MySql Version 8.

Alternative approach is to use triggers

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
 IF NEW.GENDER NOT IN ('F', 'M') THEN
 SIGNAL SQLSTATE '12345'
 SET MESSAGE_TEXT := 'check constraint on GENDER failed';
 END IF;
END$$ 
DELIMITER ;

share|improve this answer

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042

add a comment | 

Check constraints are introduced in MySql Version 8.

Alternative approach is to use triggers

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
 IF NEW.GENDER NOT IN ('F', 'M') THEN
 SIGNAL SQLSTATE '12345'
 SET MESSAGE_TEXT := 'check constraint on GENDER failed';
 END IF;
END$$ 
DELIMITER ;

share|improve this answer

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042

DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Test`
FOR EACH ROW
BEGIN
 IF NEW.GENDER NOT IN ('F', 'M') THEN
 SIGNAL SQLSTATE '12345'
 SET MESSAGE_TEXT := 'check constraint on GENDER failed';
 END IF;
END$$ 
DELIMITER ;

share|improve this answer

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042

answered Mar 24 at 10:11

Derviş KayımbaşıoğluDerviş Kayımbaşıoğlu

16k22042