如何将主键和外键添加到Microsoft SQL Server数据库中的现有表

I've created 2 tables, table123 and items_ordered.

我创建了2个表,table123和items_ordered。

table123 has 5 columns: customerid, firstname, surname, city and state

table123有5列:customerid,firstname,surname,city和state
items_ordered has 5 columns customerid, order_date, item, quantity and price.

items_ordered有5列customerid,order_date,item,quantity和price。

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

我想在customerid列上的table123中创建一个主键,在customerid列上的items_ordered中创建一个外键。

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'

无法在表'table123'中的可空列上定义PRIMARY KEY约束

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?

上面的代码为我提供了一个表格,将customerid与客户名称和购买的产品相关联。但这是创建主键和外键的正确方法吗?

2 个解决方案

#1

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

您需要将列定义更改为非null,并确保所有值都是唯一的

#2

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.

将列定义更改为不允许空值。

#1