CODEWITHSUNDEEP
sqlsql-server-2005key
sugam
sugam

Reputation: 17

adding a foreign key in sql server 2005

I am using the following command to add a foreign key 

ALTER TABLE Company
ADD FOREIGN KEY (enumber)
REFERENCES emp(enumber);

... but it's giving an error:

Msg 1769, Level 16, State 1, Line 2
Foreign key 'company_enumber_FK' references invalid column 'enumber' in referencing table 'company'.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

Now if I create a column enumber in table company and execute it the command runs but shows null value.

Here enumber is a primary key of table emp and company is another table in which I want the foreign key enumber to be added

What should I do?

Upvotes: 0

Views: 196

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

The usual approach would be to add the new column to the Company table - either as a nullable column or (if all rows should receive the same value) with a default.

Next, you apply the foreign key constraint.

If, in the first paragraph, you opted for the nullable column, you should now write an UPDATE that determines what the correct value is for each row in Company.

Finally, you can alter Company again to change the nullable column to be NOT NULL.

Upvotes: 3

Mike Perrenoud
Mike Perrenoud

Reputation: 67898

This means the Company doesn't have a field named enumber. So in other words, this:

ADD FOREIGN KEY (enumber)

is referencing the wrong field in Company.

Upvotes: 0

Related Questions