i am trying to set up a customer table with several personal details, including Login and password combined to make a unique identifier. I have made Login the key to the table, but how do i make Login and Password a combined identifier. When i click key in password it just removes the key from Login and places it there.
Also how do i include a foreign key in another table ??
Sorry if these questions seem a little lame, I am only a beginner.
Hello. Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have: CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
why it is not possible to create a Foreign key to a Unique constraint?
Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.
He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;
The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.
Ok - these two columns ar no PK but the hold an Unique constraint......
I working a tutorial where I will learn a lot of new stuff, LINQ being one of them. As a part of this tutorial, there are four tables and foreign keys. My db skills were limited to simple tables and i've nevr done anything where I related tables before. I'm a bit confused about foreign keys and how to set them up. I think I'm setting up right, but I have a doubt. I have a couple of screen caps to show you what where my doubt comes from at: http://www.noelakins.com/keys.htmlWhat is causing my confusion is that the connecting lines between the Albums and Events table diagrams in my setup seems to point back to the Photos table, and the Photos table seems to point to the Tags table. From the tutorial, the connecting lines seem to be pointing the other direction. Am I setting up the key right, or do I have them backwards? I'm setting out to learn a lot of stuff in this tutorial, so when I mess it up, i would like to know that it isn't because the foreign keys are wrong.EDIT:After doing some googling and some reading and watching, I'm a little less confused about how to set up foreign keys, at least how to do it in VWD. My problem now seems to be understanding the relationships. In the tutorial example, the Tags table has a FK on the PhotoID field, which matches the primary key (PhotoID) in the Photos table. Now, does this mean that the Tags table is the parent table and the Photos table the child? If this is true, then the other two tables must be child tables of the Photos table. At least that how I think this should work. The issue that I'm having with this is trying to understand how a cascade delete would work. Is the foreign key relationship a one way or both way relationship? For example, if I have made a foreign key on the AlbumID field of the Photos table, where the primary key table is Albums, PK is AlbumID and the foreign key table is Photos, then, if I do a cascade delete of a particular photo, does it delete the album too? That wouldn't make sense. Foreign keys and cascade deletes must be one way, right? Thanks
INSERT INTO X ...... ( A, B, C )INSERT INTO Y ...... ( J, K, L )If Y has a foreign key M which is the primary key D of X,is there an easy and/or efficient way to have SQL Serverassign D, and tell us what it is so we can add M to thelist for Y ?I know I can select D where A, B, C but I wondered aboutother tricks.--Wes GroleauAlive and Wellhttp://freepages.religions.rootsweb.com/~wgroleau/
I ran into an interesting situation. I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem. I found FK's that are referencing columns that do not have a unique constraint or a unique index.Â
I don't know the history of the database but was there a time in SQL Server history where this would have been possible? I scripted out the tables and created it in a test database. When I run the script to create the FK I get the following message.Â
I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2. Currently the database is running on SQL Server 2008 Ent.
ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1]) REFERENCES [dbo].[table2] ([col2])
There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.
I received the error below upon saving when I was trying to create a foreign key in a table using relationships in "table designer" of sql server 2005 management studio. This process has been successful for other tables. What might be the problem?
Thanks
Code Snippet
- Unable to create relationship 'FK_SAMPLE_GRID'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_GRID". The conflict occurred in database "acquire", table "dbo.GRID", column 'GRIDNAME'.
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this? Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Hi, I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error. e.g MOB 401.908.804 - Fails 0401.907.324 - okay 8239 9082 (pager) - fails Anyone got an idea on how to fix this???? Regards.. Peter.
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161 Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324). Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ?
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
hi team, .Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code
Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword
and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0
This could return multiple part numbers and If they have entered notes I want the row with the notes
create table Test ( [recId] [int] identity(1, 1) not null, [code] [varchar](50) not null, [prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)), constraint [PK_Test] primary key clustered ( [recId] ) with fillfactor = 90 on [primary] ) on [primary] go
insert into Test (code, prime) values ('AVA', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(1 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('BUS', cast(0 as bit)) insert into Test (code, prime) values ('CAR', cast(1 as bit)) insert into Test (code, prime) values ('CAR', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(1 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit)) insert into Test (code, prime) values ('RLW', cast(0 as bit))
select * from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail insert into Test (code, prime) values ('RLW', cast(1 as bit))
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
I want to make a poll and have to use a foreign key in my database. This is not possible in Web Matrix so I have to use SQL Enterprise Manager. But don't know how. I read the help but can't figure it out. Can someone help me??? Thanks in advance.
Is it possible in SQL 6.5 to create a Foreign Key on a field that has a datatype of SMALLINT? I have created FK`s with other datatypes, but it doesn`t seem to work with this particular datatype. Is there some trick to this?
When you set a foreign key to reference a primary key on another table, does the foreign key automatically update? Can anyone explain just how to make a relational table from just the nuts and bolts SQL statements.
Hi Friends,Is there any way to get the table name which is referenced by theforeign keyfor example: consider two table "Staff" and "Department"Staff with following columnsPK_IDFK_DepartmentIDNameAddressDepartment with following columnsPK_DepartmentIDDeptNameActually what i need is: Initially i would be having the table name as"Staff"from Staff table i need to identify that the column FK_DepartmentID isa foreign keyand the primary key is in the Department tablei need to traverse from Staff table and identify that FK_DepartmentIDis a primary key in Department tablethis has to be accomplished by sql query.... probably this could befetched fromData Dictionary but i couldnt find the relationship between the systemtables.ThanksArunDhaJ
I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.
Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language
table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)
This cause an error because the foreign key does not include all part of the primary key.
Hi everyone, My main table has a column named "customer" which is a foreign key pointed to my secondary table consisted of a column "customer_id". I wanted to change my secondary table's customer_id's name into another name whereby deleting it and create a new column instead (i dont know another way of changing names by code) but i'm not allowed to do that due to "customer_is"'s being attached to my main table as FK. Any suggestion how to do it ? Thanks a lot.
i want to make primary key and foreign key relationship of table A1 and table B1 but A1 exist in database A and B1 exist in database B column name u can pretain as C1, C2
I am deleting a column from a table in code. Before I drop the colum I need to find if the column belongs to a foreign key. I have the names of the foreign keys for the table from sysobjects.How can I determine what columns are part of the foreign key?
Just installed this the Forum starter Kit and am getting this message when I try to add the first threadI'm a bit of a newbie but not only damp behind the earsThe INSERT statement conflicted with the FOREIGN KEY constraint "FK_MembersForums_MemberInfo". The conflict occurred in database "SQL2005_****_*****01", table "dbo.MemberInfo", column 'memberid'.The statement has been terminated. What do I need to do to the SQL Tables in order to get this to work?
Hi, i have 3 tables. Orders: {OrderNr(PK, autonumber), date} Order_Line: {OrderNr(FK), ItemNr(FK), number} Items: {ItemNr(PK), Name, ...}
How do I insert a new Order with a new order_line? I get this error: The INSERT statement conflicted with the FOREIGN KEY constraint I can't figure out how to capture the autogenerated OrderNr.
