Ufdjrw

Are objects structures and/or vice versa?

How to answer pointed "are you quitting" questioning when I don't want them to suspect

Was there ever an axiom rendered a theorem?

Denied boarding due to overcrowding, Sparpreis ticket. What are my rights?

"My colleague's body is amazing"

Shall I use personal or official e-mail account when registering to external websites for work purpose?

Hosting Wordpress in a EC2 Load Balanced Instance

Is Fable (1996) connected in any way to the Fable franchise from Lionhead Studios?

Is ipsum/ipsa/ipse a third person pronoun, or can it serve other functions?

Is Social Media Science Fiction?

Are cabin dividers used to "hide" the flex of the airplane?

Can I legally use front facing blue light in the UK?

How to manage monthly salary

Pristine Bit Checking

LWC and complex parameters

Ideas for 3rd eye abilities

COUNT(*) or MAX(id) - which is faster?

aging parents with no investments

I see my dog run

Does a dangling wire really electrocute me if I'm standing in water?

What do the Banks children have against barley water?

How did the USSR manage to innovate in an environment characterized by government censorship and high bureaucracy?

Why was the "bread communication" in the arena of Catching Fire left out in the movie?

A poker game description that does not feel gimmicky

Error while using CHECK clause in SQL (MS access)

How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerCan we have the table name as “option” in MySQL?Parameterize an SQL IN clauseSQL join: where clause vs. on clauseInserting multiple rows in a single SQL query?How do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableHow to import an SQL file using the command line in MySQL?sqlstates42000 Syntax error or access violation: 1075

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

0

I am using MS Access. While I was creating a table:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 PRIMARY KEY(ID),
 check(Age >= 17)
 );

with this code, it is showing error message with

Syntax error in field definition

Can anyone please help me with that? Thanks.

sql ms-access

share|improve this question

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Storing age is usually a bad plan to begin with - because you know that within a year, that data will be incorrect. DOB, on the other hand, doesn't tend to change and age can easily be derived from it.
  
  – Damien_The_Unbeliever
  Mar 8 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Start with identifying the problem line. Remove one row at the time and see when the problem disappears. Divide and qonquer.
  
  – jarlh
  Mar 8 at 7:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "PRIMARY KEY(ID),", you have no ID column, but a StudentID.
  
  – jarlh
  Mar 8 at 7:19
  

  
  
  
  

add a comment | 

0

I am using MS Access. While I was creating a table:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 PRIMARY KEY(ID),
 check(Age >= 17)
 );

with this code, it is showing error message with

Syntax error in field definition

Can anyone please help me with that? Thanks.

sql ms-access

share|improve this question

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Storing age is usually a bad plan to begin with - because you know that within a year, that data will be incorrect. DOB, on the other hand, doesn't tend to change and age can easily be derived from it.
  
  – Damien_The_Unbeliever
  Mar 8 at 7:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Start with identifying the problem line. Remove one row at the time and see when the problem disappears. Divide and qonquer.
  
  – jarlh
  Mar 8 at 7:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  "PRIMARY KEY(ID),", you have no ID column, but a StudentID.
  
  – jarlh
  Mar 8 at 7:19
  

  
  
  
  

add a comment | 

I am using MS Access. While I was creating a table:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 PRIMARY KEY(ID),
 check(Age >= 17)
 );

with this code, it is showing error message with

Syntax error in field definition

Can anyone please help me with that? Thanks.

sql ms-access

share|improve this question

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 PRIMARY KEY(ID),
 check(Age >= 17)
 );

share|improve this question

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

share|improve this question

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

edited Mar 8 at 8:27

CodeNotFound

13.6k53752

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

asked Mar 8 at 7:15

Stat_prob_001Stat_prob_001

1115

1 Answer
1

active

oldest

votes

2

For Access, you have to introduce your PK and CHECK constraints in CONSTRAINT clauses. Something like:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 CONSTRAINT PK_student PRIMARY KEY(StudentID),
 CONSTRAINT CK_student_age check(Age >= 17)
 );

(Also fixed column name in PK, thanks to jarlh)

share|improve this answer

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  this is also showing error with error message "Syntax error in CONSTRAINT clause"
  
  – Stat_prob_001
  Mar 8 at 7:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You'll have to execute this over an OLEDB connection or turn on SQL Server compatible syntax for it to work.
  
  – Erik A
  Mar 8 at 7:54
  

  
  
  
  

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%2f55058437%2ferror-while-using-check-clause-in-sql-ms-access%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

For Access, you have to introduce your PK and CHECK constraints in CONSTRAINT clauses. Something like:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 CONSTRAINT PK_student PRIMARY KEY(StudentID),
 CONSTRAINT CK_student_age check(Age >= 17)
 );

(Also fixed column name in PK, thanks to jarlh)

share|improve this answer

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  this is also showing error with error message "Syntax error in CONSTRAINT clause"
  
  – Stat_prob_001
  Mar 8 at 7:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You'll have to execute this over an OLEDB connection or turn on SQL Server compatible syntax for it to work.
  
  – Erik A
  Mar 8 at 7:54
  

  
  
  
  

add a comment | 

2

For Access, you have to introduce your PK and CHECK constraints in CONSTRAINT clauses. Something like:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 CONSTRAINT PK_student PRIMARY KEY(StudentID),
 CONSTRAINT CK_student_age check(Age >= 17)
 );

(Also fixed column name in PK, thanks to jarlh)

share|improve this answer

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  this is also showing error with error message "Syntax error in CONSTRAINT clause"
  
  – Stat_prob_001
  Mar 8 at 7:26
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You'll have to execute this over an OLEDB connection or turn on SQL Server compatible syntax for it to work.
  
  – Erik A
  Mar 8 at 7:54
  

  
  
  
  

add a comment | 

For Access, you have to introduce your PK and CHECK constraints in CONSTRAINT clauses. Something like:

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 CONSTRAINT PK_student PRIMARY KEY(StudentID),
 CONSTRAINT CK_student_age check(Age >= 17)
 );

(Also fixed column name in PK, thanks to jarlh)

share|improve this answer

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347

CREATE TABLE student(
 StudentID INT NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Age INT NOT NULL,
 GENDER VARCHAR(9),
 CONSTRAINT PK_student PRIMARY KEY(StudentID),
 CONSTRAINT CK_student_age check(Age >= 17)
 );

share|improve this answer

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347

answered Mar 8 at 7:20

Damien_The_UnbelieverDamien_The_Unbeliever

198k17256347