Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - Unique Constraint with conditions in MYSQL

In postgres we have a constraint defined that essentially allows us to limit the number of entries in a table with a certain value to one. We created this constraint:

create unique index list$default$uk on list_group(visitor_uid) where list_type = 'default';

Which means that the unique constraint is only applied when the list_type='default' so that there can only be one 'default' list per visitor in the table.

It looks like MySql doesn't support the additional where on the unique constraint. Is there another way to support this in the database schema for MySQL?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

MYSQL doesn't support such types of constraints.

You should use stored procudures for inserting data instead, so you can do some checking and validation.

Why don't you define your default as such that it must have 1 as primary key? This way a normal unique constraint on the pk would be enough already.

If nothing fits to you, you could also consider changing your data model.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...