how to check constraints on two tables without primary key -foreign key relationship

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

[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.

[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

[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

[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'