How to write a CREATE TABLE for SQL Server without encountering a syntax error?

Question

CREATE TABLE member 
(
    member_id member(10) 
        constraint member_member_id_pk PRIMARY KEY;
    last_name varchar2(25) 
        constraint member_last_name_nn NOT NULL;
    join_date DATE DEFAULT SYSDATE
        constraint member_join_date_nn NOT NULL;
)

I am not sure what is wrong, I always get missing right parenthesis at line 3.

please change the title of the question. It should be a brief of your question — the_unknown_spirit
– the_unknown_spirit, Commented Jul 10, 2016 at 15:25

marc_s · Accepted Answer · 2016-07-10 14:24:58Z

3

You must not use ; at the end of each line!! Use a , (comma) instead, except on the last line (before the )).

Also, for the NOT NULL, don't define an explicit constraint - on the other hand, for the default value for join_date, you should define an explicit constraint.

And the expression to use for that default constraint should be SYSDATETIME() for T-SQL - not SYSDATE() ....

create table member 
(
    member_id member(10) 
        constraint member_member_id_pk PRIMARY KEY,
    last_name varchar2(25) NOT NULL,
    join_date DATE NOT NULL
        constraint df_member_join_date DEFAULT (SYSDATETIME())
)

And last but not least - this here is the official MSDN documentation for CREATE TABLE where you find all these details - and more - please consult it next time you need to know something like this!

answered Jul 10, 2016 at 14:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Loo Chun Meng Over a year ago

Thank you for prompt reply. Sure , i will consult the documentation first next time.

marc_s Over a year ago

@LooChunMeng: if this answer helped you solve your problem, then please accept this answer. This will show your appreciation for the people who spent their own time to help you.

Collectives™ on Stack Overflow

How to write a CREATE TABLE for SQL Server without encountering a syntax error?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related