How to store a standard selection?
-
I have a question that's more of the hypothetical kind. I have two tables:
Users
userid
username
1
John Doe
2
Jane Doe
email
userid
email
1
1
1
2
2
2
Now I'd like to make one (or zero) email address per user the default address. On an ANSI compliant system I could simply add a nullable column to the email table and add a check so that the value can only be 'DEFAULT' or null and add a unique composite key on this column and the userid column. This would of course not work on certain databases that don't allow more than one null value when having a unique key. So what other solutions are there? Preferrably not allowing null values.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have a question that's more of the hypothetical kind. I have two tables:
Users
userid
username
1
John Doe
2
Jane Doe
email
userid
email
1
1
1
2
2
2
Now I'd like to make one (or zero) email address per user the default address. On an ANSI compliant system I could simply add a nullable column to the email table and add a check so that the value can only be 'DEFAULT' or null and add a unique composite key on this column and the userid column. This would of course not work on certain databases that don't allow more than one null value when having a unique key. So what other solutions are there? Preferrably not allowing null values.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
How about a bit field (0/1;true/false)? Set the default to 0, not null.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
How about a bit field (0/1;true/false)? Set the default to 0, not null.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
How would that allow only one emailaddress to be set to default?
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have a question that's more of the hypothetical kind. I have two tables:
Users
userid
username
1
John Doe
2
Jane Doe
email
userid
email
1
1
1
2
2
2
Now I'd like to make one (or zero) email address per user the default address. On an ANSI compliant system I could simply add a nullable column to the email table and add a check so that the value can only be 'DEFAULT' or null and add a unique composite key on this column and the userid column. This would of course not work on certain databases that don't allow more than one null value when having a unique key. So what other solutions are there? Preferrably not allowing null values.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
An extra table to store only a reference to the user, and a reference to his default email;
userId
emailId
1
1
2
4
..which would work best if
emailwould get an identity-like field. --edit; Add aUNIQUEconstraint on the userId-column :)Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
An extra table to store only a reference to the user, and a reference to his default email;
userId
emailId
1
1
2
4
..which would work best if
emailwould get an identity-like field. --edit; Add aUNIQUEconstraint on the userId-column :)Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
Where's the 'slapping my forehead' icon when I need it? I guess :doh: will have to do. :thumbsup:
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
How would that allow only one emailaddress to be set to default?
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
The default is set to true, the rest to false.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me