Can two Tables have the same primary key ?

I have been given a project to complete where ... two tables "Flights" and "ScheduledFlights" have a column called "FlightNo" . In both these tables it is mentioned that "FlightNo" is primary key ?
Is this possible ? i am not talking about foreign key here....

Here is the script that i have created..

create table flights
(
FlightNo char(5) constraint FlightNo primary key clustered not null,
DepTime char(5) not null,
ArrTime char(5) not null,
AircraftTypeID char(4) references Aircraft(AircraftTypeID) not null,
SectorID Char(5) references Sector(SectorID) not null
)

create table ScheduledFlights
(
FlightNo char(5) constraint FlightNo2 primary key clustered not null,
FlightDate datetime not null,
FirstClassSeatsAvailable int not null,
BusinessClassSeatsAvailable int not null,
EconomyClassSeatsAvailable int not null
)

Now the problem is with FlightNo attribute .. ? Can two Tables have the same primary key ?
(FlightNo2 is given as a temporary solution)

Hi

I think your question sounds a bit ambiguous because you do not distinguish between Primary Key itself and Primary Key's Name.

Two tables can have primary keys defined on the columns with the same name. But no two tables can share the same Primary Key name. To awoid confusion you can call your Primary keys :

CONSTRAINT pkFligts PRIMARY KEY (FlightNo )

CONSTRAINT pkSheduledFligts PRIMARY KEY (FlightNo )

NB.

|||Thanks. I will ask my teacher about that... can u tell me one more thing ...

I have another table called " Passenger" which has an attribute called Travel Date which is>

"Date Of travel. The flight number and the date of travel together form a foreign key that references the flight number and flight date in the flight table."

In short ,

FlightNo(in Flights Table)+DeptTime(in Flights Table)

how will i write this in the create table statement... sorry i am learning if this sounds stupid.|||

That explains it :-)

When you need to create a multi-column Foreigh Key , you add your constraint separately after a list of columns. Make sure , the columns references by

CREATE TABLE Passenger

( <your list of columns>,

CONSTRAINT fkPassengerFlights FOREIGN KEY (FlightNo, DeptTime)

REFERENCES Fligts (FlightNo, DeptTime)

)

NB. Good luck with your studing.

|||

Hello,

What you need is a composite foreign key. A composite key is made of more than one column. A composite primary key will have say col1, col2 and col3 etc in it. Any foreign key referencing such composite primary key will have col1, col2 and col3 in it. Such a Foreign Key is called Composite Foreign Key. Below is a sample code..hope it helps...

create table.....

constraint [FK_AnyRelevantName] Foreign Key (col1, col2, col3)

References [TableName_Containing_Primary_Key] (Col1, Col2, col3) -- these will be the primary key columns defined on another table.

I Hope this is helpful.

Thanks......

|||Thank you very much for taking time off and helping me out.