Styjun

Making a pikuach nefesh phone call on Yom Kippur - mitsva or something to be avoided?

How much does freezing grapes longer sweeten them more?

FPGA starts working after irrelevant changes, why?

Why is 1.e4 c5 2.Nf3 b6 so unpopular?

As a vegetarian, how can I deal with microwaves smelling of meat and fish?

Is success due to hard work sustainable in academic research?

Should we increase saturation and brightness on CMYK colors?

Let A, B and C be three sets. If A ∈ B and B ⊂ C, is it true that A ⊂ C?. If not, give an example.

How would a race of humanoids with tails design [vehicle] seats?

Use GPLv3 library in a closed system (no software distribution)

Did Bobby Fischer actually write "Bobby Fischer Teaches Chess"

Compress .hex file for micro-controller

Are Changelings immune to the Polymorph spell?

Should I replace the battery terminal clamp if some material is missing?

When and why did the House rules change to permit an inquiry without a vote?

How can I seal 8 inch round holes in my siding?

Does using an img title attribute in addition to the alt attribute help image SEO?

Stamp of electrical department on letter head of recommendation letter

What's the best way to notate this syncopation?

Should I respond to a sabotage accusation e-mail at work?

Reverse Voltage?

Can every type of linear filter be modelled by a convolution?

Options for passes to national parks in Arizona/Utah for 5 people travelling in one car

Why is 10.1.255.255 an invalid broadcast address?

How to set a specific field to not null?

How does database indexing work?How can I prevent SQL injection in PHP?MySQL error: key specification without a key lengthHow do I UPDATE from a SELECT in SQL Server?plpgsql function returns table(..)How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?Best practices for SQL varchar column lengthHow to exit from PostgreSQL command line utility: psqlHow to alter a column and change the default value?Can't declare variable inside function on PostgreSQL

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty
margin-bottom:0;

0

I have a table where two columns show the country and the passport respectively both being type varchar, it also have other columns but are not relevant for the problem, and I want to add a constraint where a specific country may not have a passport. For example, only the country Italy can have a passport or can be set as null and the other countries must have a passport.

I have tried this code:

alter table profesor alter column passport set not null where country != 'Italy'

Which drops the next error:

ERROR: syntax error at or near "where" 
LINE 1: ...able profesor alter column passport set not null where country... 
 ^ 
SQL state: 42601 
Character: 58

sql postgresql pgadmin-4

share|improve this question

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What could possibly be the intention of this code? A column cannot be "occasionally" NOT NULL. It either is or isn't.
  
  – Gordon Linoff
  Mar 28 at 21:42
  

  
  
  
  

add a comment
 | 

0

I have a table where two columns show the country and the passport respectively both being type varchar, it also have other columns but are not relevant for the problem, and I want to add a constraint where a specific country may not have a passport. For example, only the country Italy can have a passport or can be set as null and the other countries must have a passport.

I have tried this code:

alter table profesor alter column passport set not null where country != 'Italy'

Which drops the next error:

ERROR: syntax error at or near "where" 
LINE 1: ...able profesor alter column passport set not null where country... 
 ^ 
SQL state: 42601 
Character: 58

sql postgresql pgadmin-4

share|improve this question

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What could possibly be the intention of this code? A column cannot be "occasionally" NOT NULL. It either is or isn't.
  
  – Gordon Linoff
  Mar 28 at 21:42
  

  
  
  
  

add a comment
 | 

I have a table where two columns show the country and the passport respectively both being type varchar, it also have other columns but are not relevant for the problem, and I want to add a constraint where a specific country may not have a passport. For example, only the country Italy can have a passport or can be set as null and the other countries must have a passport.

I have tried this code:

alter table profesor alter column passport set not null where country != 'Italy'

Which drops the next error:

ERROR: syntax error at or near "where" 
LINE 1: ...able profesor alter column passport set not null where country... 
 ^ 
SQL state: 42601 
Character: 58

sql postgresql pgadmin-4

share|improve this question

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

alter table profesor alter column passport set not null where country != 'Italy'

