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??

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.

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

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

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'