hi.
I have ten or more(i don't know) tables that have a column named foo with same datatype.
how can i tell sql that values in all the tables should be unique.
I mean If(i have value "1" in table1) I should NOT be able to have value "1" in table2
views:
75answers:
5
+4
A:
Hey,
Have a common ID's table, which these ten tables reference. That will work well in that it will ensure unique ID's, but doesn't mean you couldn't duplicate the ID's in the table if someone really wants to.
What I mean is a common ID's table ensures that you don't have duplicates for insert (by also inserting an ID into this common table), but the thing is the way to guarantee that it never happens is by building the business rules into the system or placing check constraints to cross reference the other tables (which would ensure uniqueness, but degrade performance).
Brian
2010-03-08 21:11:05
A:
You can define the field as a GUID (or a UNIQUEIDENTIFIER in SQL server). Then it will always be unique no matter what.
Jeff Hornby
2010-03-08 21:16:24
Order of values is important.
Behrooz
2010-03-08 21:18:06
+1
A:
The question is phrased vaguely; if you need to generate a column that's unique among several tables, use row GUIDs or a common ID generator table; if you need to enforce uniqueness (and the field values are already there), use triggers.
Generally, if you generate the values, you don't need to enforce anything. The generation logic, if done right, will take care of that. If you are inserting, say, user input, then you can and should enforce uniqueness during insertion. As a validation rule or something.
Seva Alekseyev
2010-03-08 21:16:56
I want to have some errors if the value i am trying to insert is not unique.(i want to be told if there is a concurrency problem in my program).but i don't like triggers(because they seem to be slow for my work(+10 tables))
Behrooz
2010-03-08 21:21:27
@HLGEM:I have done it to have fastest possible speed for querying.but I think you are right.(Any solution will be slow.)
Behrooz
2010-03-08 21:25:55
@Behrooz The DBMS table's job is exactly that: to contain many billions of rows. Nothing wrong about it. Why you promised that to yourself?
Roberto Russo
2010-03-08 21:41:31
@Roberto Russo:because they are all different.they only have 3 or 4 columns that are same and the other 20 columns are different.and i hate nulls.(so this was the only way to do it.)
Behrooz
2010-03-08 21:48:46
@Behrooz There is another way: the right way. Look for "Third normal form"
Roberto Russo
2010-03-08 21:56:57
A:
How about setting a check constraint on each table, such that ID % 10 = N (where N is the table number, from 0-9). And use IDENTITY(N,10) each time.
Rob Farley
2010-03-08 21:19:23
Q:and what happens if table 1 has 1000 rows.and i insert 1 row in table 2. A:I have lost the order of rows.
Behrooz
2010-03-08 21:23:00
Fair enough. I hadn't picked that up as a requirement. If you go down the route of the IDs table, use the OUTPUT clause to make sure you don't have concurrency issues.
Rob Farley
2010-03-08 22:46:22
A:
I would suggest that possibly your design is flawed. Why are these separate tables? It ouwld be better to put them in one table with one id field and another filed to identify whatever is making these spearate tables (cusotmer id for instance). Then you can read about partioning tables if you want them to be split by customer for performance reasons.
HLGEM
2010-03-08 21:23:09