Can Two Tables Have The Same Primary Key ?

Aug 1, 2006

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)

View 6 Replies


ADVERTISEMENT

Joining Two Tables Without Primary Key

Jan 4, 2008

Hi,

Suppose i have two tables employee and orders.Can i join these two tables with candidate key colums in each table.

View 1 Replies View Related

Change Primary Key In One Of Tables

May 26, 2015

I have inherited a SQL Server Database that has three tables related to one another.  The DB uses a Access 2013 front end. I know access fairly well, but am not all that competent when it comes to SQL Server. I am using SQL Server Management Studio 12.  I need to change the primary key in one of the tables.  When I do so, I get the message illustrated below.  How can I get around this?

I did find the place where there is a table option of "Prevent Saving changes that require table re-creation".  However, there seems to be a lot of comments that indicate it should not be turned off.  If I don't turn it off, how can I
make the changes to the table?

View 5 Replies View Related

Export Some Tables From One DB To Another WITHOUT Lost Of Primary Key?

Jun 7, 2005

Hi,
i am using the enterprise manager of our sql server 2000. i want to
export some (not all) tables from one DB to another one with all the
data inside these tables AND the primary keys. if i do i only receive
the tables with the data but all primary keys and indexes will be lost.
how to do it?
thanks, tobi

View 7 Replies View Related

SQL Db Create Tables And Cluster Primary Key

Jun 21, 2005

With the last table being created below, it has a clustered primary key.One of the fields it is referencing on the previous table, courseId, can NOT be unique.But without it being unique, the cluster primary key won't work.Is there another way to achieve what I am trying to do here?CREATE TABLE dbo.courseScores   (          courseId varchar(20) NOT NULL     CONSTRAINT FK_courseId_courseStructure2 FOREIGN KEY (courseId)    REFERENCES courseStructure (courseId), 
          studentId varchar(20) NOT NULL    CONSTRAINT FK_studentId_students2 FOREIGN KEY (studentId)    REFERENCES students (studentId), 
     CONSTRAINT PK_courseScore PRIMARY KEY CLUSTERED (courseId, studentId)
          )
 
CREATE TABLE dbo.objScores  (             objID varchar(20) NOT NULL    CONSTRAINT FK_objId_objstructure FOREIGN KEY (objID)    REFERENCES objStructure (objID), 
    studentId varchar(20) NOT NULL    CONSTRAINT FK_studentId_students3 FOREIGN KEY (studentId)    REFERENCES students (studentId), 
    courseId varchar(20) NOT NULL    CONSTRAINT FK_course FOREIGN KEY (courseId)    REFERENCES courseScores  (courseId),                     CONSTRAINT PK_objScores PRIMARY KEY CLUSTERED (objID, studentId, courseId)           )
Thanks all,Zath

View 1 Replies View Related

Determining What Tables Do Not Have Primary Keys

Aug 14, 2001

Does anyone have a script that will roll through the tables in a database and identify tables without primary keys defined? I did not see any in the online script database.

Thanks,
Rick

View 1 Replies View Related

Getting Primary Key Column Names For All Tables

Sep 9, 1999

I have a situation in which i have to get the last value stored in the Primary Key for all the tables. Based on this value i have to update another table which stores the Table names and the last Key value for the table. The values in this table are not correct therefore i have to update it now. I was trying to write a cursor for this but the only problem is i can't get to know how to get the column name on which the primary key is defined for all the tables, thru code.

I would appreciate if someone could help me out with this.

View 4 Replies View Related

Export/Import Tables W/ Primary Key

Nov 14, 2003

Can someone tell me if there's a way to export data from one database to another without losing the primary key information that has been set in the source database?

Please respond to URL. Thanks!

CC

View 1 Replies View Related

T-SQL (SS2K8) :: Search All Tables For Primary Key Value?

Feb 26, 2015

I found a few 'SearchAllTables' scripts out there to find a value in any table/column/row of any Database.

My problem is that none of these database search queries seem to look at primary key values.

The value I'm looking for is a primary key - I need to find all the other tables that have this primary key value.

how I could accomplish this?

View 2 Replies View Related

How To List Tables With Primary Keys

Jun 9, 2007

Hello,We imported a bunch of tables from a database and realized that theprimary keys weren't copied to the destination db. In order to re-create the keys, we need to know which tables have them. Is there acommand that I can use (on the source db) to find out which tablescontain primary keys? The db has hundreds of tables and I'd rather notgo through each one to see which has a primary key.Also, for future reference, is there a way to include the primary keyon an import?Thanks,Peps

View 2 Replies View Related

Primary Keys And Joining Tables

Aug 24, 2015

I've created a table called Employees with a primary key called EmployeeID.  The table contains EmployeeID, FirstName and LastName columns.  I now want to create a table called Team which will contain the columns TeamID, EmployeeID (to reference the column EmployeeID from the Employee table) and a column called TeamName.  Sql won't let me create multiple primary keys in one table (I did think that was the case ) key but yet if I look at the Adventure Works database in the Person.PersonPhone table, I can see three primary keys defined.

