Foreign Key and Nullable column.

Hi Gurus,
This seems simple but not to me. Now I want to create a new table that
contains a column such as Product_Id. I want to create a foreign key
constraint on Product_Id pointing to a Product table. Sometimes the
Product_Id can be NULL but I don't want to make the column to be a nullable
column, because that can make query complex and slow. So I like to use 0 to
represent NULL. Now I also don't want to insert a Product_Id=0 to the Product
table, because I just worry that can hurt applications or some reports. My
dillema here is that I just can't create the forieign key to protect data
integrity because the 0 Product_Id may not be able to find a line in the
Product table. I also have other design choices like this.
Do you have similar experience? Can any guru here give me a good advice? Can
I avoid to insert a dummy line with Product_Id=0 and still have the
Non-nullable column and still can create the FK?
Thanks in advance.
JamesJames,
Could make the column NOT NULL with a DEFAULT constraint of 9999999 or some
number out-of-range for your products.
HTH
Jerry
"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:0829A5B5-7BCE-4508-96E9-24ADA978A88D@.microsoft.com...
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable
> column, because that can make query complex and slow. So I like to use 0
> to
> represent NULL. Now I also don't want to insert a Product_Id=0 to the
> Product
> table, because I just worry that can hurt applications or some reports.
> My
> dillema here is that I just can't create the forieign key to protect data
> integrity because the 0 Product_Id may not be able to find a line in the
> Product table. I also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good advice?
> Can
> I avoid to insert a dummy line with Product_Id=0 and still have the
> Non-nullable column and still can create the FK?
> Thanks in advance.
> James|||James Ma wrote:
> Hi Gurus,
> This seems simple but not to me. Now I want to create a new table that
> contains a column such as Product_Id. I want to create a foreign key
> constraint on Product_Id pointing to a Product table. Sometimes the
> Product_Id can be NULL but I don't want to make the column to be a
> nullable column, because that can make query complex and slow. So I
> like to use 0 to represent NULL. Now I also don't want to insert a
> Product_Id=0 to the Product table, because I just worry that can hurt
> applications or some reports. My dillema here is that I just can't
> create the forieign key to protect data integrity because the 0
> Product_Id may not be able to find a line in the Product table. I
> also have other design choices like this.
> Do you have similar experience? Can any guru here give me a good
> advice? Can I avoid to insert a dummy line with Product_Id=0 and
> still have the Non-nullable column and still can create the FK?
> Thanks in advance.
> James
If the FK column can be null then allow null values. From a design
standpoint that's the best option to maintain data integrity, despite
whatever complications this causes with queries.
You can create a view to query the table and replace the NULL value with
whatever you want to send to the application. If no users have rights to
query the table directly you have what you want and have the RI on the
back end that the database demands.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks a lot. I'll use nullable column.
However, I still appreciate if you can help me to clarify. I read many
articles or books that recommend to use a special not null value to represent
null value, since null's behaviour is wierd sometimes (My experiences also
confirm that). So, does that mean it is a better idea to use the dummy line
method if I am designing a database from scratch? When no applications and no
queries have ever been coded?
"David Gugick" wrote:
> James Ma wrote:
> > Hi Gurus,
> >
> > This seems simple but not to me. Now I want to create a new table that
> > contains a column such as Product_Id. I want to create a foreign key
> > constraint on Product_Id pointing to a Product table. Sometimes the
> > Product_Id can be NULL but I don't want to make the column to be a
> > nullable column, because that can make query complex and slow. So I
> > like to use 0 to represent NULL. Now I also don't want to insert a
> > Product_Id=0 to the Product table, because I just worry that can hurt
> > applications or some reports. My dillema here is that I just can't
> > create the forieign key to protect data integrity because the 0
> > Product_Id may not be able to find a line in the Product table. I
> > also have other design choices like this.
> >
> > Do you have similar experience? Can any guru here give me a good
> > advice? Can I avoid to insert a dummy line with Product_Id=0 and
> > still have the Non-nullable column and still can create the FK?
> >
> > Thanks in advance.
> >
> > James
> If the FK column can be null then allow null values. From a design
> standpoint that's the best option to maintain data integrity, despite
> whatever complications this causes with queries.
> You can create a view to query the table and replace the NULL value with
> whatever you want to send to the application. If no users have rights to
> query the table directly you have what you want and have the RI on the
> back end that the database demands.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||James Ma wrote:
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent null value, since null's behaviour is wierd sometimes (My
> experiences also confirm that). So, does that mean it is a better
> idea to use the dummy line method if I am designing a database from
> scratch? When no applications and no queries have ever been coded?
>
If there is a business case for this, and the value actually means
something, then you can do it. Otherwise, I wouldn't.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"James Ma" <JamesMa@.discussions.microsoft.com> wrote in message
news:9E1AF30B-B432-449D-9540-B541910D9F38@.microsoft.com...
> Thanks a lot. I'll use nullable column.
> However, I still appreciate if you can help me to clarify. I read many
> articles or books that recommend to use a special not null value to
> represent
> null value, since null's behaviour is wierd sometimes (My experiences also
> confirm that). So, does that mean it is a better idea to use the dummy
> line
> method if I am designing a database from scratch? When no applications and
> no
> queries have ever been coded?
>
Opinions differ and there aren't necessarily absolute right or wrong answers
to design questions. In my opinion it does make perfect sense to minimise
the use of nulls wherever feasible. I dislike using nullable foreign keys
for some of the reasons you've mentioned. There is an easy alternative.
Create a new table that has a common primary key with your current one and
then only populate that table where you need to reference a product.
CREATE TABLE your_table (x INTEGER NOT NULL PRIMARY KEY, ....)
CREATE TABLE your_table_product (x INTEGER NOT NULL PRIMARY KEY REFERENCES
your_table (x) , ...., product_id INTEGER NOT NULL REFERENCES products
(product_id))
--
David Portas
SQL Server MVP
--

