SQLTeam.com | Weblogs | Forums

Error on primary and foreign keys pls help


#1

CREATE TABLE COMPANY_DETAILS
(
COMPANY VARCHAR(20) NOT NULL,
CONTACT_NAME VARCHAR(20) NOT NULL,
ADDRESS VARCHAR(20) NOT NULL,
POST_CODE VARCHAR(5) NOT NULL,
PHONE INT NOT NULL,
PRIMARY KEY(COMPANY,POST_CODE),
)

CREATE TABLE CAR_ENTRY_DETAILS
(
CAR_NO VARCHAR(10) PRIMARY KEY,
COMPANY VARCHAR(20) NOT NULL REFERENCES COMPANY_DETAILS(COMPANY),
TYPE_OF_TYRE varchar(25) NOT NULL REFERENCES PRICE_LIST(TYPE),
DATE_OF_NEW_TYRE DATE NOT NULL,
MILAGE INT,
NO_OF_NEW_TYRES INT CHECK (NO_OF_NEW_TYRES<=5),
REMARKS VARCHAR(100) NULL
)

Msg 1776, Level 16, State 0, Line 41
There are no primary or candidate keys in the referenced table 'COMPANY_DETAILS' that match the referencing column list in the foreign key 'FK__CAR_ENTRY__COMPA__2A4B4B5E'.
Msg 1750, Level 16, State 0, Line 41
Could not create constraint. See previous errors.


#2

If COMPANY unique (on its own?) if so make that the PKey.

if not you need BOTH the COMPANY and POST_CODE columns in the CAR_ENTRY_DETAILS for the Foreign Key.

Personally, If COMPANY is not unique, I would not make the Primary Key on the composite of COMPANY and POST_CODE, I would use a [single column, with a unique value] Surrogate Key instead (for example an IDENTITY number) and use that in CAR_ENTRY_DETAILS ... but ask two Database Professionals for an opinion on Surrogate Keys and you will get Three Answers!!


#3

In CAR_ENTRY_DETAILS table i want only
COMPANY VARCHAR(20) NOT NULL as a foreign key from COMPANY_DETAILS table,

(INVOICE TABLE STRUCTURE IS BELOW)
IN INVOICE table i want
COMPANY VARCHAR(20) NOT NULL and POST_CODE VARCHAR(5) NOT NULL, as a foreign key from COMPANY_DETAILS table,
(INVOICE TABLE STRUCTURE IS BELOW)

SO PLEASE LET ME KNOW HOW TO DEFINE THE FOREGIN KEY IN COMPANY_DETAILS FOR THE ABOVE SCENARIO.

CREATE TABLE INVOICE
(
INVOICE_NO INT NOT NULL PRIMARY KEY IDENTITY(1,1),
DATE_OF_BILL DATE DEFAULT GETDATE(),
COMPANY_NAME VARCHAR(20) REFERENCES COMPANY_DETAILS(COMPANY),
POST_CODE VARCHAR(5) REFERENCES COMPANY_DETAILS(POST_CODE),
PHONE VARCHAR(10) REFERENCES COMPANY_DETAILS(PHONE)
)


#4

Sorry, but you can't. The foreign key can only "link" on the primary key, so all tables referencing COMPANY_DETAILS can only do so on whatever the Primary Key is defined as.

If you had both a COMPANY table and also a COMPANY_ADDRESS table, where the COMPANY_ADDRESS table had PKey columns for COMPANY and POST_CODE then you could reference COMPANY_ADDRESS from INVOICE [and you could also reference COMPANY from INVOICE just on the COMPANY column], and you could reference the COMPANY table from CAR_ENTRY_DETAILS


#5

Sounds like you want to have multiple addresses for Companies? (and associate the Invoice with Company + Postcode)?

If so you would be better with a COMPANY table, which just defines the COMPANY VARCHAR(20) and then have a separate COMPANY_ADDRESS table that has COMPANY, and all the Contact/Address/Phone columns.


#6

A FK can link to any unique index, not just a PK. [Sadly, for some reason, you can't have a FK to a non-unique index.]

Thus, if COMPANY is not unique, you can't have a FK to just it.

Btw, to reduce overhead, POST_CODE should be CHAR(5) not VARCHAR(5), since a post code is always the same length.


#7

Generally I very strongly favor natural keys, but names should not be used as keys anyway. Companies change names all the time. Post codes can also change. In this case, a surrogate key is necessary.

Use a numeric code in place of COMPANY name, and a unique code in place of (COMPANY, POST_CODE) combination, if that's the way you want to identify a company (never seen it done that way, though, much more common to use a geographical name rather than a post code).


#8

Doh! Thanks for that, dunno why that had not occurred to me and not sure I've ever come across circumstances where I might want to take advantage of it - although I can, of course, envisage that they exist, just my brain doesn't think that way.

If COMPANY is unique within COMPANY_DETAILS then it doesn't make sense, to me, to want to FKey on COMPANY, POST_CODE. If COMPANY is not unique then it would need another, "higher level" table for a definition of COMPANY where that would be unique.