View 3 Replies View Related

Can Only Use Primary Key And Not Foreign Key While Joining Tables?

Oct 9, 2015

I was trying a joining example provided in my book in which customer is a table and person is another table. The query provided in the book is this... USE AdventureWorks2012;

GO
SELECT c.CustomerID, c.PersonID, p.BusinessEntityID, p.LastName
FROM Sales.Customer AS c
INNER JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID;

This is the query that I did....

SELECT
      c.CustomerID,p.FirstName,p.MiddleName,p.LastName
FROM
      Sales.Customer AS c INNER JOIN Person.Person AS p
ON
      c.CustomerID=p.BusinessEntityID

ORDER BY
         p.BusinessEntityID;

Keys :-

Person Table
[PK_Person_BusinessEntityID]
[FK_Person_BusinessEntity_BusinessEntityID]

Customer Table
[PK_Customer_CustomerID]
[FK_Customer_Person_PersonID]

However,both of them gives a very different result set.But my question is why do we need to use the Customer.PersonID instead of Customer.CustomerID. Is it really important to use one primary key and one foreign,is there any specific reason why the book showed c.personId=p.BusinessEntityId.??

View 3 Replies View Related

Script: Find Tables Without Primary Keys

Aug 3, 2002

...got tired of looking at them by hand.

Cheers
-b

DECLARE @vcDB varchar(20),@vcSchema varchar(20),@vcTable varchar(200)

Select @vcDB='mydb',@vcSchema='dbo'

DECLARE cLoop cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=@vcDB
and TABLE_SCHEMA=@vcSchema
order by TABLE_NAME ASC

open cLoop

FETCH NEXT FROM cLoop INTO @vcTable
WHILE @@FETCH_STATUS=0
BEGIN
if not exists (SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = @vcSchema
AND TABLE_NAME = @vcTable
AND CONSTRAINT_TYPE = 'PRIMARY KEY')
print @vcTable + ' does not have a primary key'

FETCH NEXT FROM cLoop INTO @vcTable
END
Close cLoop
DEALLOCATE cLoop

View 2 Replies View Related

Do Tables Using A Different Filegroup Log Trans To The Same Transaction Log Used By Primary?

Sep 26, 2007

we like some of the advantages filegroups offer but wonder if the purpose is somewhat defeated if it is true that tables sitting on other filegroups still have to work with the same transaction log that the db's primary tables work with.

View 4 Replies View Related

How To Find All Tables That Have An Identity Column As A Primary Key

Mar 6, 2008

How do i find all the tables that have an Identity Column as a primary key in a database. Thanks.

View 8 Replies View Related

Correct Way Of Finding A Tables Primary Keys??

Aug 7, 2007

Hope this is in the right thread, sorry if not!

I have run into a problem, i need to find out that column(s) in a table that makes the primary key.
I thought that this code did the trick.
***
DECLARE @c varchar(4000), @t varchar(128)
SET @c = ''
SET @t='contact_pmc_contact_relations'
Select @c = @c + c.name + ',' FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id inner join sysindexkeys k on o.id = k.id WHERE o.name = @t and k.colid = c.colid ORDER BY c.colid
SELECT Substring(@c, 1, Datalength(@c) - 1)
***

This works in most of my cases. But i have encounterd tabels where this code doesn't work.
Here is a dump from one of the tabels where it doesn't work.
SELECT *
FROM sysindexkeys
WHERE (id = 933578364) <--id of the table
***
id indid colid keyno
933578364 1 1 1
933578364 1 2 2
933578364 2 1 1
933578364 3 2 1
933578364 4 3 1
933578364 5 4 1
933578364 6 5 1
933578364 7 6 1
933578364 8 7 1

Not sure if that dump made any sense, but i hope it did.
If i look at the table in SQL Enterprise manager there is no relations, no indexes only my primarykey made up with 2 columns (column id 1 and 2).

So, anyone know how i could solve this problem?


Regards
/Anders

View 8 Replies View Related

Updating Tables In Sequence With Primary Key And Foreign Key Relations

Feb 7, 2007

Hi all,
       In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations
       Right now my code looks like this
       foreach(object obj in Objects){
       int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
       int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
       ///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
       }
      The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
 
Thanks
Regards
Prasad.

View 2 Replies View Related

DTS - Primary Keys Drop When Transferring Tables Between Servers

Sep 7, 2000

Hello All,

Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.

Many thanks in advance for any and all help,

Gurmi

View 1 Replies View Related

Exporting Tables Along With Their Primary And Forgen Keys And Records

Jun 27, 2007

Hi all,


I’m trying to export 120 tables from SQL server 2000 to SQL server 2005 with their Primary and corresponding records.
Is there way to do this?

Thanks for any help.

Abrahim

View 6 Replies View Related

How To Find All Primary And Foreign Key Columns In All Database Tables

Apr 17, 2014

how to find all primary key columns & foreign key columns in all database tables?

View 1 Replies View Related

SQL - Foreign Key With References Of Multiple Tables With Same Primary Key Field

