cant get sql code to execute, having trouble with foreign keys create table memb

ID: 3809677 • Letter: C

Question

cant get sql code to execute, having trouble with foreign keys

create table member
(member_id Integer NOT NULL ,
First_Name Varchar(100) ,
Last_Name Varchar(100) ,
SSN Char(9) ,
Address Varchar(100) ,
City Varchar(100) ,
State Varchar(100) ,
Zip_Code Varchar(100) ,
Phone_No Char(10) ,
Email_Address Varchar(100) ,
PRIMARY KEY (member_id));

create table messages
(member_id Integer NOT NULL ,
message_id Integer NOT NULL ,
message Varchar (100) ,
PRIMARY KEY ( message_id, member_id ));

create table member_messages
(message_id Integer NOT NULL ,
member_id Integer NOT NULL ,
PRIMARY KEY ( message_id ,member_id ) ,
FOREIGN KEY (message_id ) REFERENCES messages (message_id ) ,
FOREIGN KEY (member_id ) REFERENCES member(member_id ));

create table rate ( rate_id Integer NOT NULL ,
current_interest_rate Decimal (3,2) ,
account_type Char (50) ,
CONSTRAINT rate_pk PRIMARY KEY ( rate_id )
);

CREATE TABLE accounts (account_no Integer NOT NULL,
member_id Integer,
primary_owner_first VARCHAR(100),
primary_owner_last VARCHAR(100),
joint_owner_first VARCHAR(100),
joint_owner_last VARCHAR(100),
rate_id Integer,
current_balance integer,
PRIMARY KEY(member_id, account_no),
FOREIGN KEY(rate_id) REFERENCES rate (rate_id));

create table transactions ( transaction_id Integer NOT NULL ,
account_no Integer ,
initiator Char (50) ,
amount integer ,
date_time timestamp ,
transaction_type Char(50),
PRIMARY KEY (transaction_id, account_no),
FOREIGN KEY (account_no) REFERENCES accounts (account_no)
);

Create table daily_balance (account_no INTEGER NOT NULL,
End_of_Day_Balance INTEGER NOT NULL,
date_time Timestamp,
Constraint daily_bal_PK PRIMARY KEY (account_no, End_of_Day_Balance)
);

create table cd ( account_no Integer NOT NULL ,
interest_rate Decimal (3,2) ,
last_interest_date date ,
terms Char (20) ,
CONSTRAINT cd_PK PRIMARY KEY ( account_no),
FOREIGN KEY (account_no) REFERENCES accounts (account_no),
);

create table loan ( account_no Integer NOT NULL ,
interest_rate decimal (3,2) ,
loan_type Varchar (50) ,
principal integer ,
unpaid_interest Char (100) ,
annual_rate Decimal (3,2),
monthly_payment integer ,
termination_date date ,
next_payment_due_date date ,
next_payment_due_amt Integer ,
fixed_rate Decimal (3,2),
fixed_monthly_pmt integer ,
account_status Char (5) ,
CONSTRAINT loan_PK PRIMARY KEY ( account_no ),
FOREIGN KEY (account_no) REFERENCES accounts (account_no),
);

create table checking ( account_no Integer NOT NULL ,
Balance integer,
min_bal_req Char (100) ,
interest_rate Decimal (3,2),
last_interest_date date ,
End_of_Day_Balance Integer,
CONSTRAINT checking_PK PRIMARY KEY ( account_no),
FOREIGN KEY (account_no) REFERENCES accounts (account_no),
FOREIGN KEY (End_of_Day_Balance) REFERENCES daily_balance(End_of_Day_Balance)
);

create table savings ( account_no Integer NOT NULL ,
Balance Integer ,
min_bal_req Integer ,
interest_rate Decimal(3,2) ,
last_interest_date date ,
CONSTRAINT savings_PK PRIMARY KEY ( account_no),
FOREIGN KEY (account_no) REFERENCES accounts(account_no),
FOREIGN KEY (End_of_Day_Balance) REFERENCES daily_balance(End_of_Day_Balance)
);

Create table member_accounts (member_id Integer NOT NULL,
account_no Integer NOT NULL,
PRIMARY KEY (member_id, account_no),
FOREIGN KEY (member_id) REFERENCES member(member_id),
FOREIGN KEY (account_no) REFERENCES accounts(account_no)
);

Create table transfer (transaction_id Integer NOT NULL ,
account_no Integer NOT NULL,
account_no_2 Integer,
amount Integer,
PRIMARY KEY (transaction_id, account_no),
FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id),
FOREIGN KEY (account_no) REFERENCES transactions(account_no)
);