ERROR: syntax error at or near "where" 
LINE 1: ...able profesor alter column passport set not null where country... 
 ^ 
SQL state: 42601 
Character: 58

share|improve this question

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

share|improve this question

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

asked Mar 28 at 21:32

aristi95aristi95

1022 bronze badges

1 Answer
1

active

oldest

votes

0

I think you want a check constraint, not a not null constraint:

alter table profesor
 add constraint chk_profesor_passport
 check (passport is not null or country = 'Italy');

share|improve this answer

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thak you it worked
  
  – aristi95
  Mar 28 at 21:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can you help me with other question please? My table have a column that show the phone number and I want to add a constraint where this number must be in a specific format like +cod.country-cod.local-num.local. For example: +54-351-4350114. And later I want to add a constraint where num.local must have at least 7 digits.
  
  – aristi95
  Mar 28 at 22:08
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @aristi95 . . . New questions should be asked as questions and not in comments.
  
  – Gordon Linoff
  Mar 28 at 22:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes sorry it is just that I have to wait 90 minutes. But thank you anyways.
  
  – aristi95
  Mar 28 at 22:17
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @aristi95: Be aware that this still allows insert into profesor(passport, country) VALUES (NULL, NULL); as CHECK constraints only prevent rows where the expression evaluates to FALSE.
  
  – Erwin Brandstetter
  Mar 28 at 22:18
  

  
  
  
  

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/4.0/"u003ecc by-sa 4.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%2f55407168%2fhow-to-set-a-specific-field-to-not-null%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

I think you want a check constraint, not a not null constraint:

alter table profesor
 add constraint chk_profesor_passport
 check (passport is not null or country = 'Italy');

share|improve this answer

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thak you it worked
  
  – aristi95
  Mar 28 at 21:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can you help me with other question please? My table have a column that show the phone number and I want to add a constraint where this number must be in a specific format like +cod.country-cod.local-num.local. For example: +54-351-4350114. And later I want to add a constraint where num.local must have at least 7 digits.
  
  – aristi95
  Mar 28 at 22:08
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @aristi95 . . . New questions should be asked as questions and not in comments.
  
  – Gordon Linoff
  Mar 28 at 22:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes sorry it is just that I have to wait 90 minutes. But thank you anyways.
  
  – aristi95
  Mar 28 at 22:17
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @aristi95: Be aware that this still allows insert into profesor(passport, country) VALUES (NULL, NULL); as CHECK constraints only prevent rows where the expression evaluates to FALSE.
  
  – Erwin Brandstetter
  Mar 28 at 22:18
  

  
  
  
  

add a comment
 | 

0

I think you want a check constraint, not a not null constraint:

alter table profesor
 add constraint chk_profesor_passport
 check (passport is not null or country = 'Italy');

share|improve this answer

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thak you it worked
  
  – aristi95
  Mar 28 at 21:58
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can you help me with other question please? My table have a column that show the phone number and I want to add a constraint where this number must be in a specific format like +cod.country-cod.local-num.local. For example: +54-351-4350114. And later I want to add a constraint where num.local must have at least 7 digits.
  
  – aristi95
  Mar 28 at 22:08
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @aristi95 . . . New questions should be asked as questions and not in comments.
  
  – Gordon Linoff
  Mar 28 at 22:12
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  yes sorry it is just that I have to wait 90 minutes. But thank you anyways.
  
  – aristi95
  Mar 28 at 22:17
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @aristi95: Be aware that this still allows insert into profesor(passport, country) VALUES (NULL, NULL); as CHECK constraints only prevent rows where the expression evaluates to FALSE.
  
  – Erwin Brandstetter
  Mar 28 at 22:18
  

  
  
  
  

add a comment
 | 

I think you want a check constraint, not a not null constraint:

alter table profesor
 add constraint chk_profesor_passport
 check (passport is not null or country = 'Italy');

share|improve this answer

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges

alter table profesor
 add constraint chk_profesor_passport
 check (passport is not null or country = 'Italy');

share|improve this answer

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges

answered Mar 28 at 21:43

Gordon LinoffGordon Linoff

867k3838 gold badges360360 silver badges459459 bronze badges