When I run this code, I receive a very vague syntax error: database: syntax error at or near "(". I am unable to find where this syntax error would be.
I have also been told that animal_adoption_history is not an associative entity when it was designed as one.
What have I done wrong when writing it?
The code:
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS animal;
DROP TABLE IF EXISTS animal_adoption_history;
create table customer (
customer_id CHAR(9) NOT NULL,
c_first_name VARCHAR(25),
c_last_name VARCHAR(50),
c_gender CHAR(1),
c_phone_number VARCHAR(20),
c_email_address VARCHAR(45),
c_date_of_birth DATE,
c_address_number VARCHAR(10),
c_street_name VARCHAR(30),
c_city VARCHAR(50),
c_state CHAR(3),
c_postcode CHAR(4),
c_has_adopted_before CHAR(1),
constraint customer_PK PRIMARY KEY (customer_id)
);
create table animal (
animal_id CHAR(9) NOT NULL,
a_animal_type VARCHAR(20) ,
a_breed VARCHAR(50),
a_colour VARCHAR(30),
a_size VARCHAR(20),
a_weight_kg VARCHAR(10),
a_description VARCHAR(75),
a_name VARCHAR(30),
a_date_of_birth DATE,
a_sex CHAR(1),
a_animal_cost INT(10),
a_microchip_status CHAR(1),
a_vaccination_status CHAR(1),
constraint animal_PK PRIMARY KEY (animal_id)
);
create table animal_adoption_history (
health_conditions VARCHAR(100),
is_available_to_adopt CHAR(1),
has_been_adopted_previously CHAR(1),
reason_for_entry VARCHAR(75),
date_entered DATE,
animal_id CHAR(9) NOT NULL,
customer_id CHAR(9) NOT NULL,
constraint animal_adoption_history_PK PRIMARY KEY (animal_id, customer_id),
constraint animal_adoption_history_FK1 FOREIGN KEY (animal) references animal(animal_id)
constraint animal_adoption_history_FK2 FOREIGN KEY (customer_id) references customer(customer_id)
);
INSERT INTO customer (customer_id,c_first_name,c_last_name,c_gender,c_phone_number,c_email_address,c_date_of_birth,c_address_number,c_street_name,c_city,c_state,c_postcode,c_has_adopted_before)
VALUES ('C00000001','Olivia','Smith','F','0422425392','[email protected]','1980-06-22','2','Henderson Street','Bondi','NSW','2092','Yes');
INSERT INTO customer (customer_id,c_first_name,c_last_name,c_gender,c_phone_number,c_email_address,c_date_of_birth,c_address_number,c_street_name,c_city,c_state,c_postcode,c_has_adopted_before)
VALUES ('C00000002','Taylor','Brown','F','0422435394','[email protected]','1999-02-24','62','Ultimo Avenue','Bondi','NSW','2092','No');
INSERT INTO customer (customer_id,c_first_name,c_last_name,c_gender,c_phone_number,c_email_address,c_date_of_birth,c_address_number,c_street_name,c_city,c_state,c_postcode,c_has_adopted_before)
VALUES ('C00000003','Sarah','Li','F','0422425342','[email protected]','1997-02-22','27','Winchester Street','Epping','NSW','2092','Yes');
INSERT INTO customer (customer_id,c_first_name,c_last_name,c_gender,c_phone_number,c_email_address,c_date_of_birth,c_address_number,c_street_name,c_city,c_state,c_postcode,c_has_adopted_before)
VALUES ('C00000004','Charlie','Swift','M','0432425392','[email protected]','1998-02-22','22','Henderson Lane','Lindfield','NSW','2092','No');
INSERT INTO customer (customer_id,c_first_name,c_last_name,c_gender,c_phone_number,c_email_address,c_date_of_birth,c_address_number,c_street_name,c_city,c_state,c_postcode,c_has_adopted_before)
VALUES ('C00000005','Heath','Davidson','M','0422425911','[email protected]','2003-01-22','22','Station Street','Manly','NSW','2092','Yes');
INSERT INTO animal (animal_id,a_animal_type,a_breed,a_colour,a_size,a_weight_kg,a_description,a_name,a_date_of_birth,a_sex,a_animal_cost,a_microchip_status,a_vaccination_status)
VALUES ('A00000001','Dog','Pug','Light brown','Small','5','Playful yet enjoys cuddles','Mia','2020-02-22','F','3100','Y','Y');
INSERT INTO animal (animal_id,a_animal_type,a_breed,a_colour,a_size,a_weight_kg,a_description,a_name,a_date_of_birth,a_sex,a_animal_cost,a_microchip_status,a_vaccination_status)
VALUES ('A00000002','Cat','Tabby','Orange','Small','4','Quiet and loves the sun','Garfield','2010-04-28','M','1400','Y','N');
INSERT INTO animal (animal_id,a_animal_type,a_breed,a_colour,a_size,a_weight_kg,a_description,a_name,a_date_of_birth,a_sex,a_animal_cost,a_microchip_status,a_vaccination_status)
VALUES ('A00000003','Bird','Budgie','Green and yellow','Extra Small','0.035','Very loud when hungry','Roody','11-14','F','1200','N','Y');
INSERT INTO animal (animal_id,a_animal_type,a_breed,a_colour,a_size,a_weight_kg,a_description,a_name,a_date_of_birth,a_sex,a_animal_cost,a_microchip_status,a_vaccination_status)
VALUES ('A00000004','Rabbit','Holland Lop','Light brown and white','Small','5','Fluffy and enjoys lettuce snacks','Thumper','2018-19-04','F','900','N','N');
INSERT INTO animal (animal_id,a_animal_type,a_breed,a_colour,a_size,a_weight_kg,a_description,a_name,a_date_of_birth,a_sex,a_animal_cost,a_microchip_status,a_vaccination_status)
VALUES ('A00000005','Dog','Golden Retriever','Dark blonde','Large','32','Loves going for long walks','Milo','2014-05-30','M','2500','Y','Y');
INSERT INTO animal_adoption_history (health_conditions,is_available_to_adopt,has_been_adopted_previously,reason_for_entry,date_entered,animal_id,customer_id)
VALUES ('None','Y','Y','Owner moved away','2021-08-18','A00000001','C00000001');
INSERT INTO animal_adoption_history (health_conditions,is_available_to_adopt,has_been_adopted_previously,reason_for_entry,date_entered,animal_id,customer_id)
VALUES ('None','N','N','Newborn looking for home','2022-07-13','A00000003','C00000005');
INSERT INTO animal_adoption_history (health_conditions,is_available_to_adopt,has_been_adopted_previously,reason_for_entry,date_entered,animal_id,customer_id)
VALUES ('Diabetes','Y','Y','Owner passed away','2019-11-01','A00000004','C00000001');
INSERT INTO animal_adoption_history (health_conditions,is_available_to_adopt,has_been_adopted_previously,reason_for_entry,date_entered,animal_id,customer_id)
VALUES ('None','Y','N','Previous household abuse','2014-09-19','A00000002','C00000004');
INSERT INTO animal_adoption_history (health_conditions,is_available_to_adopt,has_been_adopted_previously,reason_for_entry,date_entered,animal_id,customer_id)
VALUES ('Arthritis','Y','Y','Newborn looking for home','2016-04-26','A00000005','C00000002');
Thanks!
CodePudding user response：
INT(10) is not valid datatype:
a_animal_cost INT(10)
There are other issues as well, you have to fix this.
CodePudding user response：
You have a number of errors in your code. When running PostgreSQL only lists the first one, which in this case is the line a_animal_cost INT(10),. Integers are a fixed size so it is wrong to attempt to specify a size for it.
Also do not use char(1) for Yes ('Y') No ('N') fields. PostgreSQL has a native boolean data type for this purpose, so use it.
When deleting tables, you need to delete the lowest tables in the hierarchy first (foreign key etc).
Finally, you can chain multiple inserts as values together.
Putting all this together, I would recommend that you use something like this:
DROP TABLE IF EXISTS animal_adoption_history;
DROP TABLE IF EXISTS animal;
DROP TABLE IF EXISTS customer;
create table customer (
customer_id CHAR(9) NOT NULL,
c_first_name VARCHAR(25),
c_last_name VARCHAR(50),
c_gender CHAR(1),
c_phone_number VARCHAR(20),
c_email_address VARCHAR(45),
c_date_of_birth DATE,
c_address_number VARCHAR(10),
c_street_name VARCHAR(30),
c_city VARCHAR(50),
c_state CHAR(3),
c_postcode CHAR(4),
c_has_adopted_before boolean,
constraint customer_PK PRIMARY KEY (customer_id)
);
create table animal (
animal_id CHAR(9) NOT NULL,
a_animal_type VARCHAR(20) ,
a_breed VARCHAR(50),
a_colour VARCHAR(30),
a_size VARCHAR(20),
a_weight_kg VARCHAR(10),
a_description VARCHAR(75),
a_name VARCHAR(30),
a_date_of_birth DATE,
a_sex CHAR(1),
a_animal_cost INT,
a_microchip_status boolean,
a_vaccination_status boolean,
constraint animal_PK PRIMARY KEY (animal_id)
);
create table animal_adoption_history (
health_conditions VARCHAR(100),
is_available_to_adopt boolean,
has_been_adopted_previously boolean,
reason_for_entry VARCHAR(75),
date_entered DATE,
animal_id CHAR(9) NOT NULL,
customer_id CHAR(9) NOT NULL,
constraint animal_adoption_history_PK PRIMARY KEY (animal_id, customer_id),
constraint animal_adoption_history_FK1 FOREIGN KEY (animal_id) references animal(animal_id),
constraint animal_adoption_history_FK2 FOREIGN KEY (customer_id) references customer(customer_id)
);
INSERT INTO customer (customer_id,c_first_name,c_last_name,c_gender,c_phone_number,c_email_address,c_date_of_birth,c_address_number,
c_street_name,c_city,c_state,c_postcode,c_has_adopted_before) VALUES
('C00000001','Olivia','Smith','F','0422425392','[email protected]','1980-06-22','2',
'Henderson Street','Bondi','NSW','2092',true),
('C00000002','Taylor','Brown','F','0422435394','[email protected]','1999-02-24','62',
'Ultimo Avenue','Bondi','NSW','2092',false),
('C00000003','Sarah','Li','F','0422425342','[email protected]','1997-02-22','27',
'Winchester Street','Epping','NSW','2092',true),
('C00000004','Charlie','Swift','M','0432425392','[email protected]','1998-02-22','22',
'Henderson Lane','Lindfield','NSW','2092',false),
('C00000005','Heath','Davidson','M','0422425911','[email protected]','2003-01-22','22',
'Station Street','Manly','NSW','2092',true);
INSERT INTO animal (animal_id,a_animal_type,a_breed,a_colour,a_size,a_weight_kg,a_description,a_name,a_date_of_birth,
a_sex,a_animal_cost,a_microchip_status,a_vaccination_status) VALUES
('A00000001','Dog','Pug','Light brown','Small','5','Playful yet enjoys cuddles','Mia','2020-02-22',
'F','3100',true,true),
('A00000002','Cat','Tabby','Orange','Small','4','Quiet and loves the sun','Garfield','2010-04-28',
'M','1400',true,false),
('A00000003','Bird','Budgie','Green and yellow','Extra Small','0.035','Very loud when hungry','Roody','2020-11-14',
'F','1200',false,true),
('A00000004','Rabbit','Holland Lop','Light brown and white','Small','5','Fluffy and enjoys lettuce snacks','Thumper','2018-04-19',
'F','900',false,false),
('A00000005','Dog','Golden Retriever','Dark blonde','Large','32','Loves going for long walks','Milo','2014-05-30',
'M','2500',true,true);
INSERT INTO animal_adoption_history (health_conditions,is_available_to_adopt,has_been_adopted_previously,reason_for_entry,
date_entered,animal_id,customer_id) VALUES
('None',true,true,'Owner moved away','2021-08-18','A00000001','C00000001'),
('None',false,false,'Newborn looking for home','2022-07-13','A00000003','C00000005'),
('Diabetes',true,true,'Owner passed away','2019-11-01','A00000004','C00000001'),
('None',true,false,'Previous household abuse','2014-09-19','A00000002','C00000004'),
('Arthritis',true,true,'Newborn looking for home','2016-04-26','A00000005','C00000002');