开发者

database,sql server, sql which key?

I have 1 table with 6 columns lets call them A B C D E F

They can be null or a value but that once have a value entered in any row of any column it must be null or unique and validated by the database what key or constraint could I use to achive this?

ok real life scenario--

I have a product this is carrots the carrot can have upto 6 barcodes values, but if i have apples the barcode mu开发者_Go百科st not be the same any of the barcode values of carrots. Table columns product, barcode1, barcode2, barcode3, barcode4, barcode5, barcode6.


If you are trying to enforce uniqueness across all six columns (e.g., I enter 'Foo' in column A for a row, then columns B,C,D,E, or F in that row cannot have 'Foo' nor any other row for columns A-F), there is no built-in mechanism to do this beyond triggers. I suspect that your data may not be normalized and that is likely the reason for the inability to use built-in referential mechanisms to enforce uniqueness. We would need to know more about the database schema to know for sure.

ADDITION

ok real life senario I have a product this is carrots the carrot can have upto 6 barcodes values, but if i have apples the barcode must not be the same any of the barcode values of carrots. Table columns product, barcode1, barcode2, barcode3, barcode4, barcode5, barcode6,

The normalized solution would be to store your barcodes in another table:

Create Table ProductBarCodes
(
    ProductId ... not null References Products( Id )
    , Sequence int not null
    , Barcode ... not null
    , Constraint PK_ProductBarCodes Primary Key ( ProductId, Sequence )
    , Constraint CK_ProductBarCodes_Sequence Check ( Sequence Between 1 And 6 )
    , Constraint UC_ProductBarCodes_Barcode Unique ( Barcode )
)


Just place a unique key. Null values are thought of as different, so the unique restriction won't touch null values.


The problem is your design I think. It doesn't seem to make much sense to have 6 columns of barcodes. Put the barcodes in a single column and relate them to products with a reference (foreign key).

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