Thanks

Explanation / Answer

The Error is Because in saving table in this column

: FOREIGN KEY (End_of_Day_Balance) REFERENCES daily_balance (End_of_Day_Balance)

Yor Try to map column which is not define in saving table defintion. Change is Script and add the column in table defination.

create table member
   (member_id Integer NOT NULL ,
    First_Name Varchar(100) ,
    Last_Name Varchar(100) ,
    SSN Char(9) ,
    Address Varchar(100) ,
    City Varchar(100) ,
    State Varchar(100) ,
    Zip_Code Varchar(100) ,
    Phone_No Char(10) ,
    Email_Address Varchar(100) ,
    PRIMARY KEY (member_id));

create table messages
    (member_id Integer NOT NULL ,
    message_id Integer NOT NULL ,
    message Varchar (100) ,
    PRIMARY KEY ( message_id, member_id ));

create table member_messages
    (message_id Integer NOT NULL ,
    member_id Integer NOT NULL ,
    PRIMARY KEY ( message_id ,member_id ) ,
    FOREIGN KEY (message_id ) REFERENCES   messages (message_id ) ,
    FOREIGN KEY (member_id ) REFERENCES   member(member_id ));

create table rate ( rate_id Integer NOT NULL ,
    current_interest_rate Decimal (3,2) ,
    account_type Char (50) ,
    CONSTRAINT   rate_pk PRIMARY KEY ( rate_id )
    );

create table transactions ( transaction_id Integer NOT NULL ,
    account_no Integer ,
    initiator Char (50) ,
    amount integer ,
    date_time timestamp ,
   transaction_type Char(50),
   PRIMARY KEY (transaction_id, account_no),
   FOREIGN KEY (account_no) REFERENCES accounts (account_no)
   );

Create table daily_balance (account_no INTEGER NOT NULL,
End_of_Day_Balance INTEGER NOT NULL,
date_time Timestamp,
Constraint daily_bal_PK1 PRIMARY KEY (account_no, End_of_Day_Balance)
);

create table cd ( account_no Integer NOT NULL ,
    interest_rate Decimal (3,2) ,
    last_interest_date date ,
    terms Char (20) ,
    CONSTRAINT   cd_PK PRIMARY KEY ( account_no),
    FOREIGN KEY (account_no) REFERENCES accounts (account_no)
    );

create table loan ( account_no Integer NOT NULL ,
    interest_rate decimal (3,2) ,
    loan_type Varchar (50) ,
    principal integer ,
    unpaid_interest Char (100) ,
    annual_rate Decimal (3,2),
    monthly_payment integer ,
    termination_date date ,
    next_payment_due_date date ,
    next_payment_due_amt Integer ,
    fixed_rate Decimal (3,2),
    fixed_monthly_pmt integer ,
    account_status Char (5) ,
    CONSTRAINT   loan_PK PRIMARY KEY ( account_no ),
    FOREIGN KEY (account_no) REFERENCES accounts (account_no)
    );


create table checking ( account_no Integer NOT NULL ,
    Balance integer,
    min_bal_req Char (100) ,
    interest_rate Decimal (3,2),
    last_interest_date date ,
    End_of_Day_Balance Integer,
     CONSTRAINT   checking_PK PRIMARY KEY ( account_no),
FOREIGN KEY (account_no) REFERENCES accounts (account_no),
FOREIGN KEY (End_of_Day_Balance) REFERENCES daily_balance (End_of_Day_Balance)
);

create table savings ( account_no Integer NOT NULL ,
    Balance Integer ,
    min_bal_req Integer ,
    interest_rate Decimal(3,2) ,
    last_interest_date date ,
    End_of_Day_Balance Integer,
    CONSTRAINT   savings_PK PRIMARY KEY ( account_no),
    FOREIGN KEY (account_no) REFERENCES accounts(account_no),
    FOREIGN KEY (End_of_Day_Balance) REFERENCES daily_balance (End_of_Day_Balance)
);

Create table transfer (transaction_id Integer NOT NULL ,
    account_no Integer NOT NULL,
    account_no_2 Integer,
    amount Integer,
PRIMARY KEY (transaction_id, account_no),
FOREIGN KEY (transaction_id) REFERENCES transactions(transaction_id),
FOREIGN KEY (account_no) REFERENCES transactions(account_no)
);

Navigate

cant get a clearer image, zoom in? Your Task Implement a premade Flow Chart to c

cant get the code to work, Solution #include #include #include #include #include

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

cant get sql code to execute, having trouble with foreign keys create table memb

Question

Explanation / Answer

Related Questions

Navigate