On Mon, 2003-02-17 at 00:15, Tom Lane wrote: > Ryan Bradetich <[EMAIL PROTECTED]> writes: > > On Sun, 2003-02-16 at 23:34, Tom Lane wrote: > >> It's not real clear to me why you bother enforcing a constraint that the > >> complete row be unique. Wouldn't a useful constraint be that the first > >> three columns be unique? > > > The table holds system policy compliance data. The catagory is > > basically the policy, and the anomaly is the detailed text explaining > > why the system is out of compliance. So the anomaly data is important > > (and often the reason why the key is unique). > > Well, sure the anomaly is important: it's the payload, the reason why > you bother to have the table in the first place. But that doesn't mean > it's part of the key. Generally the key would be the info you use to > look up a particular anomaly text. In this example, it's not clear to > me why you'd need/want two different anomaly texts entered for the same > host_id and the same category at the same instant of time. ISTM there's > something inadequate about your category column if you need that.
Ok, I understand what you are asking now :) Let me make up a contrived example to show how the table is used. host_id 1 = hosta.somewhere.com host_id 2 = hostb.somewhere.com The catagories are coded so (made up examples): cat p101 = /etc/passwd check cat f101 = filesystem check. the table would look like: 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user x has an invalid shell. 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has an invalid shell. 1 | Mon Feb 17 00:34:24 MST 2003 | p101 | user y has expired password. 2 | Mon Feb 17 00:34:24 MST 2003 | f101 | file /foo has improper owner. etc... So I do not need the anomaly to be part of the index, I only need it to I agree with you, that I would not normally add the anomally to the index, except for the unique row requirement. Thinking about it now, maybe I should guarentee unique rows via a check constraint... Thanks for making me think about this in a different way! - Ryan > regards, tom lane -- Ryan Bradetich <[EMAIL PROTECTED]> ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])