In MySQL, Can I put a restriction like below at database level?
I need to make sure 1. a specific column can only take either 'true' or 'false' values. 2. and exactly only one row should be having 'true' value?
views:
34answers:
3
+1
Q:
Could I make a column in a table only allows one 'true' value and all other rows should be 'false'
A:
a specific column can only take either 'true' or 'false' values.
Use Datatype for column either
booleanortiny-int
2 and exactly only one row should be having 'true' value?
you have to write a query which update all the row columns to false except one you set to true
Salil
2010-07-12 11:47:14
for (2), I want it to be like constraint (similar to unique, primary key constraints)
Reddy
2010-07-12 11:50:31
+1
A:
Set the data-type of the column to "boolean"; then add a trigger-function, that sets the row you want to be always "true" to true on update.
Erik
2010-07-12 11:47:46
I don't think so; you cannot set a column-lock on a single row to prevent updates. Any kind of UNIQUE index also won't work, since all other false-entries would throw a duplicate-key-exception. Only if you use stored procedures or views to change the table, you could enforce it there. If the table is used directly, I think the trigger is the one and only option.Another solution would be to extract the column to another table and join this table then. But I'm not able to check this, cause I do not know what you are exactly doing.
Erik
2010-07-12 12:23:03
+2
A:
Rather than having the boolean attribute in the table, you could have another table that contains one row and points to the row in the original table that you consider true.
Changing the true value is a matter of updating the foreign key in the TrueRow table.
Gilbert Le Blanc
2010-07-12 12:40:17
Yep. And it would be possible to use a check constraint to ensure that only one row exists (using Celko's technique here http://www.sqlmonster.com/Uwe/Forum.aspx/ms-sql-server/3453/How-to-Modify-a-views-SQL-source-from-a-program)
Martin Smith
2010-07-12 12:45:27
exactly what I wrote on my comment - but we do not know if it is possible to redesign the SQL-Schema. The problem is definitifly a problem-by-design. Anyway this was already discussed ...
Erik
2010-07-12 13:25:57
Reddy
2010-07-13 04:21:54