Vfrdtyky

I can not find out what is wrong that i am getting the error message:

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

Here is that section of the query:

CREATE TABLE Airplane_type

(

make VARCHAR NOT NULL,

model VARCHAR NOT NULL,

type VARCHAR NOT NULL,

business_capacity INT NOT NULL,

economy_capacity INT NOT NULL,

range INT NOT NULL,

weight INT NOT NULL,

length INT NOT NULL,

wingspan INT NOT NULL,

PRIMARY KEY(make, model)

);



CREATE TABLE Airplane

(

airplane_ID VARCHAR(3) NOT NULL PRIMARY KEY,

make VARCHAR NOT NULL FOREIGN KEY REFERENCES Airplane_type(make),

model VARCHAR NOT NULL FOREIGN KEY REFERENCES Airplane_type(model)

);

Any help is greatly appreciated, thanks

You've created the primary key on two columns, shouldn't the foreign key be created on two columns rather than individually? You can't reference the model column in an individual foreign key because there is nothing in the original table that guarantees that model is unique.

CREATE TABLE Airplane

(

  airplane_ID VARCHAR(3) NOT NULL PRIMARY KEY,

  make VARCHAR NOT NULL,

  model VARCHAR NOT NULL,

  CONSTRAINT FK_Airplane_type FOREIGN KEY (make, model) 

    REFERENCES Airplane_type(make, model)

);

Also, why are you using VARCHAR without specifying a length? Please read this blog post:

• Bad habits to kick : declaring VARCHAR without (length)

The column referenced by a foreign key has to be constrained by a primary key or unique index. Since your primary key is the composite of two columns (make, model), the foreign key should also reference that same composite pair.

CREATE TABLE Airplane_type

(

airplane_type_id not null Identity(1,1),

make VARCHAR NOT NULL,

model VARCHAR NOT NULL,

type VARCHAR NOT NULL,

business_capacity INT NOT NULL,

economy_capacity INT NOT NULL,

range INT NOT NULL,

weight INT NOT NULL,

length INT NOT NULL,

wingspan INT NOT NULL,

CONSTRAINT PK_AIRPLANE_TYPE PRIMARY KEY(airplane_type_id),

Constraint UK_Spotted_By_AARON Unique Key (make, model)

);



CREATE TABLE Airplane

(

airplane_ID VARCHAR(3) NOT NULL,

airplane_type_id int not null CONSTRAINT FK_Airplane_AirPlane_Type 

  Foreign Key References(AirPlane.Airplane_type_id),

Constraint PK_Airplane Primary Key (Airplane_id),

);

Using parts of a compound key as a constraint isn't a goer a far as I know, and if you normalise, it's unnecessary. The way you had your schema an airplane with a make and model combination not in your airplane type table was valid.