CODEWITHSUNDEEP
database-designforeign-keysrelationship
Stefanos Kargas
Stefanos Kargas

Reputation: 11073

Reference to table without foreign key constraint

I have a database and an application that edits the database

I have a table with addresses called ADDRESS (ID, STREETID, NUMBER)

I have a table with street names called STREET (ID, STREETNAME, CREATIONUSERID)

I also have a table with my users called USER (ID, USERNAME, PASSWORD)

The street names in my table STREET are already filled. The table is used by users as a look up table to enter an address (field ADDRESS.STREETID) somewhere in my application, but sometimes the user has to enter a new street that will be inserted into STREET for further use. When a user adds a street to my table I want to be able to keep track of which user made the addition.

Now there are 2 options as I think about it:

1st: Create a false user where the default streets of STREET will refer to and then create a foreign key constraint (STREET.CREATIONUSERID that refers to USER.ID) for the new streets created

2nd: Create no foreign key constraint, leave CREATIONUSERID null for the default streets of STREET and update it only for the new streets created to keep track of the user that added each

Which is better and why?

Upvotes: 1

Views: 2109

Answers (2)

Prasanth V J
Prasanth V J

Reputation: 1196

It is better to use foreign key.It will enforce the referential integrity

Check the link for the advantages of foreign key

Upvotes: 3

Erwin Smout
Erwin Smout

Reputation: 18408

What do you intend to do when some user of your system stops being one, and must thence be removed from your USER table ?

Upvotes: 0

Related Questions