Contents
What happens if you have many ID columns in a table?
If you have many tables with ID as the id you are making reporting that much more difficult. It obscures meaning and makes complex queries harder to read as well as requiring you to use aliases to differentiate on the report itself.
Why do you use PK for ID columns in SQL?
Using the standard naming convention of Id the the PK allows for inheritance of a base poco class with validation and such for tables which share a set of common column names. Using the Tablename + Id as the PK for each of these tables destroys the ability to use a base class for these. Just some food for thought.
Why are columns ID and name not allowed in dB?
This is a very powerful thing. Usually an OO software is built on top of the database, but the OO toolset cannot be applied because the db itself does not allow it. Having the columns id and name is still not very good, but it is a step. Why cant I do this?
How are column names used in Poco generation?
In my current position we are employing the entity framework using POCO generation. Using the standard naming convention of Id the the PK allows for inheritance of a base poco class with validation and such for tables which share a set of common column names.
How to find all tables with specified column name?
SQL query to show all tables that have specified column name: SELECT SCHEMA_NAME (schema_id) + ‘.’ + t.name AS ‘Table Name’ FROM sys.tables t INNER JOIN sys.columns c ON c.object_id = t.object_id WHERE c.name like ‘%ColumnName%’ ORDER BY ‘Table Name’. Share.
Which is the correct name for the ID column?
If you use tablenameID as the id field, this kind of accidental mistake is far less likely to happen and much easier to find. I always prefered ID to TableName + ID for the id column and then TableName + ID for a foreign key.
Can a + 1 column have the same name as a foreign key column?
+1 Columns that are synonymous should have the same name – by using the table name prefix you can have a primary key column called table1ID and a foreign key column in another table called table1ID – and you KNOW that they are the same thing.
How to select maximum ID in one to many relation?
Just group by the code field on table B in your subquery and select the maximum Id per grouping. If supported by the database, OUTER APPLY is an efficient and terse option.
Is it OK to use TableName + ID as a PK?
Using the Tablename + Id as the PK for each of these tables destroys the ability to use a base class for these. Just some food for thought. My preference is also ID for primary key and TableNameID for foreign key.
When to use table.id or tableid in SQL?
If generating unnecessary errors is a goal (maybe some people don’t have enough work?), then this kind of naming convention is great. Otherwise consistent naming is the way to go. I personally prefer (as it has been stated above) the Table.ID for the PK and TableID for the FK. Even (please don’t shoot me) Microsoft Access recommends this.