locked
Unique constraint on columns from multiple tables RRS feed

  • Question

  • Hi all,

    I have a schema that looks like this:

    create table Company
    	(CompanyID int identity(1,1) primary key not null,
    	CompanyName nvarchar(50) not null)
    
    create table Products
    	(ProductID int identity(1,1) primary key not null,
    	CompanyID int foreign key references Company(CompanyID) not null,
    	ProductCode varchar(30) not null,
    	ProductDescription varchar(250) not null
    	constraint uq_Product unique nonclustered
    	(
    		CompanyID, ProductCode
    	)
    	)
    
    
    create table ProductBarcodes
    	(BarcodeID int identity(1,1) primary key not null,
    	ProductID int foreign key references Products(ProductID) not null,
    	Barcode varchar(30) not null,
    	UnitOfMeasureCode char (10) not null)
    
    

    My barcode field must be unique by Company.

    What is the correct way to implement this?

    Thanks in advance for your advice.

    Cheers, Clay

    Monday, September 17, 2012 7:13 AM

Answers

  • Hi Clay,

    I think one of the way is to have CompanyID field into ProductBarcodes table and have a composite unique key.

    DROP TABLE ProductBarcodes
    DROP TABLE Products
    DROP TABLE company
    CREATE TABLE Company (
    	CompanyID INT identity(1, 1) PRIMARY KEY NOT NULL
    	,CompanyName NVARCHAR(50) NOT NULL
    	)
    CREATE TABLE Products (
    	ProductID INT identity(1, 1) PRIMARY KEY NOT NULL
    	,CompanyID INT FOREIGN KEY REFERENCES Company(CompanyID) NOT NULL
    	,ProductCode VARCHAR(30) NOT NULL
    	,ProductDescription VARCHAR(250) NOT NULL CONSTRAINT uq_Product UNIQUE NONCLUSTERED (
    		CompanyID
    		,ProductCode
    		)
    	)
    CREATE TABLE ProductBarcodes (
    	BarcodeID INT identity(1, 1) PRIMARY KEY NOT NULL
    	,CompanyID INT FOREIGN KEY REFERENCES Company(CompanyID) NOT NULL
    	,ProductID INT FOREIGN KEY REFERENCES Products(ProductID) NOT NULL
    	,Barcode VARCHAR(30) NOT NULL
    	,UnitOfMeasureCode CHAR(10) NOT NULL CONSTRAINT uq_Barcode_CompanyID UNIQUE NONCLUSTERED (
    		Barcode
    		,CompanyID
    		)
    	)
    --Inserting records into Company	
    INSERT INTO Company (CompanyName)VALUES ('a')
    INSERT INTO Company (CompanyName)VALUES ('B')
    --Inserting records into Products
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	1	,'A1'	,'First produce of 1'	)
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	1	,'A2'	,'Second produce of 1'	)
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	2	,'B1'	,'First produce of 2'	)
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	2	,'B2'	,'Second produce of 2'	)
    --Inserting records into ProductBarcodes
    --Valid entry
    INSERT INTO ProductBarcodes (CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (	1	,1	,'XIX'	,30	)
    --Invalid entry
    INSERT INTO ProductBarcodes (	CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (	1	,2	,'XIX'	,31	)
    	-- Error
    	--Msg 2627	,LEVEL 14	,STATE 1	,Line 48 Violation OF UNIQUE KEY CONSTRAINT 'uq_Barcode_CompanyID'.Cannot
    	--INSERT duplicate KEY IN OBJECT 'dbo.ProductBarcodes'.
    --Now Valid entry
    INSERT INTO ProductBarcodes (	CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (1	,2,'XIXIK',31)
    --Valid entry
    INSERT INTO ProductBarcodes (	CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (	2	,3	,'XIX'	,32	)
    --Verify Data
    SELECT * FROM company
    SELECT * FROM Products
    SELECT * FROM ProductBarcodes


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by clay123123123 Thursday, September 20, 2012 3:51 AM
    Tuesday, September 18, 2012 5:16 AM

All replies

  • Hi Clay,

    I think one of the way is to have CompanyID field into ProductBarcodes table and have a composite unique key.

    DROP TABLE ProductBarcodes
    DROP TABLE Products
    DROP TABLE company
    CREATE TABLE Company (
    	CompanyID INT identity(1, 1) PRIMARY KEY NOT NULL
    	,CompanyName NVARCHAR(50) NOT NULL
    	)
    CREATE TABLE Products (
    	ProductID INT identity(1, 1) PRIMARY KEY NOT NULL
    	,CompanyID INT FOREIGN KEY REFERENCES Company(CompanyID) NOT NULL
    	,ProductCode VARCHAR(30) NOT NULL
    	,ProductDescription VARCHAR(250) NOT NULL CONSTRAINT uq_Product UNIQUE NONCLUSTERED (
    		CompanyID
    		,ProductCode
    		)
    	)
    CREATE TABLE ProductBarcodes (
    	BarcodeID INT identity(1, 1) PRIMARY KEY NOT NULL
    	,CompanyID INT FOREIGN KEY REFERENCES Company(CompanyID) NOT NULL
    	,ProductID INT FOREIGN KEY REFERENCES Products(ProductID) NOT NULL
    	,Barcode VARCHAR(30) NOT NULL
    	,UnitOfMeasureCode CHAR(10) NOT NULL CONSTRAINT uq_Barcode_CompanyID UNIQUE NONCLUSTERED (
    		Barcode
    		,CompanyID
    		)
    	)
    --Inserting records into Company	
    INSERT INTO Company (CompanyName)VALUES ('a')
    INSERT INTO Company (CompanyName)VALUES ('B')
    --Inserting records into Products
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	1	,'A1'	,'First produce of 1'	)
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	1	,'A2'	,'Second produce of 1'	)
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	2	,'B1'	,'First produce of 2'	)
    INSERT INTO Products (	CompanyID	,ProductCode	,ProductDescription	)VALUES (	2	,'B2'	,'Second produce of 2'	)
    --Inserting records into ProductBarcodes
    --Valid entry
    INSERT INTO ProductBarcodes (CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (	1	,1	,'XIX'	,30	)
    --Invalid entry
    INSERT INTO ProductBarcodes (	CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (	1	,2	,'XIX'	,31	)
    	-- Error
    	--Msg 2627	,LEVEL 14	,STATE 1	,Line 48 Violation OF UNIQUE KEY CONSTRAINT 'uq_Barcode_CompanyID'.Cannot
    	--INSERT duplicate KEY IN OBJECT 'dbo.ProductBarcodes'.
    --Now Valid entry
    INSERT INTO ProductBarcodes (	CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (1	,2,'XIXIK',31)
    --Valid entry
    INSERT INTO ProductBarcodes (	CompanyID	,ProductID	,Barcode	,UnitOfMeasureCode	)VALUES (	2	,3	,'XIX'	,32	)
    --Verify Data
    SELECT * FROM company
    SELECT * FROM Products
    SELECT * FROM ProductBarcodes


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by clay123123123 Thursday, September 20, 2012 3:51 AM
    Tuesday, September 18, 2012 5:16 AM
  • My barcode field must be unique by Company.

    Hi clay123123123,
     
    First, I want to confirm what do you mean by "unique". Do you mean that we can find single record in company table with one barcode value in ProductBarcodes table? If so, we can define Barcode as: “unique”. In this way, we can find single “ProductID” value record with one barcode value, since “CompanyID” has one-to-one relationship with “ProductID”, we can get single record in company table with one fixed barcode value. To achieve this, we can modify ProductBarcodes table with the following command:

    ALTER TABLE ProductBarcodes
    ADD UNIQUE (Barcode)

    and then use the following codes to get the CompanyName with a barcode value

    select companyname from company c join products p on c.CompanyID=p.CompanyID 
    join [ProductBarcodes] pb on p.ProductID=pb.ProductID 
    where pb.Barcode='xxxxx';



    Best Regards,
    Allen
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, September 18, 2012 7:12 AM
  • Thanks Manish,

    That's what I thought.

    I will see if I can get the schema changed to include the Company Id in the barcode table.

    Cheers.

    Thursday, September 20, 2012 3:51 AM
  • You are welcome Clay, Thanks for visiting MSDN forum.


    Thanks
    Manish

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, September 20, 2012 3:53 AM