question

Michele avatar image
Michele asked

insert dbid(pk) into two fk fields in insert statement

I have these tables: CREATE TABLE tblstudent (ST_ID int identity not null primary key, FNAME VARCHAR(15) NOT NULL, LINITIAL VARCHAR(2) NOT NULL);student

CREATE TABLE tblsurvey (S_ID INT identity not null primary key, Q1 VARCHAR(150) NOT NULL, Q2 VARCHAR(150) NOT NULL, Q3 VARCHAR(150) NOT NULL, Q4 VARCHAR(150) NOT NULL, Q5 VARCHAR(150) NOT NULL, ...Q34 VARCHAR(150) NOT NULL);

CREATE TABLE tblresponse (R_ID int identity not null primary key, S_ID INT not null FOREIGN KEY REFERENCES tblsurvey(S_ID), ST_ID INT not null FOREIGN KEY REFERENCES tblstudent(ST_ID), A1 INT NOT NULL, A2 INT NOT NULL, A3 INT NOT NULL, A4 INT NOT NULL, ...A34 INT NOT NULL); How can I populate the foreign keys on the response table, I have tried a stored procedure, I cannot get it to populate the field. I keep getting an error message. Does anyone know if this is possible and if so how???

primary-keyforeign-key
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dillinzser avatar image
dillinzser answered

You have to fill the referenced tables first, then you can reference them by using their existing primary keys as foreign keys in the third table.

But it would be pretty much aid for the problemsolving process if you attach the error message you get. It is not being shown just by chance;>

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Also, A1...A34 column names sure seems like a violation of normalization to me. If you're recording responses, better to create a table with the response and create a response type lookup table. You can create as many response types as you want and then as many responses as you need and if you ever add, or change a response type, you don't need to alter the structure of the table to do it.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.