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.
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!!
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)
)
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
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.
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).
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.