why does it keep giving me errors?-CodePudding

SQL table:

CREATE TABLE Application 
(
    App_ID number(5),
    Child_Name varchar2(15) 
        CONSTRAINT pk_Application_App_ID PRIMARY KEY, 
    Child_Weight number(2) 
        CONSTRAINT df_Application_Child_Weight DEFAULT 10 
            CHECK (Child_Weight > 0), 
    Nursery_ID number(7) 
        CONSTRAINT fk_Application_Nursey_ID 
            FOREIGN KEY (Nursery_ID) REFERENCES Nursey(Nursery_ID),  
    Bdate date 
        CONSTRAINT Application_Bdate CHECK (Bdate > '01-jan-2016' AND Bdate < '01-jun-2019')
)

I keep getting an error

Constraint specification not allowed here

Name is already used by an existing object

CodePudding user response：

That's a mess; you should properly format code to see what's going on. Consider something like this, instead:

SQL> create table nursey (nursery_id number(7) primary key);

Table created.

SQL> create table application (
  2      app_id       number(5),
  3      child_name   varchar2(15),
  4      child_weight number(2) default 10,
  5      nursery_id   number(7),
  6      bdate        date,
  7      --
  8      constraint pk_application_app_id primary key (app_id),
  9      constraint df_application_child_weight check (child_weight > 0),
 10      constraint fk_application_nursey_id foreign key (nursery_id)
 11         references nursey ( nursery_id ),
 12      constraint application_bdate check (    bdate > date '2016-01-01'
 13                                          and bdate < date '2019-06-01')
 14  );

Table created.

SQL>

CodePudding user response：

You have two errors:

DEFAULT 10 in the middle of a CHECK constraint.
When specifying an inline foreign key constraint you do not need to use the keywords FOREIGN KEY or specify the column name.

You probably have a third error:

You spelt Nursery as Nursey

It is also bad practice to compare dates with strings (even if the strings look like dates). Instead you should use date literals.

CREATE TABLE Application (
    App_ID       number(5),
    Child_Name   varchar2(15) 
                 CONSTRAINT pk_Application_App_ID PRIMARY KEY, 
    Child_Weight number(2) DEFAULT 10
                 CONSTRAINT df_Application_Child_Weight CHECK (Child_Weight > 0), 
    Nursery_ID   number(7) 
                 CONSTRAINT fk_Application_Nursey_ID REFERENCES Nursey(Nursery_ID),  
    Bdate        date 
                 CONSTRAINT Application_Bdate CHECK (Bdate > DATE '2016-01-01' AND Bdate < DATE '2019-07-01')
)

db<>fiddle here