CODEWITHSUNDEEP
oracle-databasesql-insert
Clark
Clark

Reputation: 1

Create and insert table

I made a table for database and the table created but the insert vales are not working.

This is the table 

Create table patient (
Patient_ID Number(9) primary key,
First_name varchar2(15),
Last_name varchar2(10),
Contact number(10),
City varchar2(20),
Doctor_ID Number(9) references Doctor(Doctor_ID));

This is the insert statement 

insert into patient values ('21345', 'John', 'Smith', '111-111-1111', 'NJ');   
insert into patient values ('21346', 'Emily', 'Rose', '222-222-2222', 'LA');  
insert into patient values ('21347', 'Mark', 'Cruise', '333-333-3333', 'NY');
insert into patient values ('21348', 'Bran', 'Stark', '444-444-4444', 'TX');    
insert into patient values ('21349', 'Hailey', 'Wraith', '555-555-5555', 'AZ');

I am getting an error saying not enough values.

Upvotes: 0

Views: 51

Answers (2)

Enoch
Enoch

Reputation: 1001

You forgot to add the table column names during the insert so it tries to add all the data your passing to each column in the table. This is a better way of entering your data when it doesn't require you to insert into all table columns.

Insert into `patient` (`table1`, `table2`, `table3`, `table4`, `table5`) values ('21345', 'John', 'Smith', '111-111-1111', 'NJ');

To break down your issue more, you have 6 columns in your table, but the data your passing is 5, it will give you this error because 5 is less than 6. If you don't want to receive this error you need to state each column you are entering into as seen above -- BUT that field will need to be a nullable field.

In this case Doctor_ID is missing

Insert into `patient` (`table1`, `table2`, `table3`, `table4`, `table5`, `table6` ) values ('21345', 'John', 'Smith', '111-111-1111', 'NJ', 'DOCTOR_ID_DATA_HERE');

ANSWER:

Insert into `patient` (`Patient_ID`, `First_name`, `Last_name`, `Contact`, `City`, `Doctor_ID`) values ('2125', 'John', 'Doe', '111-111-1111', 'LA', '30114');

DOCTOR 30114 needs to already exist because you are referencing from another table, please take note!

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

You are only inserting 5 values when your table is expecting 6 (ParentID, First Name, Last Name, Contact, City and Doctor ID)

You need to pass in a value for Doctor_ID

Upvotes: 1

Related Questions