locked
single unique key for three table RRS feed

  • Question

  • Hi,
    
    I have following three tables and
     its columns.
    
    Table1->Zmid, UserName, Email
    Table2->AsmId, Zmid, UserName, Email
    Table3->Rmid, ZmID, AsmId, UserName, Email
    
    Now currently i have UserName column
     as
     a unique
     key
     in
     all
     three 
    tables Now my problem is i want to make single unique key on
    UserName column for all three table . For example-If i inserted a record in table1 with username "XYZ" then i can not insert the same UserName in other two tables.
          Similar case for other two tables. actully i am inserting record with Excel sheet in bulk so need
    some
    constraint which can easily detect similar username.
    Monday, October 18, 2010 1:15 PM

Answers

  • Using triggers  you will be able to achive it
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 18, 2010 1:17 PM
  • One possible solution that does not use triggers or UDS-based CHECK constraints is to consider the entities represented in these three tables as subtypes of one common supertype, and add a table for that super type. The added benefit is that you can move columns that are required for all three types to the common table.

    CREATE TABLE Common
     (UserName varchar(40) NOT NULL,
      Type char(1) NOT NULL,
      Email nvarchar(200) NOT NULL,  -- Common column
      CONSTRAINT PK_Common PRIMARY KEY (UserName),
      CONSTRAINT CK_Common_Type CHECK (Type IN ('1', '2', '3'))
     );
    -- This index is redundant, but required for the foreign keys
    CREATE UNIQUE INDEX ix_Common
       ON Common(UserName, Type);
    go
    CREATE TABLE Table1
     (UserName varchar(40) NOT NULL,
      Type char(1) NOT NULL,
      Zmid decimal(15,4) NULL,  -- Column specific for table1
      CONSTRAINT PK_Table1 PRIMARY KEY (UserName),
      CONSTRAINT CK_Table1_Type CHECK (Type = '1'),
      CONSTRAINT FK_Table1_Common
        FOREIGN KEY (UserName, Type)
        REFERENCES Common (UserName, Type)
     );
    go
    -- Repeat for the other two tables.
    


    -- Hugo Kornelis, SQL Server MVP
    Wednesday, October 20, 2010 9:05 PM
  • Alternatively you can add a computed column to each table UserAlreadyExists and add a check constraint to this column based on UDF.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 18, 2010 2:28 PM
    Answerer

All replies

  • Using triggers  you will be able to achive it
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 18, 2010 1:17 PM
  • Alternatively you can add a computed column to each table UserAlreadyExists and add a check constraint to this column based on UDF.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Monday, October 18, 2010 2:28 PM
    Answerer
  • One possible solution that does not use triggers or UDS-based CHECK constraints is to consider the entities represented in these three tables as subtypes of one common supertype, and add a table for that super type. The added benefit is that you can move columns that are required for all three types to the common table.

    CREATE TABLE Common
     (UserName varchar(40) NOT NULL,
      Type char(1) NOT NULL,
      Email nvarchar(200) NOT NULL,  -- Common column
      CONSTRAINT PK_Common PRIMARY KEY (UserName),
      CONSTRAINT CK_Common_Type CHECK (Type IN ('1', '2', '3'))
     );
    -- This index is redundant, but required for the foreign keys
    CREATE UNIQUE INDEX ix_Common
       ON Common(UserName, Type);
    go
    CREATE TABLE Table1
     (UserName varchar(40) NOT NULL,
      Type char(1) NOT NULL,
      Zmid decimal(15,4) NULL,  -- Column specific for table1
      CONSTRAINT PK_Table1 PRIMARY KEY (UserName),
      CONSTRAINT CK_Table1_Type CHECK (Type = '1'),
      CONSTRAINT FK_Table1_Common
        FOREIGN KEY (UserName, Type)
        REFERENCES Common (UserName, Type)
     );
    go
    -- Repeat for the other two tables.
    


    -- Hugo Kornelis, SQL Server MVP
    Wednesday, October 20, 2010 9:05 PM