0

I have a problem where I need to check that two columns in each table in a database are unique.

We have the database with barcode entries called uid and rid.

Table 1: T1.uid And Table 2: T2.rid No barcodes in the two table columns must be the same. How can we ensure that. If a insertion of a barcode into table T1.uid matches an entry in T2.rid we want to throw an error.

The tables are cleaned up and is in a consistent state where the entries in T1.uid and T2.rid are unique over both table columns.

It is not possible to insert NULL values in the tables respective uid and tid column(T1.uid and T2.rid)

It is not possible to create a new table for all barcodes. Because we don't have full control of the database server.

EDIT 19-02-2015

This solution cannot work for us, because we cannot make a new table to keep track of the unique names(see table illustration). We want to have a constraint over two columns in different tables without changing the schema.

Per the illustration we want to make it impossible for john to exist in T2 because he already exists in table T1. So an error must be "thrown" when we try to insert John in T2.Name.

The reason is that we have different suppliers that inserts into these tables in different ways, if we change the schema layout, all suppliers would need to change their database queries. The total work is just to much, if we force every suppplier to make changes. So we need something unobtrusive, that doesnt require the suppliers to change their code.

A example could be that T1.Name is unique and do not accept NULL values. If we try insert an existing name, like "Alan", then an exception will occur because the column has unique values.

But we want to check for uniqueness in T2.Name at the same time. The new inserted value should be unique over the two tables.

enter image description here

Community
  • 1
  • 1
Neru-J
  • 1,623
  • 2
  • 23
  • 38
  • How does the data come into the database? Because there's a number of ways this could be handled, and depending on whether it's imported via data processes or entered via an application - then the better solution can differ. – Allan S. Hansen Feb 18 '15 at 13:59
  • possible duplicate of [SQL Unique constraint across multiple tables](http://stackoverflow.com/questions/10337944/sql-unique-constraint-across-multiple-tables) – Tab Alleman Feb 18 '15 at 14:03
  • 1
    Not a duplicate, link do not solve the problem, the answer found for the solution is described in below answer. – cognacc Jul 21 '15 at 08:09

3 Answers3

0

Maybe something like this:

SELECT uid FROM Table1 Where Exists ( SELECT rid FROM Table2 WHERE Table1.uid = rid )

This will show all rows from Table1 where their column uid has an equivalent in column rid of Table2.

farzaaaan
  • 410
  • 3
  • 9
0

The condition before the insertion happens could look like below. @Idis the id you need to insert the data for.

DECLARE @allowed INT;

SELECT @allowed = COUNT(*)
FROM
(
SELECT T1.uid FROM T1 WHERE T1.uid = @Id
UNION ALL
SELECT T2.rid FROM T2 WHERE T2.rid = @id
)
WHERE
 @id IS NOT NULL;

IF @allowed = 0 
BEGIN
  ---- insert allowed
  SELECT 0;
END
Dimi Takis
  • 4,924
  • 3
  • 29
  • 41
0

Thanks to all who answered. I have solved the problem. A trigger is added to the database everytime an insert or update procedure is executed, we catch it check that the value(s) to be inserted doens't exist in the columns of the two tables. if that check is succesfull we exceute the original query. Otherwise we rollback the query.

http://www.codeproject.com/Articles/25600/Triggers-SQL-Server

  • Instead Of Triggers
Neru-J
  • 1,623
  • 2
  • 23
  • 38