0

maybe I can get some feedback from some folks on this. I created two tables and inserted data into one table and i put a constraint (Foreign key) on the table std_individual_address.

I get the following error message when I try to execute the insert now:

Msg 515, Level 16, State 2, Line 43 Cannot insert the value NULL into column 'individual_GUID', table 'ABLE.dbo.std_individual_address';
column does not allow nulls. INSERT fails. The statement has been terminated.

Here is all my code:

--Create the std_individual table

CREATE TABLE std_individual(
individual_GUID INT NOT NULL IDENTITY,
individual_First_Name VARCHAR(50) NULL,
individual_Last_Name VARCHAR(50) NULL,
individual_email VARCHAR(40) NULL,
PRIMARY KEY (individual_GUID));

--Create the std_individual_address table

 CREATE TABLE std_individual_address
 (
individual_address_GUID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
individual_address_line1 VARCHAR(100) NULL,
individual_address_line2 VARCHAR(100) NULL,
individual_address_line3 VARCHAR(100) NULL,
individual_address_city VARCHAR(50) NULL,
individual_address_state VARCHAR(30) NULL,
individual_address_zipcode VARCHAR(30) NULL,
individual_GUID INT NOT NULL,
CONSTRAINT fk_std_individual_address_std_individual FOREIGN KEY (individual_GUID) REFERENCES   std_individual (individual_GUID)
)

--Insert Individual Data

INSERT INTO std_individual
(individual_First_Name,individual_Last_Name,individual_email)
VALUES
('Terry','Smith','tsmith@example.net'),
('Ronald','Smegan','ronald@example.net'),
('Arnold','Aggassi','aaggassi@example.edu'),
('Jerry','Brukheimer','bbrukheimer@example.edu');

--Mind the Constraint

    INSERT INTO std_individual_address(individual_GUID) SELECT individual_GUID from std_individual

    --Attempt to insert rest of the data

    INSERT INTO std_individual_address

  (individual_address_line1,individual_address_line2,individual_address_city,individual_address_state,

individual_address_zipcode )

    VALUES 
    ('8200 Greensboro Drive','Ste 1500','Mclean','Virgina','22102'),
    ('1121 14th Street, NW','Ste 1000','Washington' ,'District of Columbia','20005'),
    ('1700 Connecticut Ave,NW','Ste 300','Washington' ,'District of Columbia','20009'),
    ('205 Pennsylvania Ave,SE','','Washington','District of Columbia','20003');

Then I get the error message above. Any ideas on how to combat that issue?

René Vogt
  • 43,056
  • 14
  • 77
  • 99
SRahmani
  • 348
  • 1
  • 5
  • 17
  • Your foreign key says it cant be null. In your final insert you are NOT inserting a value for that column. BTW, you really should avoid the name GUID when the datatype is int. GUID is synonymous wiht uniqueidentifier. It is also not a good practice to include the table name in every column name. Just name your columns a name that matches the data it contains. (AddressLine1, AddressLine2, etc) – Sean Lange Sep 04 '14 at 18:39
  • @SeanLange, Thanks - this is not a real application and I am not naming the tables GUID. Rather this is for a scenario I may be facing shortly with a new schema change. Thank you for your feed back. – SRahmani Sep 04 '14 at 18:42
  • The important part is the first sentence. Do you now understand the reason you got that error? – Sean Lange Sep 04 '14 at 18:43

0 Answers0