Apr 9, 2007

I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag

View 3 Replies View Related

How Do I Create A Composite Primary Key, Using Fields From Multiple Tables?

May 22, 2007

Hi All



I hope someone smart can help me, it would be highly appriciated.



I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.



TblOrders: OrderNum, Orderdate, ....

TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress



The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.



Can this be done, and how.



Many thanks



Kind regards

Tina

View 3 Replies View Related

How To Publish Tables Without Primary Keys In Transactional Replication

May 16, 2007

Hi All

I am a newbie to replication. I just want to know Is there any way to publish tables (articles) without primary keys in transactional replication.



Early Thanks,

Salman Shehbaz.

View 1 Replies View Related

SQL 2012 :: Insert Data Onto Tables Having Primary And Foreign Key Relations?

Oct 31, 2015

Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?

For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.

View 3 Replies View Related

Enter Default Values For All Columns In All Tables Except Primary Keys

Jul 23, 2005

How can i enter Default Values of " " to all the columns of type characterof all the tables (excluding system tables) and Default Values of 0of all columns of type numbers. Excluding all primary key columns.Thank you

View 1 Replies View Related

Reporting Services :: How To Create 2 Tables With Primary / Foreign Key Relationship

Jun 6, 2015

I want to create a table with primary key , and put relationship with second table.

View 5 Replies View Related

How To Join Two Tables Having No Common Coloumns And Having No Primary Key Fields In Any Of The Table

Apr 14, 2008

hi friends i am reading a csv file and storing the data in table i have created the tables like this:

coloumn names datatype constraints

start_date_time varchar(50) allownull = true
ani varchar(50) allownull=true
dialed_digit varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true

and the second table as

coloumn names datatype constraints

country varchar(50) allownull = true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true

now i want to relate and join the table so that i can create a view with the following coloumns such as

start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...

View 11 Replies View Related

Inserted Records Missing In Sql Table Yet Tables' Primary Key Field Has Been Incremented.

Jun 18, 2007

I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table.  Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.

View 5 Replies View Related

SQL Server 2014 :: 2 Tables Combined Left Join To Primary Key Of A 3 Third Table

Sep 1, 2014

Looking to improve performance of the following code.

It basically generates future days for each dog. So there is a dog table and a day table with every day.

These 2 table cross join and then fill in missing rows. As time moves i will fill in further future dates but will need the initial insert to be a reasonable query.

All columns are covered by index's but the queries at the end take quite a long time. I would hope for index scan to just point out the missing rows especially on the final query.

How to make the last query as fast as possible.

IF OBJECT_ID('dbo.[AllDates]', 'U') IS NOT NULL
DROP TABLE dbo.[AllDates]
CREATE TABLE dbo.[AllDates] (
[Date] date not null PRIMARY KEY
)
;WITH Dates AS

[Code] .....

View 2 Replies View Related

How To Find Missing Records From Tables Involving Composite Primary Keys

Nov 23, 2006

Table 1







     Code
    Quarter

500002
26

500002
27

500002
28

500002
28.5

500002
29

 

Table 2







     Code
           Qtr

500002
26

500002
27

 

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result







    Code
   Quarter

500002
28

500002
28.5

500002
29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

View 3 Replies View Related

SQL Server 2012 :: Find Queries That Lock Tables Or Not Using Primary Key While Running Update

Jul 20, 2015

I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.

If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.

If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.

View 2 Replies View Related

Import Csv Data To Dbo.Tables Via CREATE TABLE &&amp; BUKL INSERT:How To Designate The Primary-Foreign Keys &&amp; Set Up Relationship?

Jan 28, 2008

Hi all,

I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:

-- ImportCSVprojects.sql --

USE ChemDatabase

GO

CREATE TABLE Projects

(

ProjectID int,

ProjectName nvarchar(25),

LabName nvarchar(25)

);

BULK INSERT dbo.Projects

FROM 'c:myfileProjects.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=======================================
-- ImportCSVsamples.sql --

USE ChemDatabase

GO

CREATE TABLE Samples

(

SampleID int,

SampleName nvarchar(25),

Matrix nvarchar(25),

SampleType nvarchar(25),

ChemGroup nvarchar(25),

ProjectID int

);

BULK INSERT dbo.Samples

FROM 'c:myfileSamples.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO
=========================================
-- ImportCSVtestResult.sql --

USE ChemDatabase

GO

CREATE TABLE TestResults

(

AnalyteID int,

AnalyteName nvarchar(25),

Result decimal(9,3),

UnitForConc nvarchar(25),

SampleID int

);

BULK INSERT dbo.TestResults

FROM 'c:myfileLabTests.csv'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = ''

)

GO

========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.

2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?

Please help and advise.

Thanks in advance,
Scott Chang

View 6 Replies View Related

Creating Indexes On Columns That Are Foreign Keys To Primary Keys Of Other Tables

Jul 16, 2014

what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:

[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------

The foreign key above is as:

ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])

What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:

CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)

In other words, what's best practice for adding an index which best supports a Foreign Key constraint?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved