CODEWITHSUNDEEP
sqlsql-serverdatabase
CrashBandicoot
CrashBandicoot

Reputation: 399

How to add a primary key and foreign key to an existing table in Microsoft SQL Server database

I've created 2 tables, table123 and items_ordered.

I want to create a primary key in table123 on the customerid column and a foreign key in items_ordered on the customerid column.

Here is the code I used to try and create a primary & foreign key:

ALTER TABLE table123
    ADD PRIMARY KEY (customerid);

ALTER TABLE items_ordered
    ADD FOREIGN KEY (customerid)
        REFERENCES table123 (customerid);

However, when I execute these commands, it says

Cannot define PRIMARY KEY constraint on nullable column in table 'table123'

How can I fix this? There aren't any null values in the tables either.

P.S. I've tried using this alternative method to create a primary key and foreign key:

SELECT table123.customerid, table123.surname, 
       items_ordered.order_date, items_ordered.item, items_ordered.price
FROM table123, items_ordered
WHERE table123.customerid = items_ordered.customerid;

The above code gives me a table, linking customerid to the customer name and product bought. But is this the correct way of creating a primary & foreign key?

Upvotes: 1

Views: 19086

Answers (2)

Shruti
Shruti

Reputation: 190

Could you try this?

ALTER TABLE table123
ALTER COLUMN customerid INT NOT NULL

Then run :

ALTER TABLE table123
ADD PRIMARY KEY (customerid)

Then,

ALTER TABLE items_ordered
ADD FOREIGN KEY (customerid)
REFERENCES table123 (customerid)

You need to change your column definition to not null, and make sure all values are unique as well

Upvotes: 7

Alienari
Alienari

Reputation: 21

This normally occurs when you are trying to create a primary key on a column that is allowed to have null values. There may not be any null values in content but the definition still allows for them.

Change the column definition to not allow nulls.

Upvotes: 1

Related Questions