Can I have composite constraints?

Question

Hi,

Having a table with this structure...

Table_files

• id_file (PK)
• file_name
• file_path

... can I have a constraint that allows me to not duplicate the pair "file_name"+"file_path" (but allows me to duplicate the "file_name" and "file_path" individually), where the only Primary Key is the field "id_file"?

Thanks

Answer 1

Yes. Create an index for the two fields, and make it unique.

Answer 2

to go with what Guffa said in his answer, create a unique index on the two fields:

CREATE UNIQUE NONCLUSTERED INDEX IX_Table_files_name_path ON Table_files 
(
    file_name,file_path
)
GO

this prevents any combination of file_name+file_path from being duplicated, but allows for repeated values within file_name and file_path values, just not the same combination.