I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.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),
)CREATE TABLE dbo.objScores ( tmp int IDENTITY(1,1) PRIMARY KEY, objective varchar(50) NOT NULL, courseId varchar(20) NOT NULL CONSTRAINT FK_course FOREIGN KEY (courseId) REFERENCES courseScores (courseId) )
Once I get it working, then the tmp will be gone and then set 3 foreign keys as the clustered primary, fyi.Not sure how to reference half a primary key?Any help is greatly appreciated.....Thanks all,Zath
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr]( [NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL, [ContactKey] [int] NOT NULL, [EventTypeEnum] [tinyint] NOT NULL,
I have created two tables. table one has the following fields,
Id -> unique clustered index. table two has the following fields, Tid -> unique clustered index Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
I have a really super slow stored proc that does something simple. it updates a table if certain values are received.
In looking at this the matching is done on the Primary Key, which is set as a Clustered index, looking further I have another constraint, that sets the same column to a Unique, Non-Clustered.
I am not sure why this was done, but it seems to be counter productive. I have read only references to Which one is better on a primary key, but not can their be both and if it is "Smart".
I've a table with primary key defined as non-clusterd, now without dropping it can I modify the existing index to clustered through tsql as I had to write some migration script and in that script I wanna do this.
I'm changing the collation sequence of a field which is a primary, clustered key field via:
ALTER TABLE [dbo].[clusterAlgorithm] WITH NOCHECK ADD PRIMARY KEY CLUSTERED ( [ClusterAlgorithmClassName] ) ON [PRIMARY] GO
Is there a way to drop the primary key designation before doing an alter table/alter column statement and then recreating the key, or must I drop and recreate the table?
I want to keep certain archive data in certain tables. One such table is currently about 190 GB in size. It has a primary key with clustered index and three non-clustered indexes. The type of queries fired are strictly selects (daily) and inserts (only monthly).
Question: Is it advisable to have a non-clustered index on the primary key column?.....I am finding that the insert performance is getting hurt due to presence of clustered index on such a large table (190 GB).
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] ( [Id] [int] NOT NULL , [Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED ( [Id] ) ON [PRIMARY] GO
The records that i added are:
Id Code
1 a 2 b 3 aa 4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a 3 aa 2 b 4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a 2 b 3 aa 4 bb
as per the primary key order that is a non clustered index.
All of the 3 books I've read say it is not a good idea to create a clustered index on the primary key but it is created as the default. My question is has this changed in 2005? My understanding is to create the clustered index on columns used first in join clauses and then in where clauses, what is the answer?
OK - I have a two tables in a database. Table one contains an ID, 'oneID', field as the primary key. It is auto-incremented. Table two has ID field, 'twoID', as the primary key. This field also auto-increments. Table two also has 'oneID' as the foreign key. Now, my question is, how do I get the foreign key in table two to auto-increment in conjuction with table one's primary key? They are after all the same data. Do I have to manually code to get table one data and save it to table two data? thanks
I'm currently using Oracle and receiving this error:
SQL Error: ORA-02291: integrity constraint (43437001.ATHLETE_FK1) violated - parent key not found 02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found" *Cause: A foreign key value has no matching primary key value. *Action: Delete the foreign key or add a matching primary key.
When inserting this value:
INSERT INTO ATHLETE VALUES ('A001','TOM','HANKS','03-FEB-90','USA','C001');
I have an application in which i need to get the foreign key fieldsfrom a table and then get all the foreign keys primary key field fromthe linking table. Could some one tell me how i do this usingINFORMATION_SCHEMA. I have tried and can get the foreign keys but notsure how to get the associated primary keys.
Hi, In my table1, I have a composite primary key ssn+firstname+lastname. In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key? table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?
I'm using MS SQL Server Studio Express 9.0. When selecting SQL Server Compact Edition, I'm able to create tables and primary keys with no problem. But how do I create a relationship with another table (foreign key)?
I am trying to drop a primary key on column LID and then create a clustered index on a new identity column ID and then add the primary key back on the LID. I am not able to do so due the table being in replication. here is the error:
Cannot alter the table '' because it is being published for replication.
How do I get past the error and create the Clustered Index on ID column in both publisher and subscriber?
Hi All, It seems I have been requested to carry out a complex query and the best way I think I can do this is with the use of a stored procedure. The problem is that I am not quite sure whether my SP is stated correctly and also how I would go about stating the SP in my VB.net code!
I would be ever so grateful if somebody could look over my SP code and possibly recommend a way of stating my code. My ability is limited so I would appreciate it if examples could be used with possible relations to my problem.
The SP should state that Department should appear as the end result of the query when the page is loaded. So when a row is selected in tblRisk, dependant upon what the Dept is in that table, it then populates the department in which it is associated with from tblDept. I have left the SP below.
Many Thanks, Kunal
CREATE PROCEDURE dbo.ShowMe @yourInputValue INTAS SELECT tblDept.Department FROM tblDept JOIN tblRisk ON tblDept.Ref = tblRisk.Dept WHERE tblDept.Ref = @yourInputValue RETURN 0GO
I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?
Hi, How do I delete data which is a Foreign Key in another table? For example; string query = "DELETE * from user_details WHERE user_ID = '" +userID.Text+ "'; The user_ID is the Primary Key in the user_details table and also a Foreign Key in other tables. Thank you. (:
I am having trouble dropping constraints(Primary and Foreign Keys). I would like to do so so I can truncate the tables and repopulate them. Any time I use the DROP CONSTRAINT #### on one table, I get an error message saying this is referenced in another table. Any help in how to drop the keys so I can truncate the tables in a database would be appreciated. I must be overlooking something simple. Thanks for the help.
create table z ( eno int, deptno char(2), ename varchar(5), constraint pk_eno_deptno primary Key(eno,deptno) ) and I tried to establish a foreign key based on the above defined table. It's giving error,
create table z_dup ( eno int FOREIGN KEY REFERENCES z(eno), deptno char(2) ) go
Error:- ***** There are no primary or candidate keys in the referenced table 'z' that match the referencing column list in the foreign key 'FK__z_dup__eno__18CE1BA6'.Could not create constraint. See previous errors.
Even I tried to create a foreign key like the following,
create table z_dup ( eno int FOREIGN KEY REFERENCES z(eno,deptno), deptno char(2) ) go
Error:- ******** More than one key specified in column level FOREIGN KEY constraint, table 'z_dup'.
Can anyone please help me to create a foreign key relation with the base table.
I have two tables. CREATE TABLE Service( SERVId varchar (10) NOT NULL, SERVName varchar (30) NOT NULL, SERVDesc TEXT NULL, PRIMARY KEY(SERVId) )
CREATE TABLE New( SERVId varchar (10) NOT NULL, NDId varchar (10) NOT NULL, NDName varchar (30) NOT NULL, NDDesc text NULL, PRIMARY KEY(SERVId,NDId) FOREIGN KEY(SERVId) REFERENCES Service(SERVId) )
Msg 547, Level 16, State 2 DELETE statement conflicted with COLUMN REFERENCE constraint 'FK__Newdev__SERVId__4939E6D2'. The conflict occurred in database 'test', table 'New', column 'SERVId' Command has been aborted.
The same problem occurs even if the second table i.e. new is declared as follows, i.e created with only one primary key: CREATE TABLE New( SERVId varchar (10) NOT NULL, NDId varchar (10) NOT NULL, NDName varchar (30) NOT NULL, NDDesc text NULL, PRIMARY KEY(NDId) FOREIGN KEY(SERVId) REFERENCES Service(SERVId) )
what might be the problem? But the same relation works on MS Access. Is there anyother way of declaring this kind of relationship i.e. primary and foreign key?Can anyone pl. help me in solving this problem of foreign key relation.
I have a query that returns contacts connected to a client, but the problem is that it's only returning a result when a contact is associated with a client, even if the client does exist in the db. i want it to still return the client if the client exists.
SELECT * FROM clients, addressbook where clients.clientid = addressbook.clientid AND (clients.clientname LIKE '" . strtoupper($_GET['txtfname'])."%')
Be warned, index padding is not included in this, and I'm not sure the fillfactor setting is correct.
set nocount on create table #PK(constraint_schema sysname not null, constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#PK primary key clustered(constraint_schema, constraint_name)) create table #cols(constraint_schema sysname not null, constraint_name sysname not null, column_name sysname not null, ordinal_position int not null, constraint PK_#PKcol primary key clustered(constraint_schema, constraint_name, ordinal_position)) create table #FK(constraint_schema sysname not null, constraint_name sysname not null, unique_constraint_schema sysname not null, unique_constraint_name sysname not null, sql varchar(4000) not null, constraint PK_#FK primary key clustered(constraint_schema, constraint_name))
insert into #PK select constraint_schema, constraint_name, 'ALTER TABLE ' + quotename(table_schema) + '.' + quotename(TABLE_NAME) + ' ADD CONSTRAINT ' + quotename(CONSTRAINT_NAME) + ' PRIMARY KEY ' + CASE WHEN si.indid<>1 THEN 'NON' ELSE '' END + 'CLUSTERED (>cols<) WITH FILLFACTOR=' + cast(si.OrigFillFactor as varchar) + ' ON ' + quotename(fg.groupname) AS SQL from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC INNER JOIN sysindexes si on TC.CONSTRAINT_NAME=si.name inner join sysfilegroups fg on si.groupid=fg.groupid WHERE CONSTRAINT_TYPE IN('PRIMARY KEY','UNIQUE')
insert into #fk select c.constraint_schema, c.constraint_name, c.unique_constraint_schema, c.unique_constraint_name, 'ALTER TABLE ' + quotename(F.table_schema) + '.' + quotename(F.table_name) + ' ADD CONSTRAINT ' + quotename(F.constraint_name) + ' FOREIGN KEY(>cols<) REFERENCES ' + quotename(r.table_schema) + '.' + quotename(r.table_name) + '(>rcols<)' AS sql FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS F INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C ON F.constraint_schema=C.constraint_schema AND f.constraint_name=c.constraint_name AND F.constraint_type='FOREIGN KEY' INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS R ON R.constraint_schema=C.unique_constraint_schema AND r.constraint_name=c.unique_constraint_name AND r.constraint_type in ('PRIMARY KEY','UNIQUE') ORDER BY F.table_name, r.table_name
insert into #cols select constraint_schema, constraint_name, COLUMN_NAME, ORDINAL_POSITION from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
update P SET SQL=Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<') FROM #PK P INNER JOIN #cols C ON P.constraint_schema=C.constraint_schema AND P.constraint_name=C.constraint_name WHERE C.ORDINAL_POSITION=@ctr
UPDATE F SET SQL=Replace(Replace(SQL, '>cols<', @delim + quotename(c.column_name) + '>cols<'), '>rcols<', @delim + quotename(r.column_name) + '>rcols<') FROM #FK F INNER JOIN #cols C ON F.constraint_schema=C.constraint_schema AND F.constraint_name=C.constraint_name AND C.ordinal_position=@ctr INNER JOIN #cols R ON F.unique_constraint_schema=R.constraint_schema AND F.unique_constraint_name=R.constraint_name AND C.ordinal_position=R.ordinal_position
select @ctr=@ctr+1, @delim=',' END set nocount on
update #PK SET SQL=Replace(SQL, '>cols<', '') update #FK SET SQL=Replace(Replace(SQL, '>cols<', ''), '>rcols<', '')
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.??
