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.
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?
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.??
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null], ClientCode [unique index varchar - not null], name [varchar null], surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
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.
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
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
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).
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.
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
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?
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
Pls let me know How I generate script for All primary keys and foreign keys in a table. Thereafter that can be used to add primary keys and foreign keys in another databse with same structure.
Also how I script default and other constraints of a table?
"Violation of PRIMARY KEY of restriction 'PK_Approve_Overtime'. The overlapping key cannot be inserted in object 'Dbo.Approve_Overtime'. The statement was ended." can soemone explain to me why i have this kind of error? i have this two tables. approve_overtime table has a primary key id_no and application_input table with a primary key of id_no! all the values from of application_input will be stored also in approve_overtime. sometimes the datas can be stored.sometimes it cannot and produces an error!
Using SQL Svr 7.0. It appears that primary keys are created as nonclustered unique indexes. Is there a configuration setting I can use to make them be created as clustered unique indexes?
If a table has a column defined as Int, Identity(1,1) which is to be used as the primary key, should that index be defined as clustered or non-clustered? In Enterprise manager when you create a PK on a table it defaults to being a clustered index. I am sure the answer depends on the other index requirements and columns in the table but I'd like to see what other ppl think about this.
I have read that SQL Server tables can't have more than one primary key. I know in Access two keys are allowed. Why can't there be two primary keys in a single table in SQL Server 7.
I accidently removed the primary keys from my table by mistake. Is there anyway ,That i can get the PK's back to what is used to be. Need Help pls...... When I try "resetting" the PK I kep getting this error:
'table_name' table - Unable to create index 'PK_tablename'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '1'. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors. [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: The table 'tDetail' has been created but its maximum row size (12521) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes. [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
In access you can have two fields that are primary keys with one or the other repeating as long as the combination is not repeated. i.e. key1 key2 200410 12345 200410 12346 200410 12588 etc for all 200410 there can not be a repeat of any value of key 2
is there a way to have this dual key in MSSQL :confused:
I have declared a table with a single column to be a Primary key. If I write 2 records, the first comprsing "A" and the second "a" then I get an exception about violating the uniqueness of the key. Is there a way that I can define the key to be case sensitive.
I have two tables with similar primary keys, table a and table b, and I want to find out all the key values that are disimilar between the tables. Can this be done with a select? if so what would it be.
I am not entirly sure what a forigen key is, is it a unique ID which is the same as the primary key? If adding a foreign key to a table that already has data, will it update each row with a unique ID or will it only create a unique ID on newly inserted records (for the foreign key)? If the foreign key is the same as the primary key then why do we need to even add a foreign key at all?For example a table like: Table Name : Customers--CustomerID uniqueidentity (primary Key)--FirstName nchar Table Name : Orders --OrderID int--CustomerID int Obviuously CustomerID are going to be the same in both tables, so why would you need to add a foreign key on 'CustomerID' to the 'Order' table, can't SQL match the customerID in each table any way? Cheers
HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it?
Hi, I have a question on pk? I have a db which has tables which do not have pk?I want to create Pk on each one of them - infact,I want to alter the table , add one more field and make it the pk . My question is - do I will have to go to each table,alter it and then create the pk on it or do someone have any script or better way to do it? Any help is appreciated.
Hi, Just a continuation to my earlier queries on replication. I have a db which I want to replicate - it do not have pk? I do not want to create pk on existing columns - so I thought of creating one more column in all the tables and make them pk . Any one has any idea if this will work fine or may give any problem which I should be prepare for. Any thought appreciated - pvd
