SQLTeam.com | Weblogs | Forums

Use of one table as source of values for two columns in another table while maintaining integrity

able 1

Business

Example

HP

Compaq

Table 2

Column 1: PN

Column 2:Make which draws its value from Table 1

Column 3:Brand which draws its values from Table 1


Example

5261 Compaq HP

6162 HP HP

How the relationship between these two tables can be generated so that integrity can be maintained?

Something like shown below. The line that enforces the data integrity is the FOREIGN KEY constraint

CREATE TABLE dbo.Table1
(
    Manufacturer VARCHAR(64) NOT NULL,
    Brand VARCHAR(64) NOT NULL,
    CONSTRAINT pk_Table1 PRIMARY KEY ( Manufacturer, Brand )
);

CREATE TABLE dbo.Table2
(
	PN INT NOT NULL,
	Manufacturer VARCHAR(64) NOT NULL,
	Brand VARCHAR(64) NOT NULL,
	CONSTRAINT fk_Table2_Manufacturer_Brand FOREIGN KEY (Manufacturer, Brand) REFERENCES dbo.Table1(Manufacturer,Brand),
	CONSTRAINT pk_Table2_PN PRIMARY KEY (PN)
)
1 Like

Where is dbo.Business?

My scenario is that

There is a table dbo.business

There is another table dbo.PartNumber which has two columns (Manufacturer and Brand) getting their values from the table business.

If you want to get a list of Manufacturers or Brands, you will get it from a query on dbo.PartNumber.

I do not want to generate The table Manufacturer, and Brand before PN is generated.

I hope this is clear