CODEWITHSUNDEEP
sqloracle-database
Abiodun Adeoye
Abiodun Adeoye

Reputation: 1095

issues with DEFAULT values

create Table payment_record(
payment_record_id int NOT NULL,
payment_number Varchar(5) NOT NULL UNIQUE,
recieved_from_client Number (8,2) NOT NULL DEFAULT 0.00,
Paid_to_client Number (8,2) NOT NULL DEFAULT 0.00,
payment_date Date NOT NULL,
PRIMARY KEY(payment_record_id));

outcome

SQL> create Table payment_record(
 2  payment_record_id int NOT NULL,
 3  payment_number Varchar(5) NOT NULL UNIQUE,
 4  recieved_from_client Number (8,2) NOT NULL DEFAULT 0.00,
 5  Paid_to_client Number (8,2) NOT NULL DEFAULT 0.00,
 6  payment_date Date NOT NULL,
 7  PRIMARY KEY(payment_record_id));
 recieved_from_client Number (8,2) NOT NULL DEFAULT 0.00,
                                       *
ERROR at line 4:
ORA-00907: missing right parenthesis

Please what am I getting wrong? Am trying to create a table where defaultly the 

Paid_to_client 
recieved_from_client

can produce 0.00 if values as not assign to it

Upvotes: 2

Views: 52

Answers (3)

Hisham
Hisham

Reputation: 455

You have to use default before column constraint for example not null. For reference

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

DEFAULT goes before NOT NULL:

create Table payment_record(
payment_record_id int NOT NULL,
payment_number Varchar(5) NOT NULL UNIQUE,
recieved_from_client Number (8,2) DEFAULT 0.00 NOT NULL,
Paid_to_client Number (8,2) DEFAULT 0.00 NOT NULL ,
payment_date Date NOT NULL,
PRIMARY KEY(payment_record_id));

Upvotes: 3

Mureinik
Mureinik

Reputation: 311073

The default clause should come before the NOT NULL specification:

SQL> create Table payment_record(
 2  payment_record_id int NOT NULL,
 3  payment_number Varchar(5) NOT NULL UNIQUE,
 4  recieved_from_client Number (8,2) DEFAULT 0.00 NOT NULL,
 5  Paid_to_client Number (8,2) DEFAULT 0.00 NOT NULL,
 6  payment_date Date NOT NULL,
 7  PRIMARY KEY(payment_record_id));

Upvotes: 3

Related Questions