Member Avatar for arbazpathan

i have two tables in my sql database one is "vendor" and other "purchase_order" they both have one common attribute "vendor_name" my problem is that i want vendor name in my "purchase_order" table to be same as that in "vendor" table but vendor_name is not a primary key in "vendor" table so i cannot use primary key_foreign_key reference. what should i use??

  • 3 Contributors
  • 4 Replies
  • 202 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by adam_k

Dani AI

Generated

Best practice is to make referential integrity use the vendor_id surrogate key rather than vendor_name. As observed, a foreign key to a non-PK column is possible only if that column is made UNIQUE; as noted, triggers or app-level checks are workable fallbacks. The guidance below gives a safe, practical migration path plus alternatives and cautions.

Add a nullable vendor_id to purchase_order, populate it by matching names (handling duplicates), then enforce a proper FK. Example steps (test on a copy/backup first):

ALTER TABLE purchase_order ADD vendor_id INT NULL;

Find duplicate vendor_name values:

SELECT vendor_name, COUNT(*) AS cnt
FROM vendor
GROUP BY vendor_name
HAVING COUNT(*) > 1;

If duplicates exist, choose a canonical vendor_id per name and use it to populate purchase_order.vendor_id:

WITH keepers AS (
  SELECT vendor_name, MIN(vendor_id) AS keep_id
  FROM vendor
  GROUP BY vendor_name
)
UPDATE p
SET vendor_id = k.keep_id
FROM purchase_order p
JOIN vendor v ON p.vendor_name = v.vendor_name
JOIN keepers k ON v.vendor_name = k.vendor_name;

Then add the FK and, if appropriate, make vendor_id NOT NULL and remove or keep vendor_name as a denormalized label:

ALTER TABLE purchase_order
ADD CONSTRAINT FK_purchase_order_vendor
FOREIGN KEY (vendor_id) REFERENCES vendor(vendor_id);

Notes and troubleshooting: normalize strings (LTRIM/RTRIM, consistent casing or COLLATE) before matching; resolve ambiguous duplicates manually or with business rules; triggers provide immediate checks but hurt write performance in high-throughput systems. If changing schema is impossible, the other legal option is to add a UNIQUE constraint on vendor(vendor_name) and then add a foreign key to that column — acceptable but fragile because names change. Always perform these operations inside transactions on a development copy and keep backups.

Recommended Answers

All 4 Replies

Member Avatar for adam_k

What are you trying to do with this? How much volume/read-writes?
You can create a trigger that will verify that your vendor_name exists in the vendor table, but that will slow down your table and in a high traffic environment it can be a problem.
If the purchase_order get's handled through a program and it's not a vital piece of information, then make sure that your program does it's job and create a night/weekly/monthly job to get any records that don't match the vendor_name against the vendor table.

Edited by adam_k because: n/a
Member Avatar for tesuji

Hello arbazpathan,

primary-key property is not required for table "vendor" if its "vendor_name" is already declared to be unique. If so, you can easily put a foreign-key constraint on table "purchase_order" referencing vendor(vendor_name) by means of "create table purchase_order(...)" or by "Alter table purchase_order ..." if table "purchase_order" already exists.

However, I am afraid that both tables already exist containing some data records, and, unfortunately, "vendor_name" of "vendor" is not declared to be unique. If so, your database design is inappropriate to model an one-to-many relationship correctly.

As adam_k already stated, a SQL trigger-program could be a good work-around.

I've got the feeling that there might be other potential solutions, as for example when checking whether entries in vendor table exist or not might happen within a procedural Java or PHP program.

So it would be a good idea to show more details by posting the really complete creation-statements of both tables "vendor" and "purchase_order" and also appropriate SQL statements for your wanted data processings.

-- tesu

Edited by tesuji because: n/a
Member Avatar for arbazpathan

Hello arbazpathan,

primary-key property is not required for table "vendor" if its "vendor_name" is already declared to be unique. If so, you can easily put a foreign-key constraint on table "purchase_order" referencing vendor(vendor_name) by means of "create table purchase_order(...)" or by "Alter table purchase_order ..." if table "purchase_order" already exists.

However, I am afraid that both tables already exist containing some data records, and, unfortunately, "vendor_name" of "vendor" is not declared to be unique. If so, your database design is inappropriate to model an one-to-many relationship correctly.

As adam_k already stated, a SQL trigger-program could be a good work-around.

I've got the feeling that there might be other potential solutions, as for example when checking whether entries in vendor table exist or not might happen within a procedural Java or PHP program.

So it would be a good idea to show more details by posting the really complete creation-statements of both tables "vendor" and "purchase_order" and also appropriate SQL statements for your wanted data processings.

-- tesu

the problem is that i already have a different primary key named vendor_id in my vendor table that's why i cannot use primary key foreign key constraint

Member Avatar for adam_k

Can you inform us what you are building (windows program, web, sql query)?

If your goal is to just make sure that you've got a valid vendor_name in your purchase_order table, then I guess you could also make a function to be used with a constraint.

Also another question: Why don't you use the vendor_id in your purchase_order table? Won't it take up less space and provide pk-fk relationship?

And yet another question: If you don't like any of the solutions offered in this thread, can't you use a select (or a join )in your insert statement to make sure that you've got a vendor_name that exists in the vendor table?
eg:

insert into purchase_order
(po_number, date, vendor_name) 
select '11',getdate(), vendor_name from vendor
where vendor_name ='adam_k'
Edited by adam_k because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.