替代使用可为空的列设置复合主键

I have a table say ITEM that should contain :

我有一个表说ITEM应该包含:

ID_SERVICE , // foreign key to table SERVICE
ID_FILE , // foreign key to table FILE
ID_FUNCTION  // foreign key to table FUNCTION

and it must be identified by those 3 columns (compound primary key) , however my problem is that sometimes i might have an item that doesnt have a function (ID_FUNCTION could be null).

它必须由那3列(复合主键)标识,但我的问题是有时我可能有一个没有函数的项(ID_FUNCTION可能为null)。

How can i redesign this table so that it can handle these constraintes ?

如何重新设计此表以便它可以处理这些约束?

Thanks.

2 个解决方案

#1

You create a unique index not a primary key constraint. A unique index allows for any key to be null, but still enforces uniqueness over every column. The syntax is the same as index but you add unique in front:

您创建唯一索引而不是主键约束。唯一索引允许任何键为空,但仍然强制每列的唯一性。语法与索引相同,但您在前面添加了唯一:

create unique index i_table_name
    on table_name ( column_names )
       tablespace
       storage ( ... )
       < etc >

A unique index will automatically create a unique constraint underlying it. If you don't want the index you could just create the constraint on it's own:

唯一索引将自动为其创建唯一约束。如果你不想要索引,你可以自己创建约束:

alter table table_name
add constraint constraint_name unique (column_names)

Here is more information on the different types of constraints.

以下是有关不同类型约束的更多信息。

#2

is ID_FUNCTION declared as NOT NULL? If it's not nullable, this may be the solution. Alter table so ID_FUNCTION column permits null values.

ID_FUNCTION声明为NOT NULL?如果它不可为空,这可能是解决方案。更改表,以便ID_FUNCTION列允许空值。

#1