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)
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?
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
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) )
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.
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.
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?
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
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.
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]
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.??
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
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.
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).
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.
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.
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
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.
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
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...
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.
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
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.
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?
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?