Foreign key and indexes

I have a question about foreign keys and indexes that I am unsure
about. If a table has a foreign key does this column have a non
clustered index assigned to it as default (that is hidden)? or does it
make sense to add a non clustered index to the foreign key column in
the foreign key table?
I am thinking this as I am unsure how SQL server handles foreign keys.
EXAMPLE BELOW...
DOES TABLE2.Table1ID have a non clustered index that SQL server uses?
CREATE TABLE dbo.Table1
(
Table1Id int NOT NULL,
Foo varchar(10) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1_1 PRIMARY KEY CLUSTERED
(
Table1Id
) ON [PRIMARY]
GO
CREATE TABLE dbo.Table2
(
Table2ID int NOT NULL,
Table1ID int NULL,
barr varchar(10) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 ADD CONSTRAINT
PK_Table2 PRIMARY KEY CLUSTERED
(
Table2ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.Table2 WITH NOCHECK ADD CONSTRAINT
FK_Table2_Table1 FOREIGN KEY
(
Table1ID
) REFERENCES dbo.Table1
(
Table1Id
) NOT FOR REPLICATION
GO
ALTER TABLE dbo.Table2
NOCHECK CONSTRAINT FK_Table2_Table1
GOSQL Server does not index foreign keys by default. Usually it does make
sense to create an index on a foreign key.
David Portas
SQL Server MVP
--sql

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a écrit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henri
a Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR
|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a crit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

Foreign Key + Index

Imagine 2 tables:
AUTHORS
author_id (int) (PK)
author_name (varchar)
BOOKS
book_id (int) (PK)
book_author_id (int) (FK from AUTHOR)
book_title (varchar)
book_author_id is already declared as a foreign key.
If I want better performance when querying SELECT * FROM BOOKS WHERE
book_author_id = 1234
do I have to set a index on book_author_id,
or is it unecessary as a FK is already set?
I think that as a FK is a constraint and not an index, it's still necessary
but I want to be sure.
Can you answer my question?
Thanks
Henria Foreign Key is NOT automatically indexed in SQL Server.
you'll need to index it.
Greg Jackson
Portland, OR|||Thanks for your answer Greg :-)
"pdxJaxon" <GregoryAJackson@.Hotmail.com> a crit dans le message de
news:OMI0r%234EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> a Foreign Key is NOT automatically indexed in SQL Server.
> you'll need to index it.
>
> Greg Jackson
> Portland, OR
>
>

foreign key - relationship

I have two tables and I'm trying to create a one to many relationship (master table can have many records in the details table)

I created a column in my details table with with ID of the primary key in the master table.

The primary key ID isn't inserted as a foreign key when I insert a record. I specifed the relationship in EM.

Not sure why the primary key ID isn't inserted as a foreign key into my details table?

Any help is greatly appreciated. Thanks.
-Dman100-Explain how you are doing it?

ohhh you do realise you need to insert the value into the table -- it does not do it automatically|||Okay, my mistake, I thought it would be automatic.

Can I do this within my sql statement, using a join or insert or whatever?? to pass the primary key value from the master table into the details table as a foreign key?

Thanks for your help! I appreciate it.
-Dman100-

Foreign Key - ON DELETE RESTRICT

Hi

Are there any way to use foreign key in MS-SQL Express with ON DELETE RESTRICT like other databases?

Best Regards

Igor Sane

to my knowledge there are only the cacade update/delete functions|||

isane did this answer your question? if not please provide more info or mark answer.

thanks,

derek

sql