How to refer a column when the referencing column is an identity column

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?it does not seem logical at all.
you have some column that's Identity and still you need it to be a FK column for some other table!!!

I don't see any chance for this to succeed.

All you can do is implement some logic in the SPROC to ensure the values in the Identity column are unique and do away with the Identity characteristics of this column.

Hope i've not confused you...|||The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'.. I'm afraid that either the requirement is wrong or you have misunderstood it. The values of the fields in a foreign key are determind by the parent not the child. This should be an identity in the parent and then a matching datatype in the child's foreign key (e.g. smallint, int etc).

HTH|||I'm afraid that either the requirement is wrong or you have misunderstood it. The values of the fields in a foreign key are determind by the parent not the child. This should be an identity in the parent and then a matching datatype in the child's foreign key (e.g. smallint, int etc).


Was thinking on the same line, but was not sure about this.

Thanks for re-enforcing my personal thoughts.

How to read just inserted auto incremented primary key to use it as parameter?

Hi. After inserting data (new row) by using DetailsView control, how to read auto incremented primary key (identity) of this new row from sql database to use it as parameter passed to stored procedure?

Or in other way. I need to insert new row (let's call them parent row) in one table by using DetailsView control. This is quite easy. But after inserting I need to read primary key of this new row to insert new child row in other table. Both tables are in relation. How to do this? Any suggestions? Pawel.

Hi Pawelek,

Since the ID (primary key) is configured as an auto incremented field, the last row inserted will always have the maximum number.

You could write a stored procedure which selects the maximum ID number ( SELECT max(ID) from ParentRowsTable ) and returns it.
Then in your code you can execute the SP after you insert a row and you get the max. ID of the parent rows.

Greets,

Wim

|||Hi. I'm afraid that this is a risky business. What if in the same time some other people will insert new row which becomes row with maximum number? Is it possible such situation? Regards. Pawel.|||

Hi. you can return the@.@.IDENTITY

INSERT INTO [dbo].[table] (name, phone)VALUES ("test", "tttt")Return@.@.IDENTITY

|||I would recommend using SCOPE_IDENTITY() instead of @.@.IDENTITY. The former maintains the scope. Check out BOL for more info.

How to query bitmap columns

I need to find out all columns that are identity. How do I query syscolumn to find out this information.

Thanks.

I need to find all the identity columns in my database. How do I query syscolumns to get this information>

Thanks.

|||Try this: select object_name(object_id) + '.' + name as Table_Column from sys.columns where is_identity = 1 You may want to filter further to eliminate system table identity columns. Please don't post the same question more than once, either. Steve Kass Drew University http://www.stevekass.com MJM2@.discussions.microsoft.com wrote: > I need to find out all columns that are identity. How do I query > syscolumn to find out this information. > > Thanks. > >|||Create the below stored procedure in ur SQL server 2005 database

and execute .It will return all identity column information

Code Snippet

CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON

SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.system_type_id
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue,
CASE c.system_type_id
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC
END

|||This is what I need. Thanks (and sorry for the double post - am new at this).

|||This works as well. Appreciate the responses. Thanks!